Re: Partition pruning

The 10053 shows that the join order is d_client -> f_order -> d_time.

What I mean is that since f_order is range partitioned by i_number and i_number is present only in d_time and not d_client how can the join between d_client and f_order cause partition elimination since the join keys is not the partitioned keys.

I will take alook at the note and the expanded plan_table.

I was testing in 10.2.0.3, sorry for not specifying it in the first post.

Thanks

--
LSC




On Sun, May 3, 2009 at 1:04 AM, Yong Huang <yong321_at_yahoo.com> wrote:



>

> Hi, Cheng,

>

> Regarding your question why subquery partition pruning still occurs when

> it obviously should not (see the message at

> http://www.freelists.org/post/oracle-l/Partition-pruning

> ), I think this Metalink note is relevant:

> Note:802367.1 "Cost Of Subquery Pruned Partitioned Table Is The Same

> As Accessing All Partitions", or the same titled Bug 8418618. You didn't

> tell us Oracle version. Is it 10.2.0.4 or 10.2.0.3?

>

> If I'm not mistaken, your query actually accesses all 7 partitions. So

> the correct plan should show

>

> ----------------------- ---------------

>  Operation              Pstart| Pstop

> ----------------------- ---------------

> ...

>    PARTITION RANGE ALL     1 |     7

>

> You can get that plan with the workaround in the Metalink note after you

> disable _subquery_pruning_enabled.

>

> > f_order is subpartitioned by range. Partition pruning should happen when

> > d_time joins with f_order.

> > ...

> > I wonder how can partition pruning can happen when d_client joins with

> > f_order when the partitioning key is pointing to d_time?

>

> I don't quite follow you here. But take a look at that Metalink note.

>

> Yong Huang

>

>

>

>

>


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