RE: Subquery partition pruning

Hi

> I dont understand why the pruning is not happening with
> TBL$OR$IDX$PART$NUM() function call (subquery pruning) when hash join
is
> involved.

Most of the time it is because of the estimations of the query
optimizer. When I want to demonstrate that feature, I usually set the
environment in the following way:

ALTER SESSION SET "_subquery_pruning_enabled"=TRUE;
ALTER SESSION SET "_subquery_pruning_cost_factor"=1;
ALTER SESSION SET "_subquery_pruning_reduction"=100;

So, what I suggest you is to give it a try by setting this parameters.
If it works, the "problem" is due to the estimations. If it is the case,
you have to judge if the estimations are good or not. It might be a good
thing that subquery pruning is not used... Remember, part of the query
is executed twice and, therefore, the overhead cannot be underestimated.

HTH
Chris

Troubleshooting Oracle Performance (Apress, 2008)
http://antognini.ch/top 


--
http://www.freelists.org/webpage/oracle-l

Other related posts: