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:
- Before analyze, why was PSB... index getting used,
since the order of columns in the where clause is
different ?
- How come correct index, PSA..., is used after the
table is analyzed ?
- What happens in reality when incorrect index, such
as PSB..., is displayed in the explain plan ?
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
