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. 

Interval Partitioning in Oracle - 11g new feature

As we are aware of the concept of Partitioning in Oracle which helps in three ways  a) Performance  b) Manageability  and  c) Availability.

Having basic idea about partition ( especially range partition ) will help you here to understand about interval partitioning easily. 

We have an article already posted in this site in explaining about Partitioning, types and benefits. Here we go with the link 

Article about Oracle Partitioning

Assuming you have gone through the above link already; here we start to know about Interval Partitioning. 

Why Interval Partition?  How it works ?

Interval Partition is one of the key new features of Oracle 11i.  We can highlight interval partition as extended version of Range partition. 

For example:  we have created partitions in a table for the below range criteria of data belongs to a column of a table. 

Partition 1    for the data lesser than Jan-2016

Partition 2    for the data between  Jan-2016 to Jun 2016

Partition 3    for the data between  Jul-2016  to Dec 2016

Partition 4    for the data between  Jan-2017 to Jun 2017

Inserting data lesser than Jun-2017  will be placed into its right partition; but when we insert a data greater than Jun 2017; for example .    15-Aug-2017;  we will end up with an error message saying there is no partition available. 

To fix this growing data scenario, we will have to create or add new partitions as a maintenance activity month on month.  So, Interval Partition helps here.  Interval Partition is automatically created new partitions whenever required as per the new data is getting inserted.

Example code  to create Interval Partitioned table :-

CREATE TABLE interval_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('30-Apr-2017','DD-MON-YYYY'))
);

Adding the yellow marked line specially for interval partitioning orders Oracle to create new partitions for every new month data greater than 30-Apr-2017. 

For example;  if we insert into a column "created date"  with the data lesser than 30-Apr-2017 it will be a part of Partition part_01.  If we inserted a data greater than 30-Apr-2017, it will create new partition and then insert the new data. 

The new partitions will be created for  the new months of May-2017, Jun-2017, Jul-2017 etc.,  and so on..  So there is no maintenance activity required.  This will be managed automatically. 

List down all the partitions created for a particular table:-

Below query will provide the details about the partitions created by user and interval partitions created by Oracle automatically.

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
WHERE table_name = <table_name>;

How to add interval partition into already partitioned table?

We are in a scenario of a table which was partitioned already, but we would like to add interval partitioning to avoid further maintenance activities. We can go with an ALTER command. 

ALTER TABLE TABLE_PARTITIONED1 SET INTERVAL(NUMTOMYINTERVAL (1, 'MONTH'));

As we have possibility of creating Composite partitioning  such as ( Range-Hash Partitioning, Range-List Partitioning, Range -Range, List-List and so on.. );

We can have Composite Partitioning with Interval Partitions as well as mentioned below.

• Interval-Hash
• Interval-List
• Interval-Range

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. 

Table Partitioning in Oracle

Partitioning is nothing but the concept of "Divide & Rule"   or   "Categorize them to handle easily".

In order to handle data in huge tables in a easy and quick way,
Oracle has an effective concept " Partitioning Tables"  and  " Partitioned Indexes"

Lets start knowing about what it is all about, how it works and benefits out of it.


Say.. "Hai" to Partitioning  


Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition.

Each Partition has its own name. To retrieve the data, it can be retrieved by calling a table or by calling its partition unit. 


Pictorial view of Partitioned Table and Non Partitioned Table




When actually we can partition a table ?

Huge tables which has data more than 2 GB size can go for partitioning.

Table which has years of data, but mostly we deal with recent months data and old data in a table are just to read.

Table which has data can be categorized into types like Department wise, Country wise, etc.,  So that we can deal with those particular category data alone by naming them as partition.


Types of Partitioning :-

The three types of  partitioning are List, Range and Hash.





Below are the lines to support in explaining the above pictorial view of different partitioning types. 


List Partitioning :-

List partitioning segregates data by its data  which falls into different categories. 

For example:-
The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

A query to retrieve data only from Florida can deal with "East Sales Region" Partition alone.

Example code to create list partition of table 

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

Range Partitioning :-

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.  

Example Dates between 2000 to 2012, 2012 to 2014 , 2012 to 2016 and greater than 2016.  

Example: Revenue less than 1 million, less than 2 million, less than 4 million and Default ( other values will fall in default partition always )


Example Code :-

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
    TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 );

Hash Partitioning:-

No criteria involved. Just spread the data among partitions in equal size. 

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. 

The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Example code :- 

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4 
   STORE IN (gear1, gear2, gear3, gear4);

Composite Partitioning:-

We can have composite partitioning with the combination of  different partitions at different levels. 

Region wise(List) .. within region, year wise. (Range). within year  equal distribution ( hash ) partition..







Benefits of Partitioning :- 

There are three key benefits and they are 

Performance :- By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. It is like taking data from 1 million records rather than 1 billion records is always quicker

Manageability:- With partitioning, maintenance operations can be focused on particular portions of tables. For example, a database administrator could back up a single partition of a table, rather than backing up the entire table

Availability :- if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table.



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