query using OR operator

From: Suhen Pather <Suhen.Pather_at_strandbags.com.au>
Date: Tue, 05 Jun 2001 21:52:23 -0700
Message-ID: <F001.0031D4EA.20010605213026@fatcity.com>

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: Suhen.Pather_at_strandbags.com.au

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: ListGuru_at_fatcity.com (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). Received on Tue Jun 05 2001 - 23:52:23 CDT