RE: Explain Plan Question

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 ?
Received on Mon Jul 17 2000 - 06:54:49 CDT