SET EXPLAIN is your friend, but keep a tight leash on your developers.
Mine implemented this in their 4GL code, and left it there, so that I
had 400 copies of set explain running on my system !

I eventually forced them to remove it, by revoking permissions on the
sqexplain.out file, and caused the programs to crash (the only way I
could stop them) .....


-----Original Message-----
From: owner-informix-list@iiug.org [mailtowner-informix-list@iiug.org]
On Behalf Of Simmons, Keith
Sent: 03 March 2005 11:20 AM
To: tomcaml@yahoo.com
Cc: informix-list@iiug.org
Subject: RE: indexes

Tom

In the specific example you quote the index will be used to directly
retrieve
the required records, order of where clause does not matter.
What will stop a direct index read and cause an index scan (full or
partial)
is if not all the indexed fields, in sequence, are used in the where
clause
(ie where col1 and col3 would be OK, where col1 and col4 would cause in
index
scan on all col1 matches).
Also if you use an IN clause, or BETWEEN or MATCHES (especially on any
other than the last column in the index) you will get an index scan (or
even
table scan if the optimiser thinks this would be quicker).
SET EXPLAIN ON; is your friend.

Keith

-> -----Original Message-----
-> From: tomcaml@yahoo.com [mailto:tomcaml@yahoo.com]
-> Sent: Wednesday, March 02, 2005 5:28 PM
-> To: informix-list@iiug.org
-> Subject: indexes
->
->
-> hello
->
-> stull fuzzy on index retrieval and the order of the indexed
-> fields....
->
-> if i have a table with these columns indexed (col1, col3, col4)
-> and the sql says col1, col4, col3 does the index still get used or
-> does it stop at col1 because of the order?
->
-> i assume always using the exact order of fields in the sql
-> to match the
-> indexes is the way to be correct but am unsure about different orders
-> or if the sql has two out of four included but they are either in the
-> same order or ordered differently.
->
-> thanks in advance!
->
-> tom
->

************************************************** **********************
**********
This message is sent in strict confidence for the addressee only. It
may
contain legally privileged information. The contents are not to be
disclosed
to anyone other than the addressee. Unauthorised recipients are
requested
to preserve this confidentiality and to advise the sender immediately of
any
error in transmission.
This footnote also confirms that this email message has been swept for
the
presence of computer viruses, however we cannot guarantee that this
message
is free from such problems.
************************************************** **********************
**********


sending to informix-list

sending to informix-list