| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: query using OR operator
Try using the primary key value like if PRD_LVL_CHILD is the primary key then just add a where cond using and operator like PRD_LVL_CHILD>0. This will make the usage of index in the table PRCMSTEE.
May be this is not the actual way, but we have tuned several queries like this and they all are working fine and doing well in our applns.
-- On Tue, 05 Jun 2001 21:30:26 Suhen Pather wrote:Received on Wed Jun 06 2001 - 01:19:02 CDT
>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).
>
Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian INET: csvenkata_at_lycos.com 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).
 |
 |