mixing dynamic native sql and static sql?

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.

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 Received on Fri Apr 12 2002 - 04:16:24 CDT