Oracle partitioned table Drop and truncate partition index expiration ____oracle

In the DBA's maintenance Management, index invalidation is a common problem, such as move, CTAs, swap partitions and so on will cause the index invalidation, the following experiment is to verify the drop partition, truncate partition partition on the local index and global index, in common, TRUNCATE TABLE tables are not invalidated by indexes.

Basic Environment

Sql> CREATE TABLE Ou_part (a integer)
2 partition by range (a)
3 (
4 PARTITION ou_part_01 VALUES less than tablespace ts_ou_01,
5 partition ou_part_02 values less than tablespace ts_ou_02,
6 partition ou_part_03 values less than tablespace ts_ou_03,
7 partition ou_part_04 values less than () tablespace ts_ou_04
8);

Table created

Executed in 0.016 seconds

sql> INSERT INTO Ou_part values (1);

1 row inserted

Executed in 0 seconds

sql> INSERT INTO Ou_part values (11);

1 row inserted

Executed in 0 seconds

sql> INSERT INTO Ou_part values (21);

1 row inserted

Executed in 0 seconds

sql> INSERT INTO Ou_part values (31);

1 row inserted

Executed in 0 seconds

Sql> commit;

Commit Complete

Experiment 1
Sql> CREATE index Index_glo on Ou_part (a) global;

Index created

Executed in 0 seconds

Sql> Select Status, index_name from user_indexes where index_name = ' Index_glo ';

STATUS index_name
-------- ------------------------------
VALID Index_glo

Executed in 0.015 seconds

Sql> ALTER TABLE Ou_part truncate partition ou_part_01;

Table truncated

Executed in 0 seconds

Sql> select * from Ou_part;

A
---------------------------------------
11
21st
31

Executed in 0.016 seconds

At this point the A=1 data was found to have been deleted.
Sql> Select Status, index_name from user_indexes where index_name = ' Index_glo ';

STATUS index_name
-------- ------------------------------
Unusable Index_glo

Executed in 0 seconds
The index becomes unavailable at this point, indicating that the index will fail when truncate a partition.
sql> ALTER index INDEX_GLO rebuild;

Index Altered

Executed in 0 seconds

Sql> Select Status, index_name from user_indexes where index_name = ' Index_glo ';

STATUS index_name
-------- ------------------------------
VALID Index_glo

Executed in 0 seconds

sql> ALTER TABLE Ou_part drop partition ou_part_02;

Table Altered

Executed in 0 seconds

Sql> select * from Ou_part;

A
---------------------------------------
21st
31

Executed in 0 seconds

Sql> Select Status, index_name from user_indexes where index_name = ' Index_glo ';

STATUS index_name
-------- ------------------------------
Unusable Index_glo

Executed in 0 seconds

The index is unavailable at this time, and the index is not available after the drop partition

Experiment 1 shows that the global index is invalidated after drop partition, truncate partition, and a parameter update global indexes is provided for global index,oracle. Can avoid truncate or drop partition when indexing failure problem, another method is rebuild, these 2 methods have advantages and disadvantages, in the production of different environmental methods are not the same.

Experiment II

Sql> CREATE index Index_loc on Ou_part (a) local;

Index created

Executed in 0 seconds

Sql> Select A.status from User_ind_partitions a where a.index_name = ' index_loc ';

STATUS
--------
USABLE
USABLE
USABLE
USABLE

Executed in 0.016 seconds
Sql> Select status from user_indexes where index_name = ' index_loc ';

STATUS
--------
N/A

Executed in 0.016 seconds

Sql> ALTER TABLE Ou_part truncate partition ou_part_01;

Table truncated

Executed in 0.031 seconds

Sql> Select A.status from User_ind_partitions a where a.index_name = ' index_loc ';

STATUS
--------
USABLE
USABLE
USABLE
USABLE

Executed in 0.016 seconds
When truncate partition is found, local indexes are not invalidated, indicating that local indexes are maintained when truncate partition
sql> ALTER TABLE Ou_part drop partition ou_part_01;

Table Altered

Executed in 0 seconds

Sql> Select A.status from User_ind_partitions a where a.index_name = ' index_loc ';

STATUS
--------
USABLE
USABLE
USABLE

Executed in 0.016 seconds

For local indexes, when a partition of a partitioned table is deleted, the index of the partition is deleted together.

Oracle provides 2 processing methods for index invalidation issues

1, update global indexes, this method is mainly for the global index

2, rebuild, support global index and local index

This is mainly about drop, truncate, that add, split, merge division will be different, or there are other places to pay attention to.