复合分区表的分区索引的rebuild测试

文档课题：复合分区表的分区索引的rebuild测试.
数据库：oracle 19.13
背景介绍：生产环境中对分区表进行split后，因为存储空间的原因，需要对相应的分区索引进行rebuild到其它索引表空间.
当对某分区索引rebuild时却出现 ORA-14287 告警,根据MOS[Doc ID 2059614.1]资料显示，当对复合分区表的索引
进行rebuild操作时，需要对 SUBPARTITION_NAME 进行操作，如下为具体测试过程.

1、异常现象
sys@ORCLCDB> alter index hr.IND_DEPT_INFO_DEPT_STA rebuild partition P1 tablespace OGG_TBS;
alter index hr.IND_DEPT_INFO_DEPT_STA rebuild partition P1 tablespace OGG_TBS
*
ERROR at line 1:
ORA-14287: cannot REBUILD a partition of a composite partitioned index

2、异常模拟
2.1、建测试表
sys@ORCLCDB> CREATE TABLE HR.DEPT_INFO
(deptno number,
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY RANGE (deptno)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE
(SUBPARTITION q1_northwest VALUES ('OR','WA'),
SUBPARTITION q1_northeast VALUES ('NY','VM','NJ'))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (3000),
PARTITION p3 VALUES LESS THAN (maxvalue));

说明：HR.DEPT_INFO表是以deptno进行范围分区，每个分区又根据state列进行列表分区.

2.2、相关查询
sys@ORCLCDB> select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where object_name='DEPT_INFO';

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------------------ ------------------------------ ----------------------- -------------------
HR DEPT_INFO P1 TABLE PARTITION 2024-12-07 16:12:31
HR DEPT_INFO P1_Q1_NORTHEAST TABLE SUBPARTITION 2024-12-07 16:12:31
HR DEPT_INFO P1_Q1_NORTHWEST TABLE SUBPARTITION 2024-12-07 16:12:31
HR DEPT_INFO P2 TABLE PARTITION 2024-12-07 16:12:31
HR DEPT_INFO P2_Q1_NORTHEAST TABLE SUBPARTITION 2024-12-07 16:12:31
HR DEPT_INFO P2_Q1_NORTHWEST TABLE SUBPARTITION 2024-12-07 16:12:31
HR DEPT_INFO P3 TABLE PARTITION 2024-12-07 16:12:31
HR DEPT_INFO P3_Q1_NORTHEAST TABLE SUBPARTITION 2024-12-07 16:12:31
HR DEPT_INFO P3_Q1_NORTHWEST TABLE SUBPARTITION 2024-12-07 16:12:31
HR DEPT_INFO TABLE 2024-12-07 16:12:31

10 rows selected.

sys@ORCLCDB> select table_owner,table_name,partition_name,subpartition_count from dba_tab_partitions where table_name='DEPT_INFO';

TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT
------------------------------ -------------------------------- ------------------ ------------------
HR DEPT_INFO P1 2
HR DEPT_INFO P2 2
HR DEPT_INFO P3 2

sys@ORCLCDB> select table_name,partition_name,subpartition_name,high_value from dba_tab_subpartitions where table_name='DEPT_INFO';

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------
DEPT_INFO P1 P1_Q1_NORTHWEST 'OR', 'WA'
DEPT_INFO P1 P1_Q1_NORTHEAST 'NY', 'VM', 'NJ'
DEPT_INFO P2 P2_Q1_NORTHWEST 'OR', 'WA'
DEPT_INFO P2 P2_Q1_NORTHEAST 'NY', 'VM', 'NJ'
DEPT_INFO P3 P3_Q1_NORTHWEST 'OR', 'WA'
DEPT_INFO P3 P3_Q1_NORTHEAST 'NY', 'VM', 'NJ'

6 rows selected.

sys@ORCLCDB> select owner,segment_name,partition_name,segment_type from dba_segments where segment_name='DEPT_INFO';

no rows selected

sys@ORCLCDB> select owner,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,DEF_SUBPARTITION_COUNT,PARTITIONING_KEY_COUNT,SUBPARTITIONING_KEY_COUNT FROM DBA_PART_TABLES where table_name='DEPT_INFO';

OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT SUBPARTITIONING_KEY_COUNT
------------------------------ ------------------------------ --------- --------- --------------- ---------------------- ---------------------- -------------------------
HR DEPT_INFO RANGE LIST 3 2 1 1

sys@ORCLCDB> select OWNER,table_name,tablespace_name from dba_tables where table_name='DEPT_INFO';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
HR DEPT_INFO

sys@ORCLCDB> select username,default_tablespace from dba_users where username='HR';

USERNAME DEFAULT_TABLESPACE
---------- ------------------------------
HR USERS

sys@ORCLCDB> insert into hr.dept_info values (1,'SALES',20000000,'ACTIVE');
insert into hr.dept_info values (1,'SALES',20000000,'ACTIVE')
*
ERROR at line 1:
ORA-12899: value too large for column "HR"."DEPT_INFO"."STATE" (actual: 6, maximum: 2)

sys@ORCLCDB> insert into hr.dept_info values (1,'SALES',20000000,'AC');
insert into hr.dept_info values (1,'SALES',20000000,'AC')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

sys@ORCLCDB> insert into hr.dept_info values (1,'SALES',20000000,'OR');

1 row created.

sys@ORCLCDB> commit;

Commit complete.

sys@ORCLCDB> select username,default_tablespace from dba_users where username='HR';

USERNAME DEFAULT_TABLESPACE
---------- ------------------------------
HR USERS

sys@ORCLCDB> select owner,segment_name,partition_name,segment_type from dba_segments where segment_name='DEPT_INFO';

OWNER PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
HR P1_Q1_NORTHWEST TABLE SUBPARTITION

2.3、建分区索引
sys@ORCLCDB> CREATE INDEX hr.IND_DEPT_INFO_DEPT_STA on hr.DEPT_INFO (deptno,state) LOCAL;

Index created.

sys@ORCLCDB> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,TABLESPACE_NAME from dba_indexes where index_name='IND_DEPT_INFO_DEPT_STA';

OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE TABLESPACE_NAME
------------------------------ -------------------------------- --------------------------- ------------------------------ ------------------------------ ----------- ------------------------------
HR IND_DEPT_INFO_DEPT_STA NORMAL HR DEPT_INFO TABLE

sys@ORCLCDB> insert into hr.dept_info values (3,'ACC',30000000,'NY');

1 row created.

sys@ORCLCDB> commit;

Commit complete.

sys@ORCLCDB> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,TABLESPACE_NAME from dba_indexes where index_name='IND_DEPT_INFO_DEPT_STA';

OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE TABLESPACE_NAME
------------------------------ -------------------------------- --------------------------- ------------------------------ ------------------------------ ----------- ------------------------------
HR IND_DEPT_INFO_DEPT_STA NORMAL HR DEPT_INFO TABLE

sys@ORCLCDB> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR',tabname => 'DEPT_INFO', cascade =>true,no_invalidate => false,estimate_percent => 100,degree =>8);

PL/SQL procedure successfully completed.

sys@ORCLCDB> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,TABLESPACE_NAME from dba_indexes where index_name='IND_DEPT_INFO_DEPT_STA';

OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE TABLESPACE_NAME
------------------------------ -------------------------------- --------------------------- ------------------------------ ------------------------------ ----------- ------------------------------
HR IND_DEPT_INFO_DEPT_STA NORMAL HR DEPT_INFO TABLE

sys@ORCLCDB> select owner,segment_name,partition_name,tablespace_name from dba_segments where segment_name='IND_DEPT_INFO_DEPT_STA';

OWNER PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
HR P1_Q1_NORTHEAST USERS
HR P1_Q1_NORTHWEST USERS

hr@ORCLPDB> select owner,segment_name,partition_name,tablespace_name,segment_type from dba_segments where segment_name='IND_DEPT_INFO_DEPT_STA';

OWNER SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------
HR IND_DEPT_INFO_DEPT_STA P1_Q1_NORTHEAST USERS INDEX SUBPARTITION
HR IND_DEPT_INFO_DEPT_STA P1_Q1_NORTHWEST USERS INDEX SUBPARTITION

-- 表空间情况如下
TABLESPACE_NAME BLOCK_SIZE MAX_SIZE_MB CURR_SIZE_MB USED_SIZE_MB FREE_SIZE_MB PCT_MAX2CURR PCT_CURR2USED PCT
------------------------------ ---------- ----------- ------------ ------------ ------------ ------------ ------------- ----------
OGG_TBS 8192 32768 1024 1 1023 3.125 .098 0
PDB_OGGTBS 8192 32768 1024 1 1023 3.125 .098 0
USERS 8192 32768 1955 234 1721 5.966 11.969 1
SYSAUX 8192 32768 530 501 29 1.617 94.528 2
SYSTEM 8192 32768 1100 1096 4 3.357 99.636 3
UNDOTBS1 8192 32768 5645 3064 2581 17.227 54.278 9

6 rows selected.

2.3、重现异常
--rebuild索引到其它表空间
sys@ORCLCDB> alter index hr.IND_DEPT_INFO_DEPT_STA rebuild partition P1 tablespace OGG_TBS;
alter index hr.IND_DEPT_INFO_DEPT_STA rebuild partition P1 tablespace OGG_TBS
*
ERROR at line 1:
ORA-14287: cannot REBUILD a partition of a composite partitioned index

3、异常原因
-- 以下为MOS资料.
The error ORA-14287 is expected in the above scenario.

In a composite partitioned table a table partition and a local index partition are logical containers,
table subpartition and index subpartition are physical containers (segments belongs to each), hence these are the ones that need to be moved or rebuilt.

SOLUTION
Rebuild index subpartition not index partition.

You may query the SUBPARTITION_NAME column of the DBA_IND_SUBPARTITIONS view to determine the name of the index subpartitions.

e.g.

ALTER INDEX <index name> REBUILD SUBPARTITION P1_Q1_NORTHWEST;

4、方案解决
sys@ORCLCDB> select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_ind_subpartitions where INDEX_NAME='IND_DEPT_INFO_DEPT_STA';

INDEX_OWNER INDEX_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE_NAME
------------------------------ -------------------------------- --------------- ------------------------------ -------------------------------------------------- ----------------- ------------------------------
HR IND_DEPT_INFO_DEPT_STA P1 P1_Q1_NORTHWEST 'OR', 'WA' 10 USERS
HR IND_DEPT_INFO_DEPT_STA P1 P1_Q1_NORTHEAST 'NY', 'VM', 'NJ' 16 USERS
HR IND_DEPT_INFO_DEPT_STA P2 P2_Q1_NORTHWEST 'OR', 'WA' 10 USERS
HR IND_DEPT_INFO_DEPT_STA P2 P2_Q1_NORTHEAST 'NY', 'VM', 'NJ' 16 USERS
HR IND_DEPT_INFO_DEPT_STA P3 P3_Q1_NORTHWEST 'OR', 'WA' 10 USERS
HR IND_DEPT_INFO_DEPT_STA P3 P3_Q1_NORTHEAST 'NY', 'VM', 'NJ' 16 USERS

6 rows selected.

sys@ORCLCDB> ALTER INDEX hr.IND_DEPT_INFO_DEPT_STA REBUILD SUBPARTITION P1_Q1_NORTHWEST tablespace OGG_TBS;

Index altered.

sys@ORCLCDB> ALTER INDEX hr.IND_DEPT_INFO_DEPT_STA REBUILD SUBPARTITION P1_Q1_NORTHEAST tablespace OGG_TBS;

Index altered.

sys@ORCLCDB> select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_ind_subpartitions where INDEX_NAME='IND_DEPT_INFO_DEPT_STA';

INDEX_OWNER INDEX_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE_NAME
------------------------------ -------------------------------- --------------- ------------------------------ -------------------------------------------------- ----------------- ------------------------------
HR IND_DEPT_INFO_DEPT_STA P1 P1_Q1_NORTHWEST 'OR', 'WA' 10 OGG_TBS
HR IND_DEPT_INFO_DEPT_STA P1 P1_Q1_NORTHEAST 'NY', 'VM', 'NJ' 16 OGG_TBS
HR IND_DEPT_INFO_DEPT_STA P2 P2_Q1_NORTHWEST 'OR', 'WA' 10 USERS
HR IND_DEPT_INFO_DEPT_STA P2 P2_Q1_NORTHEAST 'NY', 'VM', 'NJ' 16 USERS
HR IND_DEPT_INFO_DEPT_STA P3 P3_Q1_NORTHWEST 'OR', 'WA' 10 USERS
HR IND_DEPT_INFO_DEPT_STA P3 P3_Q1_NORTHEAST 'NY', 'VM', 'NJ' 16 USERS

6 rows selected.

说明：如上所示，P1_Q1_NORTHWEST、P1_Q1_NORTHEAST子分区对应的分区索引成功rebuild到OGG_TBS表空间.