Partition Pruning with Example

In previous article we saw the benefits of partition pruning.

Here we will see how partition pruning takes place when you run the query accessing your partitioned table.

For instance , you have transaction table from year 2011 to 2019 with different partition for each year. 

And you want statement of your transaction from year 2017 to 2019. 

In traditional way oracle will scan whole table finds your data and gives you output. 

But when you have enabled partitioning oracle will prune the partition and will only access three partition of year 2017,2018 and 2019. 

Let us understand it with practical example. 

Step 1 : Create a table

SQL> CREATE TABLE TRANSACTIONS(
  2    TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER)
PARTITION BY RANGE (DATE_OF_TRANSACTION)
 (PARTITION Y_2011 VALUES LESS THAN
   (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION Y_2012 VALUES LESS THAN
   (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
PARTITION Y_2013 VALUES LESS THAN
   (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
PARTITION Y_2014 VALUES LESS THAN
   (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
PARTITION Y_2015 VALUES LESS THAN
   (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION Y_2016 VALUES LESS THAN
   (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION Y_2017 VALUES LESS THAN
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20     (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION Y_2018 VALUES LESS THAN
   (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION Y_2019 VALUES LESS THAN
   (TO_DATE('01-JAN-2020', 'DD-MON-YYYY'))); 21   22   23   24  

Table created.

SQL> 

Step 2 : Insert some records

SQL> DECLARE 
  2  START_DATE DATE :=TO_DATE('2011-02-01','YYYY-MM-DD');
CON NUMBER:=2000;
BEGIN
FOR X IN 1..200
LOOP
INSERT INTO TRANSACTIONS VALUES(1+(SELECT MAX(NVL(TRAN_ID,0)) FROM TRANSACTIONS), START_DATE , CON*100, CON||123);
COMMIT;
START_DATE := START_DATE+365;
CON:=CON+1;
IF START_DATE > TO_DATE('2019-12-31','YYYY-MM-DD') THEN
START_DATE:=TO_DATE('2011-02-01','YYYY-MM-DD');
END IF;
END LOOP;
END;
/  3    4    5    6    7    8    9   10   11   12   13   14   15   16  

PL/SQL procedure successfully completed.

SQL> 

Check if data insertion is successful.

SQL> SELECT COUNT(*) FROM TRANSACTIONS;

  COUNT(*)
----------
       200

Now let us check explain plan of the query 

SQL> EXPLAIN PLAN FOR SELECT * FROM TRANSACTIONS WHERE DATE_OF_TRANSACTION BETWEEN TO_DATE('01-APR-2017','DD-MON-YYYY') AND TO_DATE('31-MAR-2019','DD-MON-YYYY');

Explained.

SQL> 

check explain plan from dbms_xplan now

SQL> COL PLAN_TABLE_OUTPUT FOR A120               
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1393473582

---------------------------------------------------------------------------------------------------------
| Id  | Operation		 | Name 	| Rows	| Bytes | Cost (%CPU)| Time	| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	 |		|    44 |  3828 |     3   (0)| 00:00:01 |	|	|
|   1 |  PARTITION RANGE ITERATOR|		|    44 |  3828 |     3   (0)| 00:00:01 |     7 |     9 |
|*  2 |   TABLE ACCESS FULL	 | TRANSACTIONS |    44 |  3828 |     3   (0)| 00:00:01 |     7 |     9 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DATE_OF_TRANSACTION">=TO_DATE(' 2017-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
	      AND "DATE_OF_TRANSACTION"<=TO_DATE(' 2019-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL> 

You can see in PSTART and PSTOP column it shows which partition it needs to scan. The query works same way if you have 200 records or you have millions of records in you table.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp