Oracle-L: Optimizer choosing different index

From: Browett, Darren <dbrowett_at_city.coquitlam.bc.ca>
Date: Wed, 22 May 2002 12:03:40 -0800
Message-ID: <F001.0046838E.20020522120340@fatcity.com>

Oracle 8.0.5, peoplesoft version 7.51

We have a report that has stopped working due to running out of temporary space.

The first step was to check the explain plan. I compared the explain plans between the
production database and a dev database that is approx 6 months old. They were very different.

Upon review we found that the production database is now using a different index then the dev system.

I made a copy of the prod database, deleted the index, and the explain plan and sql performed the
same as the dev system.

Question I have is, what would cause the cost based optimizer to choose a different index. The only thing
that has changed is the number of rows in the source table.

Statistics are up-todate, and I have re-built the indexes in question.

Thanks

Darren

Darren Browett P.Eng This message
was transmitted
Data Administrator using 100%
recycled electrons
Information and Communication Technology City of Coquitlam
P:(604)927 - 3614
E:dbrowett_at_city.coquitlam.bc.ca

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Browett, Darren
INET: dbrowett_at_city.coquitlam.bc.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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). Received on Wed May 22 2002 - 15:03:40 CDT