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

Home -> Community -> Mailing Lists -> Oracle-L -> Explain Plan Question

Explain Plan Question

From: Deepak Sharma <sharmakdeep_at_yahoo.com>
Date: Fri, 14 Jul 2000 12:20:14 -0700 (PDT)
Message-Id: <10558.112102@fatcity.com>


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

Do You Yahoo!?
Get Yahoo! Mail Free email you can access from anywhere! Received on Fri Jul 14 2000 - 14:20:14 CDT

Original text of this message

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