List,
After playing around with this query on TEST I tried using a UNION
ALL rather than the OR operator.
SELECT PRC_TYPE FROM PRCMSTEE
WHERE PRD_LVL_CHILD = 505
UNION ALL
SELECT PRC_TYPE FROM PRCMSTEE
WHERE PRD_LVL_CHILD IN (SELECT PRD_LVL_PARENT
FROM PRDMSTEE
WHERE PRD_LVL_CHILD = 505 )
/
The cost of the query was reduced to 6 (from 84).
The Logical IO also decreased considerably.
Are there any downsides to this?
Thanks
Suhen
> 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).
