Partition Pruning in Oracle

I write this article about "Partition Pruning" based on requests from the regular readers ( Vicky, Neil & Chateswar ) of this blog page. This article is specially for them and the ones who is eager to know about Partition Pruning.


Before we go for "Partition Pruning", it is better to go through couple of articles related to Partitioning in Oracle.  Below are the links related to them . 

Table Partitioning in Oracle

Interval Partitioning in Oracle - 11g New Features

What is Partition Pruning?  

In simple English, Pruning means Eliminating or Ignoring or Filtering.

Partition Pruning is just an activity which happened when SQL statement perform to retrieve the data for a partitioned table. 

Pruning allows the database to access only the relevant partitions and ignore all partitions that are not irrelevant or necessary for the SQL statement.

  

In other words, partition pruning is the act of eliminating, or ignoring the partitions that are irrelevant to the SQL statement's selection criteria.

Benefits of Partition Pruning :-

Pruning is DBA's trump card to enhance performance tuning. Partition pruning significantly reduces the amount of data retrieved from disk and reduces the SQL processing and execution time. 

This improves query performance and optimizing resource utilization.

Static Pruning  Vs  Dynamic Pruning :-

Optimizer decides either Static Pruning or Dynamic Pruning based on the way the SQL statement detects the data of the partitioned object. 

Static Pruning occurs at Compile time of the SQL execution.

Dynamic Pruning occurs at Run time of the SQL execution.

Static Pruning happens when the WHERE condition directly filters the data belonging to particular partition. 

Dynamic Pruning happens when the WHERE condition has any functions or complex literals or Joined with another table.

In other words, Static Pruning happens if a SQL statement containing a WHERE condition with the value passed directly on the partition key column. Otherwise, Dynamic Pruning will happen.

Simply, Static Pruning happens if it comes to know the exact partition before the run time. Dynamic Pruning happens if it comes to know the exact partition after the run time.

Performance wise;  Static Pruning is very faster when compare to dynamic Pruning because it filtered out most of the unwanted resources and data at first step. 

Hope this helps.. if yes, then please share this post and comment your feedback.  

Please LIKE this   FB Page    to get more articles like this.