Colin

What was the Query? Explain Plan ?

Keith

-> -----Original Message-----
-> From: Colin Dawson [mailto:cjd_1955@hotmail.com]
-> Sent: Friday, September 23, 2005 10:00 AM
-> To: informix-list@iiug.org
-> Subject: Optimiser choice of index
->
->
-> IDS 7.31.FD7
-> Solaris 9
->
->
-> We have a query that rans in <1 sec using a 2 column
-> composite index (id &
-> date). I fragmented the table (it was getting close to the
-> 16777215 page
-> limit) and added another index (date). The query now takes
-> >5 mins to run, a
-> set explain confirmed the optimiser was using the new index.
-> Adding an
-> optimiser directive to use the original index solved the problem.
->
-> BTW Update Statistics was run after the fragmentation and
-> index build.
->
-> My question is this:
-> Why would the optimiser use the new index on date only when
-> using the index
-> on id and date is obviously better?
->
-> I have a vague recollection about Informix selecting the
-> latest created
-> index when a column appears in more than one index but can't
-> remember which
-> version of OnLine it was.
->
-> All observations gratefully received
->
->
->
->
-> Regards
->
-> Colin
->
-> There are 10 types of people in the world, those that
-> understand binary and
-> those that don't
-> sending to informix-list
->

************************************************** ********************************
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