Set Explain On
Hi, I have a sql statment which takes a long time to run, and i tried the explain on and have the following output: 1) informix.cut: INDEX PATH Filters: informix.cut.status != 'X' (1) Index Keys: settled_at Lower Index Filter: informix.cut.settled_at >= datetime(2003-01-01 08:00 :00) year to second Upper Index Filter: informix.cut.settled_at <= datetime(2003-01-02 07:59 :59) year to second 2) informix.abc: INDEX PATH Filters: informix.abc.abc_type = 'SGL' (1) Index Keys: abc_id Lower Index Filter: informix.abc.abc_id = informix.cut.abc_id NESTED LOOP JOIN Since there is no index on the cust.status should i put the index on it to speed up the time??? thx.. Franklin
Actually, it depends on the cardinality of the column "status" .. if there are only 2 or 3 values in the whole table for this column, creating an index might not help that much ...but say, with the option != 'X' you are selecting a very small subset of rows from the whole table then the index is going to immensely help. Thanx much, Rajib Sarkar Advisory Support Engineer (Wells Fargo Bank) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "FRANKLIN TANG" <franklin@macausl To: ids@iiug.org ot.com> cc: Sent by: Subject: Set Explain On [258] forum.subscriber@ iiug.org 02/05/2003 09:47 PM Hi, I have a sql statment which takes a long time to run, and i tried the explain on and have the following output: 1) informix.cut: INDEX PATH Filters: informix.cut.status != 'X' (1) Index Keys: settled_at Lower Index Filter: informix.cut.settled_at >= datetime(2003-01-01 08:00 :00) year to second Upper Index Filter: informix.cut.settled_at <= datetime(2003-01-02 07:59 :59) year to second 2) informix.abc: INDEX PATH Filters: informix.abc.abc_type = 'SGL' (1) Index Keys: abc_id Lower Index Filter: informix.abc.abc_id = informix.cut.abc_id NESTED LOOP JOIN Since there is no index on the cust.status should i put the index on it to speed up the time??? thx.. Franklin
1. How many records are in the respective tables? 2. What are the table schemas? > -----Original Message----- > From: FRANKLIN TANG [mailto:franklin@macauslot.com] > Sent: Wednesday, February 05, 2003 11:48 PM > To: ids@iiug.org > Subject: Set Explain On [258] > > > Hi, > I have a sql statment which takes a long time to run, and > i tried the explain on and have the following output: > > 1) informix.cut: INDEX PATH > > Filters: informix.cut.status != 'X' > > (1) Index Keys: settled_at > Lower Index Filter: informix.cut.settled_at >= > datetime(2003-01-01 08:00 > :00) year to second > Upper Index Filter: informix.cut.settled_at <= > datetime(2003-01-02 07:59 > :59) year to second > > 2) informix.abc: INDEX PATH > > Filters: informix.abc.abc_type = 'SGL' > > (1) Index Keys: abc_id > Lower Index Filter: informix.abc.abc_id = informix.cut.abc_id > NESTED LOOP JOIN > > > Since there is no index on the cust.status > should i put the index on it to speed up the time??? > thx.. > > Franklin > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."
Looking at the explain it looks like you are joining the tables on abc_id. Depending on the select statement I would recommend adding an index on the joined columns in addition to the filters. The best index to add would be a key only index.... Please provide the schema and sql statement. ----- Forwarded by Darren Jacobs/7001/Carmax on 02/06/03 11:46 AM ----- |---------+-----------------------------> | | "John Carlson " | | | <John_Carlson@whsm| | | ithusa.com> | | | Sent by: | | | forum.subscriber@i| | | iug.org | | | | | | | | | 02/06/03 10:09 AM | | | | |---------+-----------------------------> >------------------------------------------------------------------------------- --------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: RE: Set Explain On [265] | >------------------------------------------------------------------------------- --------------------------------| 1. How many records are in the respective tables? 2. What are the table schemas? > -----Original Message----- > From: FRANKLIN TANG [mailto:franklin@macauslot.com] > Sent: Wednesday, February 05, 2003 11:48 PM > To: ids@iiug.org > Subject: Set Explain On [258] > > > Hi, > I have a sql statment which takes a long time to run, and > i tried the explain on and have the following output: > > 1) informix.cut: INDEX PATH > > Filters: informix.cut.status != 'X' > > (1) Index Keys: settled_at > Lower Index Filter: informix.cut.settled_at >= > datetime(2003-01-01 08:00 > :00) year to second > Upper Index Filter: informix.cut.settled_at <= > datetime(2003-01-02 07:59 > :59) year to second > > 2) informix.abc: INDEX PATH > > Filters: informix.abc.abc_type = 'SGL' > > (1) Index Keys: abc_id > Lower Index Filter: informix.abc.abc_id = informix.cut.abc_id > NESTED LOOP JOIN > > > Since there is no index on the cust.status > should i put the index on it to speed up the time??? > thx.. > > Franklin > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."
It would help to see the whole statement. However, an index is seldom if ever going to help with a not-equals condition. To be of any real use, the number of rows satisfying the equals condition would have to be overwhelmingly the majority of the rows in the table, and even then, it is doubtful to me whether the optimizer would know to use the index -- and I'd suspect the overhead of maintaining the index would outweigh the benefits of doing so. Now, if you added the status column to another index -- that would be a different story. OTOH, it isn't clear which index you might use. Also, did you run UPDATE STATISTICS recently? Correctly for the tables? In general, a not equals condition is not readily exploited for optimization purposes. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "FRANKLIN TANG" | | | <franklin@macausl| | | ot.com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 02/05/2003 08:47 | | | PM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Set Explain On [258] | | | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| Hi, I have a sql statment which takes a long time to run, and i tried the explain on and have the following output: 1) informix.cut: INDEX PATH Filters: informix.cut.status != 'X' (1) Index Keys: settled_at Lower Index Filter: informix.cut.settled_at >= datetime(2003-01-01 08:00 :00) year to second Upper Index Filter: informix.cut.settled_at <= datetime(2003-01-02 07:59 :59) year to second 2) informix.abc: INDEX PATH Filters: informix.abc.abc_type = 'SGL' (1) Index Keys: abc_id Lower Index Filter: informix.abc.abc_id = informix.cut.abc_id NESTED LOOP JOIN Since there is no index on the cust.status should i put the index on it to speed up the time??? thx.. Franklin