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_CHILD0. 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: 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) 10 FILTER 21 TABLE ACCESS (FULL) OF 'PRCMSTEE' (Cost=84 Card=5026 Byt es=30156) 31 TABLE ACCESS (BY INDEX ROWID) OF 'PRDMSTEE' (Cost=2 Card =1 Bytes=7) 43 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). 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: [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).
Re: query using OR operator
Hi Suhen, The UNION ALL will return duplicates from the two queries, whereas the original query will not, right? So the query is tuned, but it returns a different result set. Do you agree? Best regards, - Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
RE: query using OR operator
Greg, Correct me if I am wrong but both will return duplicates since the OR operator is used. The primary key is on PRD_LVL_CHILD.PRDMSTEE. Regards Suhen Hi Suhen, The UNION ALL will return duplicates from the two queries, whereas the original query will not, right? So the query is tuned, but it returns a different result set. Do you agree? Best regards, - Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). -- 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).
Re: query using OR operator
both will return duplicates since the OR operator is used. select ename from emp where mgr = 1 or mgr in (1); -- If there are three rows where mgr = 1, this query will return three rows. select ename from emp where mgr = 1 union all select ename from emp where mgr in (1) -- But this returns six rows. In rewriting your query to tune it, you changed the sql from the form used in the first example to the form used in the second. So for your query, if there is ever a row in the table where the CHILD column value = 105 and in that same row the PARENT column value is also 105, you will get duplicates in the new tuned query that would not have appeared in the old one. Maybe you have a check constraint on these two columns to guarantee that will never happen
RE: query using OR operator
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) 10 FILTER 21 TABLE ACCESS (FULL) OF 'PRCMSTEE' (Cost=84 Card=5026 Byt es=30156) 31 TABLE ACCESS (BY INDEX ROWID) OF 'PRDMSTEE' (Cost=2 Card =1 Bytes=7) 43 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).
Re: query using OR operator
Assuming there is an index on PRDMSTEE.PRD_LVL_PARENT and PD.PRD_LVL_PARTENT: SELECT PC.PRC_TYPE FROM PRCMSTEE PC WHERE PC.PRPRD_LVL_CHILD = 505 OR EXISTS ( SELECT NULL FROM PRDMSTEE PD WHERE PRD_LVL_CHILD = 505 AND PD.PRD_LVL_PARENT = PC.PRD_LVL_CHILD ) Jared On Tuesday 05 June 2001 22:30, Suhen Pather wrote: 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) 10 FILTER 21 TABLE ACCESS (FULL) OF 'PRCMSTEE' (Cost=84 Card=5026 Byt es=30156) 31 TABLE ACCESS (BY INDEX ROWID) OF 'PRDMSTEE' (Cost=2 Card =1 Bytes=7) 43 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: Jared Still 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).
