One Query or Two
- From: Charlotte Hammond <charlottejanehammond@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 8 Jul 2005 03:11:21 -0700 (PDT)
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
Other related posts:
