Re: Partition pruning

From: Yong Huang <yong321_at_yahoo.com>
Date: Sat, 2 May 2009 16:04:04 -0700 (PDT)
Message-ID: <182875.90051.qm_at_web80607.mail.mud.yahoo.com>

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

Received on Sat May 02 2009 - 18:04:04 CDT