I don't have an explanation, but: The CBO uses floating point instructions a lot (I think). May be there is a difference in rounding between the Intel chip and the Sparc chip that causes the cost to differ just enough so that the index isn't/is used.
Anjo. On Saturday 07 September 2002 01:48, you wrote: > 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: Anjo Kolk 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).
