The effect of Add&split partition on Global&local index in the oracle11.2 of partition function test

The partition exception for some large tables in the production library requires an online operation on the existing table to add the missing partition, because it is a production library, or a little cautious, and is available today, testing the impact of the Add&split partition on the global&local index, The test version is oracle11.2.0.4 and the process is as follows:

First, create the partition table:

CREATE TABLE TP1
(
C1 INT PRIMARY KEY,
C2 VARCHAR2 (10),
C3 CHAR (10)
)
Partition by range (C1)
(
Partition P1 values less than (6),
Partition P2 values less than (11),
Partition P3 values less than (16),
Partition MaxValue values less than (MaxValue));
However, when the add partition encountered a problem, the following error:

ORA-14074: The partition bounds must be adjusted above the last partition boundary

The root cause is the last MaxValue partition, and then create a partition table with no MaxValue:

CREATE TABLE TP2
(
C1 INT PRIMARY KEY,
C2 VARCHAR2 (10),
C3 CHAR (10)
)
Partition by range (C1)
(
Partition P1 values less than (6),
Partition P2 values less than (11),
Partition P3 values less than (16));

Then, create the local index:

Create INDEX IDX_TP2_C2 on TP2 (C2) local;

Because the primary key is a global index, you do not need to create another global indexes, we insert the data:

INSERT into TP2 values (1, ' AAA ', ' AAA ');
INSERT into TP2 values (2, ' AAA ', ' AAA ');
INSERT into TP2 values (3, ' AAA ', ' AAA ');
INSERT into TP2 values (4, ' AAA ', ' AAA ');
INSERT into TP2 values (5, ' AAA ', ' AAA ');
INSERT into TP2 values (6, ' AAA ', ' AAA ');
INSERT into TP2 values (7, ' AAA ', ' AAA ');
INSERT into TP2 values (8, ' AAA ', ' AAA ');
INSERT into TP2 values (9, ' AAA ', ' AAA ');
INSERT into TP2 values (' AAA ', ' AAA ');
INSERT into TP2 values (one, ' aaa ', ' AAA ');
INSERT into TP2 values (triple, ' AAA ', ' AAA ');
INSERT into TP2 values (' AAA ', ' AAA ');
INSERT into TP2 values (' AAA ', ' AAA ');
INSERT into TP2 values ("AAA", ' AAA ');
Commit

We add partition:

ALTER TABLE TP2 Add PARTITION p10 values less than (51);

The add partition succeeds because there is no maxvalue partition. Check the primary key and index:

Select Index_name,status from user_indexes where table_name= ' TP2 ';

Select Index_name,partition_name,status from user_ind_partitions where index_name= ' idx_tp2_c2 ' ORDER by Partition_name ;

After checking, add partition has no effect on global and local indexes, such as:

So, split partition:

ALTER TABLE TP2 SPLIT PARTITION p1 at (3) into (PARTITION P1, PARTITION p13);

Finding the split partition causes the Golbal index to be unavailable, and the corresponding partition for the local index is not available, such as:

To rebuild the primary key index:

ALTER INDEX sys_c0027745 REBUILD ONLINE;

After rebuilding the primary key index, the global index and local index status are as follows:

Add the Update indexes option for the split partition command:

ALTER TABLE TP2 SPLIT PARTITION p2 at (8) into (PARTITION P2, PARTITION p28) update indexes;

Check that the split partition has no effect on the global&local index, such as:

Rebuild partitions that are not available for the local index:

ALTER index IDX_TP2_C2 rebuild partition P1 online;

ALTER index IDX_TP2_C2 rebuild partition P13 online;

Check the local index status as follows:

The above is the test process and results, recorded in this, so that other students and their own reference.

The effect of Add&split partition on Global&local index in the oracle11.2 of partition function test