Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL statement tuning

Re: SQL statement tuning

From: Robin Li <rli_at_nyp.org>
Date: Wed, 28 Nov 2007 10:41:07 -0500
Message-id: <474D8C13.2010409@nyp.org>


This is the answer from the remedy application support group, I don't know if it helps?



The full query looks similar to:

...WHERE ( 'Company' = $Company$) OR ( $Company$ = " " )) AND (( 'Region' = $Region$) OR ( $Region$ = " " )) AND ...

The query is structured to have those ORs to allow for the situation where (in the example above) Company is not filled in but Region is, and to still return a value.

So if $Company$ = Microsoft, and $Region$ = " " then the query would read: ...WHERE ( 'Company' = "Microsoft") OR ( Microsoft = " " )) AND (( 'Region' = " " ) OR ( " " = " " )) AND ... and it would find anything where Company = Microsoft regardless what value was in Region (because " " = " " in the region OR statement)

If $Company$ = " " and $Region$ = East, the query would read: ...WHERE ( 'Company' = " ") OR ( " " = " " )) AND (( 'Region' = "East" ) OR ( "East" = " " )) AND ...
and it would find anything where Region = East, regardless of Company (because " " = " " in the company OR statement)

I believe that they are checking for spaces due to how ARS/Remedy passes values in this situation.


Robin

Finn Jorgensen wrote:

> Clearly the "Eagle", "(0=0)" and "'Eagle = ' '" is generated by code
> somewhere, somehow and without the details of how and why it's
> generated like this, it's going to be difficult to suggest a solution.
>
> Finn

>

>
> On 11/28/07, *Robin Li* <rli_at_nyp.org <mailto:rli_at_nyp.org>> wrote:
>

> Yes, I did try this one and got the result as fast as without the
> 'OR'. The remedy application support group said that they had to
> contact BMC to see what they could do, because this query is only
> a small subset of the full query (lots of 'OR'), and a lot of
> other queries are like this.
>
> Thanks
> >Robin >

>
>

> Finn Jorgensen wrote:
> >> How about : >> >> SELECT T143.C1,C490021100,C1000000001,C200000020 >> FROM aradmin.T143 >> WHERE ((T143.C200000020 LIKE ('Eagle' || '%')) AND (0 = 0)) >> union >> SELECT T143.C1,C490021100,C1000000001,C200000020 >> FROM aradmin.T143 >> WHERE ('Eagle' = ' '); >> >> Finn >> >> >> On 11/27/07, *Robin Li* <rli_at_nyp.org <mailto:rli_at_nyp.org>> wrote: >> >> -- Hi all, >> >> Can someone shed some light on how to tune this sql >> statement? Oracle >> version 10.2 >> >> SELECT T143.C1,C490021100,C1000000001,C200000020 >> FROM aradmin.T143 >> WHERE (((T143.C200000020 LIKE ('Eagle' || '%')) AND (0 = 0)) >> OR ('Eagle' >> = ' ')); >> >> Execution Plan >> ---------------------------------------------------------- >> Plan hash value: 1317490582 >> ------------------------------------------------------------------------ >> | Id | Operation | Name | >> Rows | Bytes | >> ------------------------------------------------------------------------ >> >> | 0 | SELECT >> STATEMENT | | 7375 | 388K| >> | 1 | TABLE ACCESS BY INDEX ROWID| T143 >> | 7375 | 388K| >> |* 2 | INDEX RANGE SCAN | I143_200000020_1 >> | 7624 | | >> ------------------------------------------------------------------------ >> Predicate Information (identified by operation id): >> --------------------------------------------------- >> 2 - access("T143"."C200000020" LIKE 'Eagle%') >> filter("T143"."C200000020" LIKE 'Eagle%') >> >> From the trace: >> >> SELECT T143.C1,C490021100,C1000000001,C200000020 >> FROM aradmin.T143 >> WHERE (((T143.C200000020 LIKE (:"SYS_B_0" || :"SYS_B_1")) AND >> (:"SYS_B_2" = :"SY >> S_B_3")) OR (:"SYS_B_4" = :"SYS_B_5")) >> >> call count cpu elapsed disk query >> current rows >> ------- ------ -------- ---------- ---------- ---------- >> ---------- >> ---------- >> Parse 1 0.00 0.00 0 0 >> 0 0 >> Execute 1 0.00 0.00 0 0 >> 0 0 >> Fetch 2 12.10 42.49 126166 130472 >> 0 1 >> ------- ------ -------- ---------- ---------- ---------- >> ---------- >> ---------- >> total 4 12.10 42.49 126166 130472 >> 0 1 >> Misses in library cache during parse: 0 >> Optimizer mode: ALL_ROWS >> Parsing user id: 51 >> >> Rows Row Source Operation >> ------- --------------------------------------------------- >> 1 TABLE ACCESS FULL T143 (cr=130472 pr=126166 pw=0 >> time=129302 us) >> >> Elapsed times include waiting on following events: >> Event waited on Times Max. >> Wait Total >> Waited >> ---------------------------------------- Waited ---------- >> ------------ >> SQL*Net message to client 2 0.00 >> 0.00 >> db file sequential read 824 0.03 >> 1.10 >> db file scattered read 8641 0.06 >> 33.40 >> SQL*Net message from client 2 0.00 >> 0.00 >> >> There are 3.1 million rows for this table, the "Eagle" = " " >> is used in workflow for a specific function on a remedy >> application. If I take out the 'OR'(I figure it's always not >> true), the query runs in milliseconds, the execution plan is >> the same . The problem is that we can't take the 'OR' out. >> How can I solve this? >> >> TIA >> >> Robin >> >> >> >> >> >> -------------------- >> >> This electronic message is intended to be for the use only of >> the named recipient, and may contain information that is >> confidential or privileged. If you are not the intended >> recipient, you are hereby notified that any disclosure, >> copying, distribution or use of the contents of this message >> is strictly prohibited. If you have received this message in >> error or are not the named recipient, please notify us >> immediately by contacting the sender at the electronic mail >> address noted above, and delete and destroy all copies of >> this message. Thank you. >> >> >> -- >> http://www.freelists.org/webpage/oracle-l >> >> >> >-------------------- > >This electronic message is intended to be for the use only of the named recipient, and may contain information that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately by contacting the sender at the electronic mail address noted above, and delete and destroy all copies of this message. Thank you. >

>

>
>

This electronic message is intended to be for the use only of the named recipient, and may contain information that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately by contacting the sender at the electronic mail address noted above, and delete and destroy all copies of this message. Thank you.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 28 2007 - 09:41:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US