12c之后，分区表的索引size异常增大_alter index coalesce cleanup-优快云博客

在Oracle 12c中，发现分区表的全局索引大小显著增加。分析发现，从12.1版本开始，由于分区表DDL操作的元数据优化，导致全局索引维护延迟，造成空间未被有效利用。应用补丁后，通过ALTER INDEX COALESCE CLEANUP命令，成功解决了索引空间异常增大的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

12c之后，分区表的索引size异常增大

最近做了一个case，现象是：
分区表的Global index size在12c后增加特别大，跟11.2 有很大的不同。

测试case：

SET TERM OFF 
SET LINESIZE 30000 
SET PAGESIZE 30000 
SET TRIMSPOOL ON
alter session set nls_date_format= 'YYYY/MM/DD HH24:MI:SS';
SPOOL testcase.log

CREATE TABLE TEST_TBL_01 (
  PART_KEY  CHAR(8) NOT NULL
 ,COL01 CHAR(3)  NOT NULL
 ,COL02 CHAR(10)  NOT NULL
 ,COL03 NUMBER  NOT NULL
 ,COL04 CHAR(50)
 ,COL05 CHAR(50)
 ,COL06 CHAR(50)
 ,COL07 CHAR(50)
 ,COL08 CHAR(50)
) 
PARTITION BY RANGE (PART_KEY)
(
  PARTITION PT_20210401 VALUES LESS THAN (20210402),
  PARTITION PT_20210402 VALUES LESS THAN (20210403),
  PARTITION PT_20210403 VALUES LESS THAN (20210404),
  PARTITION PT_99999999 VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE TEST_TBL_01
ADD CONSTRAINTS TEST_TBL_01_PK
PRIMARY KEY (COL01,COL02,COL03);


BEGIN
  FOR II IN 1..99999 LOOP
    INSERT INTO TEST_TBL_01 VALUES('20210401','001','AAAAAAAAAA',II,'A001','B001','C001','D001','E001');
    INSERT INTO TEST_TBL_01 VALUES('20210402','002','AAAAAAAAAA',II,'A001','B001','C001','D001','E001');
    INSERT INTO TEST_TBL_01 VALUES('20210403','003','AAAAAAAAAA',II,'A001','B001','C001','D001','E001');
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'TEST%';

ALTER TABLE TEST_TBL_01 DROP PARTITION PT_20210401 UPDATE INDEXES;
ALTER TABLE TEST_TBL_01 SPLIT PARTITION PT_99999999 AT ('20210405') INTO (PARTITION PT_20210404,PARTITION PT_99999999) UPDATE INDEXES;
BEGIN
  FOR II IN 1..99999 LOOP
    INSERT INTO TEST_TBL_01 VALUES('20210404','004','AAAAAAAAAA',II,'A001','B001','C001','D001','E001');
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'TEST%';

ALTER TABLE TEST_TBL_01 DROP PARTITION PT_20210402 UPDATE INDEXES;
ALTER TABLE TEST_TBL_01 SPLIT PARTITION PT_99999999 AT ('20210406') INTO (PARTITION PT_20210405,PARTITION PT_99999999) UPDATE INDEXES;
BEGIN
  FOR II IN 1..99999 LOOP
    INSERT INT