RE: One Query or Two
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 8 Jul 2005 09:18:05 -0400
Charolette, there are times where you as a developer will understand
the data better than the optimizer will be able to so in those cases
adding logic to execute a different tuned query for each case seems to
be a perfectly valid technique. You just want to be careful to do this
only where it is of significant benefit and you want to try to make sure
that you design the logic in such a way that it is unlikely to ever
cause a problem in the future.
IMHO -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Charlotte Hammond
Sent: Friday, July 08, 2005 6:11 AM
To: ORACLE-L
Subject: One Query or Two
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:
