In previous article we saw the benefits of partition pruning.

Partition Pruning in Oracle

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

Step 2 : Insert some records

Check if data insertion is successful.

Now let us check explain plan of the query 

check explain plan from dbms_xplan now

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:

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.