Re: mixing dynamic native sql and static sql?

In article <9769adc8.0204120116.30675406_at_posting.google.com>, plm_at_gmx.li says...
>
>Hello,
>
>I need to parametrize the ordering from a large query.
>
>The whole query could well be written (and preferably be written) as a
>static normal query in a pl/sql sp, only the order by clause is
>impossible to determine statically.
>

if the order by is dynamic -- the entire query is. You can use the technique outlined on:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1288401763279

to implement your binds differently as well. Here, you would add just the predicate components you want (need) -- leaving out the "%" ones and dynamically open the ref cursor at the end.

>Is it possible to first execute the static query, resulting in a
>cursor which is used (kind of like a subquery) within a small dynamic
>query that changes the ordering?
>
>If not, I'd be forced to write my whole query (with about 12 variable
>appearing at multiple places, resulting in a USING list of about 20
>variables) using dynamic sql.
>
>If I must do this, I have noticed strange behaviour in the dynamic
>sql:
>
>There are about 12 possible filter criteria in this query:
>
>select ... from tab1 t1, tab2 t2
>where ...
>and t1.c1 = :x
>and t1.c2 = :y
>...
>
>
>Most of the filter criteria are often not present (i.e. any t1.c1
>would be good).
>I tried to solve this first using dynamic sql by writing
>
>and t1.c1 like :x
>
>
>where :x gets (via a USING bind) '%' in the case that the value of
>t1.c1 doesn't care.
>
>What I have noticed is that a static query containing many "like '%'"
>clauses keeps executing at normal speed (i.e. as if the like '%' would
>not have been present). But in the dynamic sql case, after three or
>more of such like '%' parts the speed goes doewn (from 0.5 seconds to
>20 seconds, and increasing further with more like '%' parts). As if
>the optimizer is behaving very weird in this case.
>
>I could avoid this, by not binding variables, but generating the
>complete query string dynamically (omitting "and t1.c1 = ..." if this
>criterium doesn't care). This would be a huge pain however, especially
>considering the fact that I only need this dynamic sql to influence
>the ordering via parameters.
>
>Hopefully someone can give me some hints.
>
>Thanks in advance,
>
>Peter Mutsaers

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp