1 索引空间需要回收吗？
这是一个dba被经常问到的问题，尤其当下面的情形发生时
- 数据库的存储空间利用率达到预定的上限（比如90%）了，
- 某个索引占用的存储空间大得不可思议（比如几十个G），
- 某个表删除了大量的数据，
- 某个所以应该创建了很长时间（比如一年）
这时dba就会被问是不是应该收缩索引的空间了。有些时候，我们也会想当然的认为，索引的存储空间应该定期回收。这篇文章通过一个简单的实验来说明什么索引空间情况下需要回收，怎样回收，回收的效果如何检验。
2 实验环境准备
--- 创建表，表有两列，person_id 为主键
create table people (
person_id integer not null
constraint person_pk primary key,
given_name varchar2(20) not null
);
---在name列上创建索引
create index per_name_i
on people ( given_name );
---载入数据，person_id 是递增的，given_name列则是随机分布
insert into people
with rws as (
select level x from dual
connect by level <= 100
)
select rownum, dbms_random.string ( 'U', 10 )
from rws
cross join rws;
---收集统计信息
BEGIN
dbms_stats.gather_table_stats ( null, 'people' );
END;
---查询索引信息
SQL> select index_name, leaf_blocks, bytes
from dba_indexes ui join dba_segments us on ui.index_name = us.segment_name
where table_name = 'PEOPLE' order by index_name
SQL> INDEX_NAME LEAF_BLOCKS BYTES
-------------------------------- ----------- ----------
PERSON_PK 18 196608
PER_NAME_I 38 327680
-------------------------------- ----------- ----------
3 删除一半数据后索引空间的使用
3.1 第一种情况，删除主键小于等于5000的数据
delete FROM people where person_id <= 5000;
---收集统计信息后再次查询索引块数和字节数
INDEX_NAME LEAF_BLOCKS BYTES
-------------------------------- ----------- ----------
PERSON_PK 10 196608
PER_NAME_I 38 327680
-------------------------------- ----------- ----------
主键索引PERSON_PK存储person_id值小于5000的页块都空了，数据库把这些块释放回索引PERSON_PK，索引PERSON_PK可以重用这些空间，索引使用的块数变为10个（减小的8个因为删除数据而清空）。
但是删除的这5000条数据在索引PER_NAME_I中的分布是离散的，这5000条数据可能在所有索引PER_NAME_I的所有块中，PER_NAME_I保持同样的大小，叶块数目保持不变。
3.2 第二种情况，删除主键值为偶数的数据
truncate table people;
---再次运行前面的重新insert语句插入数据，索引统计信息如下
INDEX_NAME |LEAF_BLOCKS| BYTES
--------------------------------|-----------|----------
PERSON_PK | 18| 262144
PER_NAME_I | 33| 393216
-------------------------------- ----------- ----------
---删除一半数据，这次删除的数据分布到索引PERSON_PK的叶块中
delete FROM people where mod ( person_id, 2 ) = 0;
---再次运行前面的重新insert语句插入数据，索引统计信息如下
INDEX_NAME |LEAF_BLOCKS| BYTES
--------------------------------|-----------|----------
PERSON_PK | 18| 262144
PER_NAME_I | 33| 393216
--------------------------------|-----------|----------
由于这次删除的数据分布在PERSON_PK的每个叶块中，删除数据没有清空任何一个叶块，主键索引的叶块数也没有变化。
3.3 删除后插入数据
---插入5000行数据，主键值从表中的最大值10000开始，名字随机分布
insert into people
with rws as (
select level x from dual
connect by level <= 100
)
select rownum + 10000, dbms_random.string ( 'U', 10 )
from rws
cross join rws
where rownum <= 5000;
---分析表后检查索引大小
INDEX_NAME |LEAF_BLOCKS| BYTES
--------------------------------|-----------|----------
PERSON_PK | 27| 393216
PER_NAME_I | 35| 458752
--------------------------------|-----------|----------
主键索引的叶块数量和大小（bytes）都有明显增加，这是因为这次插入的行主键值大于表中原来的最大值，新行不能重用之前删除的空间，需要插入新的叶块中。
PER_NAME_I的叶块数量增加了一个，大小（bytes）也稍有变化，这是因为name值是随机的，可以重用之前删除的空间，绝大部分新增数据都插入到了原来的叶块中。
3.4 索引空间需要回收的情形
由上面的小实验可以看出，在索引键递增分布时，如果删除了大量的数据，而新插入的数据的键大于表内最大的键，这时新插入的数据不能使用先前数据删除后的空闲空间，必须写入到新的空间。这种情况下，需要对索引的空间进行回收。如果索引的键时随机分布的，除非删除数据后不再写入，或写入的数据较删除的数据少很多，则所以空间不必要回收。
4 索引空间回收
4.1 释放空快给索引
---下面的命令合并索引中的空闲空间，将空快释放给索引
alter index person_pk COALESCE;
alter index per_name_i COALESCE;
---收集统计信息后查询索引信息
INDEX_NAME |LEAF_BLOCKS| BYTES
--------------------------------|-----------|----------
PERSON_PK | 20| 393216
PER_NAME_I | 33| 458752
--------------------------------|-----------|----------
主键索引PERSON_PK的叶块数量明显减少，大小没有变化，这是因为COALESCE操作合并了空闲空间并将空块释放回给索引。
4.2 释放索引空闲空间
SQL> alter index test.per_name_i shrink SPACE;
alter index test.per_name_i shrink SPACE
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
Help: https://docs.oracle.com/error-help/db/ora-10635/
报上面这个错误的原因是索引所在的表空间不是ASSM，
SELECT TABLESPACE_NAME, SEGMENT_SPACE_MANAGEMENT
FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN (SELECT DEFAULT_TABLESPACE FROM dba_users WHERE username='TEST' ) ; 2
TABLESPACE_NAME |SEGMEN
------------------------------|------
SYSTEM |MANUAL
------------------------------|------
---在此运行shrink命令
INDEX_NAME |LEAF_BLOCKS| BYTES
--------------------------------|-----------|----------
PERSON_PK | 20| 262144
PER_NAME_I | 33| 327680
索引的大小发生了变化。
SQL> SELECT FILE_NAME,TABLESPACE_NAME, BYTES FROM dba_data_files WHERE TABLESPACE_NAME='TBS_TEST' ;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME | BYTES
------------------------------|----------
/opt/oracle/oradata/FREE/FREEPDB1/FREE/3D287CDA19DC017BE063020011AC34DE/datafile
/o1_mf_tbs_test_nf9wntfv_.dbf
TBS_TEST | 104857600
insert into people
with rws as (
select level x from dual
connect by level <= 100
)
select rownum + 15000, dbms_random.string ( 'U', 10 )
from rws
cross join rws
where rownum <= 5000;
SQL> select index_name, leaf_blocks, bytes
from dba_indexes ui join dba_segments us on ui.index_name = us.segment_name
where table_name = 'PEOPLE' order by index_name 2 3 ;
INDEX_NAME |LEAF_BLOCKS| BYTES
--------------------------------|-----------|----------
PERSON_PK | 30| 393216
PER_NAME_I | 64| 655360
所有的主键值都加在了索引的右手边，因此索引大小增加了接近50%；增长和行的增加接近成线性。但是per_name_i 增长了接近一倍的空间。这是因为加入的新值遍布整个索引，这导致了许多50/50块分裂。
通过coalesce + shrink释放索引空间在大部分情况下没有长久的好处。在多数情况下，试图恢复浪费的空间是不必要的，在增加新值时索引会迅速恢复到原来的空间。
有一种例外是当你插入的值总是增长时。比如是主键是序列值，或者插入的是时间值。如本例中的PERSON_PK。
5 收缩数据文件
Oracle 23AI数据库可以在线收缩数据文件，早期版本只适用与大文件表空间，最新版本已经能用用于普通文件的表空间了。
--检查现在索引的存储信息
INDEX_NAME |LEAF_BLOCKS| BYTES
--------------------------------|-----------|----------
PERSON_PK | 23| 262144
PER_NAME_I | 64| 393216
--------------------------------|-----------|----------
--检查数据文件大小
SQL> SELECT FILE_NAME,TABLESPACE_NAME, BYTES FROM dba_data_files WHERE TABLESPACE_NAME='TBS_TEST';
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME | BYTES
------------------------------|----------
/opt/oracle/oradata/FREE/FREEPDB1/FREE/3D287CDA19DC017BE063020011AC34DE/datafile/o1_mf_tbs_test_nf9wntfv_.dbf
TBS_TEST | 104857600
--执行表空间收缩函数
SQL> execute dbms_space.shrink_tablespace('TBS_TEST');
PL/SQL procedure successfully completed.
--检查数据文件大小
SQL> SELECT FILE_NAME,TABLESPACE_NAME, BYTES FROM dba_data_files WHERE TABLESPACE_NAME='TBS_TEST';
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME | BYTES
------------------------------|----------
/opt/oracle/oradata/FREE/FREEPDB1/FREE/3D287CDA19DC017BE063020011AC34DE/datafile/o1_mf_tbs_test_nf9wntfv_.dbf
TBS_TEST | 8585216
数据文件字节数由104857600减小到了8585216。
6 索引块的分裂
在看awr报告时，可以看到索引两种分裂方式，50-50分裂和90-10分裂，这两种分裂方式的区分如下
1） 50-50分裂
场景：当非顺序的新数据插入到已满的索引块中间位置时发生。
操作：Oracle会将现有数据一分为二，旧块和新块各容纳约50%的数据，新的索引条目根据键值大小插入到其中一个块。
类比：像从中间撕开一本写满的笔记本，然后把两半分别装订成两个新本子。
影响：这是最平衡但也是开销最大的分裂方式，会产生额外的I/O和空间浪费。
2） 90-10分裂
场景：当顺序的新数据（例如递增的ID、时间戳）插入到已满的索引块，且新值是当前最大值时发生。
操作：Oracle不会平均分割。原块保留约90% 的现有数据，仅创建一个新的空块来存放最新的数据。
类比：一本写满的日记本，你只需要在最后加上一本新的空白日记本继续写。
影响：这是最高效的分裂方式，完美适配顺序插入，空间利用率高，I/O开销最小。
当一个索引总是作90-10分裂时，如果删除了大量数据，这时可能就需要回收索引存储空间，否则会造成空间的浪费，比如本文中《3.2 第二种情况，删除主键值为偶数的数据》描述的情形。
