1. 背景说明

表碎片过高，会影响统计信息收集效率，对表的全扫或二次回表都会徒增更多页读成本，还占用存储空间
针对以上问题，衍生出了表碎片整理，表存储空间的降水位

2.碎片产生根因

当表存在大量的UPDATE操作索引字段或者delete数据时，会造成表碎片过多

3.表大小及碎片分析

(1)检查表大小

SELECT T.OWNER,
T.TABLE_NAME,
SUM(TABLE_ROWCOUNT(T.OWNER,T.TABLE_NAME)) TAB_ROWS,
TABLE_USED_SPACE(T.OWNER,T.TABLE_NAME)*PAGE/1024/1024 SIZE_MB
FROM DBA_TABLES T
WHERE T.OWNER IN(‘ALS7BATCH’)
AND T.TABLE_NAME=‘ACCT_PAYMENT_SCHEDULE’
GROUP BY T.OWNER,T.TABLE_NAME
ORDER BY 3 DESC;

(2)检查表碎片

第一种方式：碎片率0.27，偏差较大，不建议用

SELECT OBJNAME AS OBJNAME,
OBJTYPE AS OBJTYPE,
TO_CHAR(FRAGPCT) AS FRAGPCT
FROM
(SELECT * FROM
(SELECT
OWNER||’.’||TABLE_NAME AS OBJNAME,
‘TABLE/TABLE PART’ AS OBJTYPE,
ROUND(100.0*(1-TABLE_USED_PAGES(OWNER,TABLE_NAME)/1.0/TABLE_USED_SPACE(OWNER,TABLE_NAME)),2) FRAGPCT
FROM DBA_TABLES
WHERE TABLESPACE_NAME NOT IN (‘TEMP’,‘ROLL’,‘SYSTEM’)
AND OWNER NOT IN (‘SYS’,‘SYSAUDITOR’,‘SYSSSO’,‘SYSJOB’,‘SCHEDULER’)
AND TEMPORARY=‘N’
AND TABLE_USED_SPACE(OWNER,TABLE_NAME)>(SELECT SUM(TOTAL_SIZE)*0.0001 FROM V$DATAFILE)
ORDER BY TABLE_USED_SPACE(OWNER,TABLE_NAME) DESC
LIMIT 10)
ORDER BY FRAGPCT DESC
LIMIT 10)

第二种方式：统计碎片大小为206082MB，碎片率97%，相对较准确

select
/+ GROUP_OPT_FLAG(1)/
(TABLE_USED_PAGES(‘ALS7BATCH’, ‘ACCT_PAYMENT_SCHEDULE’)) * PAGE / 1024.0 / 1024.0 as total_size_MB,
round(s.T_TOTAL * (sum(s.col_avg_len) + 26) / 1024.0 / 1024.0, 2) as “data_size_MB”,
(TABLE_USED_PAGES(‘ALS7BATCH’, ‘ACCT_PAYMENT_SCHEDULE’)) * PAGE / 1024.0 / 1024.0 - round(s.T_TOTAL * (sum(s.col_avg_len) + 26) / 1024.0 / 1024.0, 2) as frag_size_MB
from sysstats s
where s.t_flag = ‘C’
and s.id = (select object_id
from dba_objects
where object_name = ‘ACCT_PAYMENT_SCHEDULE’
and owner = ‘ALS7BATCH’
and object_type = ‘TABLE’);

4.回收碎片操作方案

注意 表碎片整理是个高IO负载操作，而且涉及ddl的操作， 一定要在系统空闲时段处理，千万别业务高峰期处理 避免阻塞或对业务性能造成波动
(1)方案一：将原表数据通过dexp导出，重命名为备份表，创建一张新的正式表，然后通过dimp逻辑导入。
(2)方案二：原表重命名为备份表，创建一张新表，从备份表INSERT INTO数据到新表中。
(3)方案三：创建聚集索引，然后删除聚集索引。
(4)方案四：通过SP_REORGANIZE_INDEX函数对聚集索引进行重组，重组后可成功回收碎片。
SP_REORGANIZE_INDEX(‘索引所属模式名’,‘聚簇索引名’);

5.测试验证步骤

5.1.方案一测试过程

（1）导出表数据
./dexp SYSDBA/’“Hn@dameng123”’:5236 DIRECTORY=/dmdata/dump FILE=/dmdata/dump/ACCT_PAYMENT_SCHEDULE.dmp LOG=dexp_acct_payment_schedule_01.log TABLES=ALS7BATCH.ACCT_PAYMENT_SCHEDULE PARALLEL=8 TABLE_PARALLEL=16

[表: ACCT_PAYMENT_SCHEDULE]导出 ACCT_PAYMENT_SCHEDULE 注释
[表: ACCT_PAYMENT_SCHEDULE]导出 ACCT_PAYMENT_SCHEDULE 列注释
[表: ACCT_PAYMENT_SCHEDULE]导出索引：IDX_PAYMENT_SCHEDULE_3
[表: ACCT_PAYMENT_SCHEDULE]导出索引：IDX_PAYMENT_SCHEDULE_2
[表: ACCT_PAYMENT_SCHEDULE]导出索引：IDX_PAYMENT_SCHEDULE_1
[表: ACCT_PAYMENT_SCHEDULE]导出索引：IDX_ACCT_PAYMENT_SCHEDULE_5_DM
[表: ACCT_PAYMENT_SCHEDULE]导出索引：IDX_APS_OBJECTNO_FINISHDATE_DM
[表: ACCT_PAYMENT_SCHEDULE]导出索引：IDX_PAYMENT_SCHEDULE_4_DM
表ALS7BATCH.ACCT_PAYMENT_SCHEDULE导出结束，共导出 28769843 行数据, 大小 8.442 GB
共导出 1 个TABLE
整个导出过程共花费 1012.822 s

成功终止导出, 没有出现警告
（2）重建表

（3）导入表数据
./dimp SYSDBA/’“Hn@dameng123”’:5236 DIRECTORY=/dmdata/dump FILE=/dmdata/dump/ACCT_PAYMENT_SCHEDULE.dmp LOG=dimp_acct_payment_schedule_01.log PARALLEL=8 TABLE_EXISTS_ACTION=TRUNCATE
dimp V8

version: 05134284294-20250305-262698-20119 Pack30

start dimp:
SYSDBA/******@LOCALHOST:5236 DIRECTORY=/dmdata/dump FILE=/dmdata/dump/ACCT_PAYMENT_SCHEDULE.dmp LOG=dimp_acct_payment_schedule_01.log PARALLEL=8 TABLE_EXISTS_ACTION=TRUNCATE
本地编码：PG_UTF8, 导入文件编码：PG_UTF8

----- [2026-03-24 15:19:37]导入表：ALS7BATCH.ACCT_PAYMENT_SCHEDULE
[表: ACCT_PAYMENT_SCHEDULE]表 ALS7BATCH.ACCT_PAYMENT_SCHEDULE 存在且表中记录被删除
[1/56][表: ACCT_PAYMENT_SCHEDULE]创建表已完成，导入表 ACCT_PAYMENT_SCHEDULE 的数据中…
[表: ACCT_PAYMENT_SCHEDULE]导入表 ALS7BATCH.ACCT_PAYMENT_SCHEDULE 的数据：28769843 行被处理, 大小 59.263 GB

[1/56][表: ACCT_PAYMENT_SCHEDULE]导入 ACCT_PAYMENT_SCHEDULE 注释
[2/56][表: ACCT_PAYMENT_SCHEDULE]导入成功……
[2/56][表: ACCT_PAYMENT_SCHEDULE]导入 ACCT_PAYMENT_SCHEDULE 列注释
[50/56][表: ACCT_PAYMENT_SCHEDULE]导入成功……
[50/56]整个导入过程共花费 648.686 s

成功终止导入, 没有出现警告

5.2.方案二测试过程

[执行语句1]:
INSERT INTO ALS7BATCH.ACCT_PAYMENT_SCHEDULE SELECT * FROM ALS7BATCH.ACCT_PAYMENT_SCHEDULE_260326;
执行成功, 执行耗时21分 8秒 767毫秒. 执行号:2516
影响了28,769,843条记录

1条语句执行成功

5.3.方案三测试过程

[执行语句1]:
CREATE CLUSTER INDEX ALS7BATCH.IDX_CLSTER_CS ON ALS7BATCH.ACCT_PAYMENT_SCHEDULE_260326(SERIALNO) PARALLEL 16;
执行成功, 执行耗时30分 44秒 415毫秒. 执行号:154222
影响了0条记录

[执行语句2]:
DROP INDEX ALS7BATCH.IDX_CLSTER_CS;
执行成功, 执行耗时8分 1秒 415毫秒. 执行号:154223
影响了0条记录

2条语句执行成功

5.4.方案四测试过程

通过SP_REORGANIZE_INDEX函数对二级索引和聚集索引进行重组，原表200GB，根据以下测试结果可以看到，二级索引REORGANIZ后大小是156472M，对聚集索引REORGANIZ后大小为22605M。
序号 REORGANIZ索引 REORGANIZ后大小 耗时
1 IDX_PAYMENT_SCHEDULE_1 198288M 24S
2 IDX_PAYMENT_SCHEDULE_2 193825M 1分30秒
3 IDX_PAYMENT_SCHEDULE_3 166464M 7分3秒
4 IDX_PAYMENT_SCHEDULE_4_DM 165121M 36秒
5 IDX_ACCT_PAYMENT_SCHEDULE_5_DM 159804M 1分46秒
6 IDX_APS_OBJECTNO_FINISHDATE_DM 156472M 1分13秒
7 INDEX33576664 22605M 28分21秒

如何查看表的聚集索引呢？可以用以下2个方法查看

（1）通过SQL查看，SYSINDEX的XTYPE为0则表示聚集索引

SELECT SCH.NAME AS SCHEMA_NAME,
TAB.ID AS TABLE_ID,
TAB.NAME AS TABLE_NAME,
IDX.ID AS INDEX_ID,
IDX.NAME AS INDEX_NAME
FROM SYSOBJECTS IDX ,
SYSINDEXES IDXINFO,
SYSOBJECTS TAB,
SYSOBJECTS SCH
WHERE IDX.ID = IDXINFO.ID
AND TAB.ID = IDX.PID
AND SCH.ID = TAB.SCHID
and SCH.NAME=‘ALS7BATCH’
and TAB.NAME=‘ACCT_PAYMENT_SCHEDULE’
and IDXINFO.XTYPE=0 ;

（2）通过查看SQL执行计划确定，CSCN2扫描的索引就是聚簇索引。

EXPLAIN SELECT * FROM ALS7BATCH.ACCT_PAYMENT_SCHEDULE;
1 #NSET2: [17737, 30177399, 1849]
2 #PRJT2: [17737, 30177399, 1849]; exp_num(49), is_atom(FALSE)
3 #CSCN2: [17737, 30177399, 1849]; INDEX33576664(ACCT_PAYMENT_SCHEDULE); btr_scan(1)

6.测试总结

根据四种不同方案，得出测试结果如下，可知四种方案都能有效整理索引碎片，且效率最高的是方案四，回收碎片后，统计信息收集效率大大提升（只需1分钟，优化前23分钟）。