Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Same SQL statement, Same Oracle, Different OS ==> Different E

Re: Same SQL statement, Same Oracle, Different OS ==> Different E

From: Jared Still <jkstill_at_cybcon.com>
Date: Fri, 06 Sep 2002 15:48:25 -0800
Message-ID: <F001.004CA82E.20020906154825@fatcity.com>

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:SamB_at_cpas.com <mailto:SamB_at_cpas.com> ]
> >
> > 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: jkstill_at_cybcon.com

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 Fri Sep 06 2002 - 18:48:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US