When you are saying test - and now production - is not using the index, what is it using instead? an FTS or a different index. If per chance two indexes were tied in the cost, the order in which they were created may be used as a tiebreaker. I am not 100% certain, but I have the impression that the one with the lower object_id is then used, i.e. the one created first. By dropping and re-creating indexes you change the object_id and thus may change the index choice in a tie.
Ultimately, an 10053 event trace is the best way to pinpoint the cause for the different plans.
At 05:45 AM 6/2/2003 -0800, you wrote:
OK, I'm confused. Maybe it's Monday morning and my brain's not working. We have a production schema and a test schema on the same Oracle 8.1.7 instance, running on Windows. They both have a customer table, with 3 million and 2 million records respectively. They both have the same indexes, and both have been analyzed today. Production used an index and took 40ms. Test didn't and took 20s. I played around, analyzing, dropping and creating indexes etc. Now neither of them use the index, both taking around 20s. I can add a hint, which works, but I want to know what changed.
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).