Re: One Query or Two
- From: Vlad Sadilovskiy <vlovsky@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 10 Jul 2005 12:47:53 -0400
Hi,
Let me try to help.
Try to minimize cost of supporting different query forms. Please,
consider following workarounds.
If you resample (update statistics) the table then the Oracle should
invalidate current plan for you. Hence, next time the query is hard
parsed and gets a new (appropriate) plan.
If you use predicates to determine relevant row set, just use literals
and not bind variables. With histograms calculated, literals should
take care of building approprate plan.
Most inconvenient case, when you use dynamic_sampling and you don't
have a literal to have deferent forms for distinct cases. Add a
representative comment inside /*+ */ for different query instances or
force a hard parse.
If you use user defined types, cardinality hint tells CBO about actual
cardinality of the TABLE() expression. Otherwise, it uses "magic"
number assumption - actually the default block size in bytes.
- Vladimir
On 7/8/05, Powell, Mark D <mark.powell@xxxxxxx> wrote:
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
