Impact of new HASH partitions on index status

Oracle always thinks that the operations on all partitions are the same. Only data changes will invalidate the partition status. I didn't expect the implementation methods of HASH partitions to be different.

A Study on adding new partitions to a HASH partition table:

 

Let's look at an example of range partition SPLIT:

SQL> CREATE TABLE T_PART
2 (id number, NAME VARCHAR2 (30 ))
3 partition by range (ID)
4 (PARTITION P1 values less than (10 ),
5 partition pmax values less than (MAXVALUE ));

Table created.

SQL> INSERT INTO T_PART
2 select rownum, TNAME
3 from tab;

12 rows created.

SQL> CREATE INDEX IND_T_PART_ID ON T_PART (ID) LOCAL;

Index created. tb

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
2 FROM USER_IND_PARTITIONS
3 WHERE INDEX_NAME = 'ind _ T_PART_ID ';

INDEX_NAME PARTITION_NAME STATUS
--------------------------------------------------------------------
IND_T_PART_ID P1 USABLE
IND_T_PART_ID PMAX USABLE

SQL> SELECT COUNT (*) FROM T_PART PARTITION (PMAX );

COUNT (*)
----------
3

SQL> ALTER TABLE T_PART SPLIT PARTITION PMAX AT (20)
2 INTO (PARTITION P2, PARTITION P3 );

Table altered.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
2 FROM USER_IND_PARTITIONS
3 WHERE INDEX_NAME = 'ind _ T_PART_ID ';

INDEX_NAME PARTITION_NAME STATUS
--------------------------------------------------------------------
IND_T_PART_ID P2 USABLE
IND_T_PART_ID P3 USABLE
IND_T_PART_ID P1 USABLE

As you can see, for a range partition, even if the SPLIT contains the data partition, as long as the data does not actually change, it will not cause index failure. Here, we SPLIT the PMAX partition into P2 and P3 partitions. All data in PMAX enters the P2 partition, while the P3 partition is empty. In this case, no data changes, therefore, the status of all partition indexes does not change to UNUSABLE.

However, the add partition of the hash partition does not comply with this rule. In fact, every time you ADD a PARTITION, data in a PARTITION is split, and the splitting result does not matter whether the data in the original PARTITION is changed, the index status of the original partition changes to UNUSABLE. The index status of the new partition depends on whether data changes.

SQL> CREATE TABLE T_HASH
2 (id number)
3 partition by hash (ID)
4 (PARTITION P1,
5 PARTITION P2,
6 PARTITION P3,
7 PARTITION P4 );

Table created.

SQL> CREATE INDEX IND_T_HASH_ID ON T_HASH (ID) LOCAL;

Index created.

SQL> INSERT INTO T_HASH SELECT ROWNUM FROM TAB;

12 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
2 FROM USER_IND_PARTITIONS
3 WHERE INDEX_NAME = 'ind _ T_HASH_ID ';

INDEX_NAME PARTITION_NAME STATUS
--------------------------------------------------------------------
IND_T_HASH_ID P1 USABLE
IND_T_HASH_ID P2 USABLE
IND_T_HASH_ID P3 USABLE
IND_T_HASH_ID P4 USABLE

SQL> SELECT * FROM T_HASH PARTITION (P1 );

ID
----------
6
11

SQL> SELECT * FROM T_HASH PARTITION (P2 );

ID
----------
9
10
12

SQL> SELECT * FROM T_HASH PARTITION (P3 );

ID
----------
2
5
8

SQL> SELECT * FROM T_HASH PARTITION (P4 );

ID
----------
1
3
4
7

Add a PARTITION P5 below:

SQL> ALTER TABLE T_HASH ADD PARTITION P5;

Table altered.

SQL> SELECT * FROM T_HASH PARTITION (P5 );

No rows selected

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
2 FROM USER_IND_PARTITIONS
3 WHERE INDEX_NAME = 'ind _ T_HASH_ID ';

INDEX_NAME PARTITION_NAME STATUS
--------------------------------------------------------------------
IND_T_HASH_ID P5 USABLE
IND_T_HASH_ID P1 UNUSABLE
IND_T_HASH_ID P2 USABLE
IND_T_HASH_ID P3 USABLE
IND_T_HASH_ID P4 USABLE

The newly added PARTITION P5 does not contain any data, that is, no data is migrated from P1 to P5, but the status of the PARTITION index is found, the index status of the shard corresponding to P1 has changed to UNUSABLE. This is totally different from the processing method of range partitions. The P5 partition does not have any data, so the partition status is USABLE.

SQL> ALTER TABLE T_HASH ADD PARTITION P6;

Table altered.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
2 FROM USER_IND_PARTITIONS
3 WHERE INDEX_NAME = 'ind _ T_HASH_ID ';

INDEX_NAME PARTITION_NAME STATUS
--------------------------------------------------------------------
IND_T_HASH_ID P5 USABLE
IND_T_HASH_ID P6 UNUSABLE
IND_T_HASH_ID P1 UNUSABLE
IND_T_HASH_ID P2 UNUSABLE
IND_T_HASH_ID P3 USABLE
IND_T_HASH_ID P4 USABLE

6 rows selected.

SQL> DELETE T_HASH WHERE ID = 5;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE T_HASH ADD PARTITION P7;

Table altered.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
2 FROM USER_IND_PARTITIONS
3 WHERE INDEX_NAME = 'ind _ T_HASH_ID ';

INDEX_NAME PARTITION_NAME STATUS
--------------------------------------------------------------------
IND_T_HASH_ID P5 USABLE
IND_T_HASH_ID P6 UNUSABLE
IND_T_HASH_ID P7 UNUSABLE
IND_T_HASH_ID P1 UNUSABLE
IND_T_HASH_ID P2 UNUSABLE
IND_T_HASH_ID P3 UNUSABLE
IND_T_HASH_ID P4 USABLE

7 rows selected.

SQL> SELECT * FROM T_HASH PARTITION (P3 );

No rows selected

SQL> SELECT * FROM T_HASH PARTITION (P7 );

ID
----------
2
8

To better illustrate this problem, we deleted the record with ID 5 before adding PARTITION P7. After adding partitions, we can find that the original P3 does not contain any data, all records are directed to the newly added P7 partition, but the status is UNUSABLE whether it is P3 or P7. This proves that, as long as a HASH partition is added, the index status of the source partition will change to UNUSABLE, unless it is a situation where the source partition itself has no data:

SQL> ALTER TABLE T_HASH ADD PARTITION P8;

Table altered.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
2 FROM USER_IND_PARTITIONS
3 WHERE INDEX_NAME = 'ind _ T_HASH_ID ';

INDEX_NAME PARTITION_NAME STATUS
--------------------------------------------------------------------
IND_T_HASH_ID P5 USABLE
IND_T_HASH_ID P6 UNUSABLE
IND_T_HASH_ID P7 UNUSABLE
IND_T_HASH_ID P1 UNUSABLE
IND_T_HASH_ID P2 UNUSABLE
IND_T_HASH_ID P3 UNUSABLE
IND_T_HASH_ID P4 USABLE
IND_T_HASH_ID P8 USABLE

8 rows selected.

In fact, for the add partition operation of HASH partitions, Oracle basically adheres to the idea that no data changes will cause index failure. The only difference is that Oracle does not final verify whether data migration has actually occurred when the source partition contains records.