| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: One Query or Two
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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
 |
 |