接本地分区索引spit测试-CSDN博客

测试全局索引split测试

同样：注意是非复合分区表，没有涉及到数据移动，全局索引没有失效。但还需要检查。分区如果有1条记录移动了，全局索引和本地索引都失效，需重建。

测试发现复合分区表全局索引和本地索引都失效，需重建。

SQL> ALTER TABLE part_tab_drop MERGE PARTITIONS P5,P_MAX INTO PARTITION P_MAX;

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          UNUSABLE

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION P_MAX;

Index altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> create index idx_part_tab_drop_col3 on part_tab_drop (col3) ;

Index created.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         VALID

SQL> ALTER TABLE part_tab_drop SPLIT PARTITION P_MAX AT (50001) INTO (  PARTITION p5,  PARTITION P_MAX);

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P5                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> 
SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         VALID

SQL> 
SQL> 

 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          UNUSABLE

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION P_MAX;

Index altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> create index idx_part_split_col3 on part_tab_split (col3) ;
create index idx_part_split_col3 on part_tab_split (col3)
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create index idx_part_tab_drop_col3 on part_tab_drop (col3) ;

Index created.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         VALID

SQL> ALTER TABLE part_tab_drop SPLIT PARTITION P_MAX AT (50001) INTO (  PARTITION p5,  PARTITION P_MAX);

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P5                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> 
SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         VALID

SQL> 
SQL> 
SQL> 
SQL> ALTER TABLE part_tab_drop MERGE PARTITIONS P5,P_MAX INTO PARTITION P_MAX;

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          UNUSABLE

SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         UNUSABLE

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION P_MAX;

Index altered.

SQL> ALTER INDEX IDX_PART_TAB_DROP_COL3 REBUILD;

Index altered.

SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         VALID

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> ALTER TABLE part_tab_drop SPLIT PARTITION P_MAX AT (50001) INTO (  PARTITION p5,  PARTITION P_MAX);

Table altered.

SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         VALID

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P5                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> ALTER TABLE part_tab_drop MERGE PARTITIONS P5,P_MAX INTO PARTITION P_MAX;

Table altered.

SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         UNUSABLE

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          UNUSABLE

SQL> ALTER INDEX IDX_PART_TAB_DROP_COL3 REBUILD;

Index altered.

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION P_MAX;

Index altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> select index_name,status from user_indexes where index_name = 'IDX_PART_TAB_DROP_COL3';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_TAB_DROP_COL3         VALID

SQL> ALTER TABLE part_tab_drop ADD CONSTRAINT yy PRIMARY KEY(id);

Table altered.

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         VALID
YY                             VALID

SQL> ALTER TABLE part_tab_drop SPLIT PARTITION P_MAX AT (50001) INTO (  PARTITION p5,  PARTITION P_MAX);

Table altered.

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         VALID
YY                             VALID

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P5                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> 
SQL> ALTER TABLE part_tab_drop MERGE PARTITIONS P5,P_MAX INTO PARTITION P_MAX;

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          UNUSABLE

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         UNUSABLE
YY                             UNUSABLE

SQL> ALTER INDEX IDX_PART_TAB_DROP_COL3 REBUILD;

Index altered.

SQL> ALTER INDEX yy REBUILD;

Index altered.

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION P_MAX;

Index altered.

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         VALID
YY                             VALID

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> ALTER TABLE part_tab_drop SPLIT PARTITION P_MAX AT (50000) INTO (  PARTITION p5,  PARTITION P_MAX);

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where table_name='PART_TAB_DROP';       
select index_name, partition_name, status from user_ind_partitions  where table_name='PART_TAB_DROP'
                                                                          *
ERROR at line 1:
ORA-00904: "TABLE_NAME": invalid identifier

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P5                             UNUSABLE
IDX_PART_DROP_COL2             P_MAX                          UNUSABLE

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         UNUSABLE
YY                             UNUSABLE

SQL> ALTER INDEX IDX_PART_TAB_DROP_COL3 REBUILD;

Index altered.

SQL> ALTER INDEX yy REBUILD;

Index altered.

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION P_MAX;

Index altered.

SQL> 
SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         VALID
YY                             VALID

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P5                             UNUSABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> ALTER TABLE part_tab_drop MERGE PARTITIONS P5,P_MAX INTO PARTITION P_MAX;

Table altered.

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         UNUSABLE
YY                             UNUSABLE

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          UNUSABLE

SQL> ALTER INDEX yy REBUILD;

Index altered.

SQL> ALTER INDEX IDX_PART_TAB_DROP_COL3 REBUILD;

Index altered.

SQL> ALTER INDEX IDX_PART_DROP_COL2 REBUILD PARTITION P_MAX;

Index altered.

SQL> 
SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         VALID
YY                             VALID

SQL> 

SQL> ALTER TABLE part_tab_drop SPLIT PARTITION P_MAX AT (50001) INTO (  PARTITION p5,  PARTITION P_MAX);

Table altered.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P5                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         VALID
YY                             VALID

SQL> insert into part_tab_drop values(500002,500003,500004,'test');

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name, partition_name, status from user_ind_partitions  where index_name = 'IDX_PART_DROP_COL2';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_PART_DROP_COL2             P1                             USABLE
IDX_PART_DROP_COL2             P2                             USABLE
IDX_PART_DROP_COL2             P5                             USABLE
IDX_PART_DROP_COL2             P_MAX                          USABLE

SQL> select index_name,status from user_indexes where table_name='PART_TAB_DROP';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_PART_DROP_COL2             N/A
IDX_PART_TAB_DROP_COL3         VALID
YY                             VALID

SQL>