| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 08 2005 - 05:13:24 CDT
 |
 |