Re: SQL statement tuning

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

--
http://www.freelists.org/webpage/oracle-l