We can simply add and drop partitions from table as we are dropping and creating a new table.

While dropping and adding table partition , we need to take care of the index too. 

In previous post we saw simply removing partitions makes index unusable.

For Reference

Drop Table Partition without Index Maintenance and SKIP_UNUSABLE_INDEXES parameter

Now in this post we will see at run time how we can update the indexes. 

Note : Updating index at run time takes more time and CPU make sure you are doing it in non peak hours. And if it is peak hours you can refer above post and skip unusable index for your routine DML and rebuild it later.

In this article we will see how we can add and drop partition of the tables with index maintenance.

To update the indexes as when we add or drop partition , you need to use update indexes clause .

Let us see examples :

1.Drop table partition with update indexes clause :

Let us see status of index.

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

About The Author

Leave a Reply

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