Re: One Query or Two

We had similar situation when we had to deal with TABLE TYPEs ie. create or replace type bla is table of bla. When used in queries these structures always assumes some magic default number for returned row count and for example different levels of optimizer_dynamic_sampling didn't do anything to that. So we had to create 2 queries - one for small count of rows that had hint "cardinality (table_name 1)" that mostly resulted in nested loops and second for more rows without cardinality hint that resulted in full scans and hash joins.
That method worked very well for us. I have to say that requirements and therefore SQL code was/is pretty stable though. There is no need to change it frequently.

I don't know how it is in your case but for us 2 types of execution plans where fully acceptable.

Gints Plivna

On 7/8/05, Charlotte Hammond <charlottejanehammond_at_yahoo.com> wrote:
> Hi All,
>
> I have a complex SELECT which is driven by a table
> containing either (a) 1 row or (b) many (10,000s)
> rows. Essentially we wish to do the same processing
> for a single customer on occassion or else the whole
> customer base. It is proving difficult to tune this
> to work optimally under both circumstances. Obviously
> we resample the driving table so the CBO knows whether
> it is situation (a) or (b) before the SELECT is
> parsed. The other tables in the join are pretty
> static in size.
>
> However it is also very very easy to rewrite this
> query into 2 separate queries, one of which works well
> for (a) and one well for (b). We are therefore
> tempted just to have a code block that runs the
> relevant query based on the circumstance. Is this a
> valid approach or should I persist with the
> optimisation exercise on a single SELECT? One concern
> is then that we'd have two (complex) SELECTs to
> maintain instead of one. I could also see the
> situation where (c) would occur in future: process a
> few 100 customers instead of 1 or 10,000s. Would this
> mean another version again?! Seems like we're trying
> to do the CBOs job for it.
>
> Just looking for opinions!
>
> Thanks
> Charlotte
>
>
>
> __________________________________
> Yahoo! Mail for Mobile
> Take Yahoo! Mail with you! Check email on your mobile phone.
> http://mobile.yahoo.com/learn/mail
> --
> http://www.freelists.org/webpage/oracle-l
>

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