Re: One Query or Two

  • From: Gints Plivna <gints.plivna@xxxxxxxxx>
  • To: charlottejanehammond@xxxxxxxxx
  • Date: Fri, 8 Jul 2005 15:23:39 +0300

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@xxxxxxxxx> 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
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l

Other related posts: