from index full scan to Index rowid scan
Please help me to change the
plan of ps_item_details itemcode
to do index rowid scan.
Now it is taking more time.
SELECT 1 FROM PS_MASTERS,PS_ITEM_DETAILS
WHERE SUPL_NAME='YYYY'
AND PS_MASTERS.ITEM_CODE=PS_ITEM_DETAILS.ITEM_CODE;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4558 Card=67251940 B
ytes=2219314020)
1 0 MERGE JOIN (Cost=4558 Card=67251940 Bytes=2219314020)
2 1 INDEX (FULL SCAN) OF 'X_INDX_ITEM_PSITS' (NON-UNIQUE) (C
ost=4170 Card=574263 Bytes=4594104)
3 1 SORT (JOIN) (Cost=386 Card=11711 Bytes=292775)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'PS_MASTERS' (Cost=2
Card=11711 Bytes=292775)
5 4 INDEX (RANGE SCAN) OF 'X_PS_MASTERS_SUPL' (NON-UNIQU
E) (Cost=1 Card=11711)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4328 consistent gets
4201 physical reads
0 redo size
4133 bytes sent via SQL*Net to client
2529 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
290 rows processed
