Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partition Pruning
Sanjay,
Partition pruning is the Oracle optimizer limiting the scope of your query according to the criteria you have specified either explicitly in the FROM clause (i.e. using the PARTITION or SUBPARTITION clauses) or implicitly in the WHERE clause (i.e. by referencing the partition-key column).
Partition pruning is *not* the act of simply scanning a partitioned table or partitioned index. Performance gains from partitioning do not come from the mere presence of partitioning, but by how you use it.
If you do not give the Oracle optimizer the opportunity to prune by telling it how to do so (either explicitly or implicitly), then you will scan all partitions. What you are seeing is expected behavior.
Hope this helps...
-Tim
on 5/5/04 7:16 AM, Sanjay Mishra at smishra_97_at_yahoo.com wrote:
> Tim
>
> Is it also the casse even if I have local partitioned index on Mgrid. Is there
> any other way that I can put partition as clause in mine query to use the
> particular partitions atleast for some of mine report.
>
> Thanks for your input
> Sanjay
> Tim Gorman <tim_at_sagelogix.com> wrote:
> You partitioned the MANAGER table by MGRID, not by EMPID. Oracle can't
> perform pruning unless your query is referencing the partition-key column.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed May 05 2004 - 08:26:16 CDT