List,
I require help with tuning a query.
SELECT PRC_TYPE FROM PRCMSTEE
WHERE PRD_LVL_CHILD = 505
OR PRD_LVL_CHILD IN (SELECT PRD_LVL_PARENT
FROM PRDMSTEE
WHERE PRD_LVL_CHILD = 505 )
/
Query Plan
--------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=84 Rows=5026 Bytes=30156
FILTER
TABLE ACCESS FULL PRCMSTEE [ANALYZED]
TABLE ACCESS BY INDEX ROWID PRDMSTEE [ANALYZED]
INDEX UNIQUE SCAN PRDMSTEEP1 [ANALYZED]
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=5026 Bytes=3
0156)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'PRCMSTEE' (Cost=84 Card=5026 Byt
es=30156)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRDMSTEE' (Cost=2 Card
=1 Bytes=7)
4 3 INDEX (UNIQUE SCAN) OF 'PRDMSTEEP1' (UNIQUE) (Cost=1 C
ard=1)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
237373 consistent gets
0 physical reads
0 redo size
541 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
I am trying to reduce the logical IO for this query.
The query performs a full table scan on PRCMSTEE, which accounts for the
total cost of the query and high
LIO.
I have run individual parts of the query and the LIO stays lows except when
the OR operator is used.
I know that the OR operator would supress the use of an index, but I also
tried using a index HINT, to force the use of the index, but
Oracle favours doing a full table scan on PRCMSTEE.
There is an INDEX on PRD_LVL_CHILD of PRCMSTEE.
Is there a way to rewrite the query to reduce the LIO?
Thanks and Regards
Suhen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suhen Pather
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
