Re: SQL statement tuning

Thank you so much for the link. I'll dig more into it. Whether they want to change the app, it's another story.

Robin

Gints Plivna wrote:

>Yeahhh have you looked at the asktom link
>http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1288401763279
>I gave you?
>There are several approaches of dynamically building where clauses
>discussed there, including a variation of yours (actually not your's
>but these developer's :)
>Look ar contexts vs. predicates using short-circuited OR by Bill
>Coulam this a variation of this OR aproach and Tom describes why it is
>not able to use indexes.
>
>Of course the question remains whether you'll be able to enforce these
>people change their app so that such queries can use indexes.
>
>Gints Plivna
>http://www.gplivna.eu
>
>2007/11/28, Robin Li <rli_at_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
>
>

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