Interesting. This might explain a similar problem I had a few years ago. Oracle support did not have a good answer for it.
Jared On Friday 06 September 2002 13:43, Toepke, Kevin M wrote: > I the RBO, the order the indexes were created in is important! I was able > to show this to management on a project I was on. How? By doing a > difinitive proof (follows) > > Import the table and data into an empty database. > Create index A > Create index B > EXPLAIN PLAN shows query using index A. > Drop table > Import the table and data into an empty database > Create index B > Create index A > EXPLAIN PLAN shows query using index B. > Drop table > Import the table and data into an empty database > Create index A > Create index B > EXPLAIN PLAN shows query using index A. > > All other things being equal, the RBO will choose the index with the lower > object_id! > > Proof took place in Oracle 8.0.5 on a Sun Solaris box. > Kevin > > -----Original Message----- > Sent: Friday, September 06, 2002 3:28 PM > To: Multiple recipients of list ORACLE-L > > > > (see answer below) > > > -----Original Message----- > > From: Sam Bootsma [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] > > > > One of our developers is encountering a situation where > > Oracle 9.0.x explain > > plan chooses one index when on UNIX, and chooses a different > > index when > > running on Windows NT. I'd appreciate any insights or > > similar experiences. > > > > The following are the facts: > > 1. The explain plan is run against the same SELECT statement on both > > platforms > > 2. It has been confirmed that there are no statistics on either of the > > databases > > 3. Both databases are using RBO (not CBO) > > 4. The UNIX database has about 100 times as many rows (in > > this table) as the > > NT database > > 5. The SELECT statement that gives different explain plans on > > different > > platforms is: > > > > SELECT FN.*, FN.ROWID > > FROM UNITFUND FN > > WHERE FN.FU_CODE = :cFuCode AND > > FN.MKEY = :cMkey AND > > FN.CLNT = :sKey AND > > FN.PLANNO = :sKey AND > > FN.DATE_FROM <= :dDate AND > > FN.SOURCE = :cSource AND > > FN.TSTATUS = 'O' > > ORDER BY FN.DATE_FROM, FN.TSECOND; > > > > 6. Between the following 2 indexes, Oracle 9.0x chooses (2) > > on Unix and (1) > > on Windows NT. > > > > 1) clnt, mkey, planno, fu_code, date_from, source, tracode, tsecond... > > 2) date_from, clnt, planno, mkey, fu_code > > Just a wild guess, but maybe the optimizer is just picking the first index > it finds because it thinks both are equally good candidates. Were both > indexes created in the same order on both databases? > > I.e. is Object_id (from dba_objects) for Index A smaller than object_id for > Index B on the UNIX database, but the reverse is true on Windows? > > You say the databases are using RBO. How do you know? Remember that if you > use some new features > (from the manual: Partitioned tables and indexes > Index-organized tables > Reverse key indexes > Function-based indexes > SAMPLE clauses in a SELECT statement > Parallel query and parallel DML > Star transformations and star joins > Extensible optimizer > Query rewrite with materialized views > Enterprise Manager progress meter > Hash joins > Bitmap indexes and bitmap join indexes > Index skip scans ) > the query optimizer will use CBO because new features are not supported by > RBO. > > Finally, this section of the manual may help you guess what's happening: > Oracle9i Database Performance Guide and Reference > Part Number A87503-02 > Chapter 8 > Using the Rule-Based Optimizer > ... > Understanding Access Paths for the RBO ---------------------------------------- Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: ---------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [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).
