Re: Partition pruning
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: yong321@xxxxxxxxx
- Date: Sun, 3 May 2009 12:15:58 +0200
Hi Yong
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@xxxxxxxxx> wrote:
>
> Hi, Cheng,
>
> Regarding your question why subquery partition pruning still occurs when
> it obviously should not (see the message at
> //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
>
>
>
>
>
Other related posts:
