Drop Table Partition with Index Maintenance

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

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 :

SQL> ALTER TABLE TRANSACTIONS_NEW DROP PARTITION Y_2015 UPDATE INDEXES;

Table altered.

Let us see status of index.

SQL> SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS,
 STATUS, TABLESPACE_NAME, PARTITIONED
FROM USER_INDEXES
WHERE TABLE_NAME='TRANSACTIONS_NEW';  2    3    4  

INDEX_NAME															 INDEX_TYPE		     UNIQUENES STATUS	TABLESPACE_NAME 	       PAR
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- --------- -------- ------------------------------ ---
TRANSACTION_NEW_IDX														 NORMAL 		     NONUNIQUE VALID	SYSTEM		            NO

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