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: Explain Plan Question

RE: Explain Plan Question

From: Rajagopal Venkataramany <rajagopalvr_at_excite.com>
Date: Mon, 17 Jul 2000 19:30:50 -0700 (PDT)
Message-Id: <10561.112230@fatcity.com>


Kevin,

 If one is using RBO, from what I have learnt, if more than one index  have the same "ranking" then the optimizer scans the "FROM" clause  from right to left, taking the rightmost table as the driving table  for the query.

 It has been a coding standard that is followed is to ensure that the  right most table has more distinct/better selectivity for a given  SQL condition.

 Hope this helps....

Regards
Rajagopal Venkataramany

On Mon, 17 Jul 2000 05:05:14 -0800, ORACLE-L_at_fatcity.com wrote:

> Deepak:
>
> When the rule based optimizer finds multiple indexes that have the
"correct"
> columns, it uses the first one it found. In other words, if there is more
> than one index on the same set of columns, the rule based optimizer will
> randomly pick one.
>
> The cost-based optimizer chooses the index with the most selective
leading
> column.
>
> Oracle uses the plan (and indexes) reported by explain plan when
executing
> the query.
>
> My experience with PeopleSoft (I've been involved in 2 implementations
and
> about 5 upgrades) shows that there can be significant performance gains
(and
> losses) when switching from the Rule-based optimizer to the Cost-based
> optimizer. Care must be taken to test the whole application in a
reasonably
> sized environment (the client site I was at used a database sized at
1/10th
> of production.)
>
> Kevin
>
> > -----Original Message-----
> > From: Deepak Sharma [mailto:sharmakdeep_at_yahoo.com]
> > Sent: Friday, July 14, 2000 4:26 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Explain Plan Question
> >
> >
> > I have the following Peoplesoft query:
> >
> > SELECT CUST.NAME1
> > FROM PS_CUSTOMER CUST,
> > PS_SET_CNTRL_REC SCR
> > WHERE SCR.SETCNTRLVALUE = 'DS001'
> > AND SCR.RECNAME = 'CUSTOMER'
> > AND CUST.SETID = SCR.SETID
> > AND CUST.CUST_ID = '00050'
> >
> > The explain plan BEFORE table PS_SET_CNTRL_REC is
> > analyzed is:
> >
> > QUERY_PLAN
> > --------------------------------------------------------------
> > ------------------
> > SELECT STATEMENT Cost =
> > 1 2.1 NESTED LOOPS
> > 2 3.1 INDEX RANGE SCAN PSBSET_CNTRL_REC UNIQUE
> > 3 3.2 TABLE ACCESS BY INDEX ROWID PS_CUSTOMER
> > 4 4.1 INDEX UNIQUE SCAN PSDCUSTOMER UNIQUE
> >
> > The explain plan AFTER table PS_SET_CNTRL_REC is
> > analyzed is:
> >
> > QUERY_PLAN
> > --------------------------------------------------------------
> > ------------------
> > SELECT STATEMENT Cost = 3
> > 1 2.1 NESTED LOOPS
> > 2 3.1 INDEX RANGE SCAN PSASET_CNTRL_REC UNIQUE
> > 3 3.2 TABLE ACCESS BY INDEX ROWID PS_CUSTOMER
> > 4 4.1 INDEX UNIQUE SCAN PSDCUSTOMER UNIQUE
> >
> > These are the indexes on PS_SET_CNTRL_REC (8500 Rows):
> >
> > PSASET_CNTRL_REC(SETCNTRLVALUE,RECNAME,SETID)
> > PSBSET_CNTRL_REC(RECNAME,SETCNTRLVALUE,SETID)
> >
> > The questions:
> >
> > 1. Before analyze, why was PSB... index getting used,
> > since the order of columns in the where clause is
> > different ?
> > 2. How come correct index, PSA..., is used after the
> > table is analyzed ?
> > 3. What happens in reality when incorrect index, such
> > as PSB..., is displayed in the explain plan ?
> >
> > -- Deepak
> >
> > =====
> > Oracle DBA,
> > Minneapolis, MN
> > USA
> --
> Author: Toepke, Kevin M
> INET: ktoepke_at_cms.cendant.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).

Regards
Rajagopal Venkataramany Received on Mon Jul 17 2000 - 21:30:50 CDT

Original text of this message

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