Re: Oracle's use of Indexes

It will be hard to find what changed since you don't have the information on exactly what production looked like when it was using the index. Whenever you do somthing which may affect the statistics, make a backup of the current statistics with dbms_stats.export_table_stats(..., cascade => true). Then you can always restore the statistics if what you try doesn't work out. You can even import the saved statistics into you test schema and see if you then get the same execution plan there as well. If yes, then it's the statistics, if no then it's some other parameter(s) that cause the difference.

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: breitliw_at_centrexcc.com

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: ListGuru_at_fatcity.com (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).