索引分裂：90-10分裂

数据库的索引基于B-树的结构，通过将数据插入到对应的叶子节点中实现数据新增的功能。更新值的操作是通过"删除"（将值标记为删除）原有的值并在对应的叶子节点上添加修改后的值来实现的。

举个例子，假设现在有一个索引键，并且假设一个数据块只能容纳该索引的4个索引值，然后当前的索引值包含以下这些数据：

Block 1 :
Abacus
Carrot

Block 2 :
Dandelion
Elephant
Heat
Iridium

Block 3 :
Lamb
Militant
Tight

此时，如果想要往这个索引中添加新的索引值 “Fear”， Oracle 需要进行以下的操作步骤：

1. 定位正确的插入数据块 （按首字母 a.b.c.d.. 排列，定位的是第二个数据块）
2. 验证新的值是否可以插入到步骤一种定位的数据块中 （每个块只能插入4个值）
3. 将新的值插入到数据块中或者将数据块进行分裂操作

此时，因为数据块2已经有4个索引值，因此需要做一次索引分裂的操作，分裂后的索引值的分布情况如下：

Block 1 :
Abacus
Carrot

Block 2 :
Dandelion
Elephant

Block 3 :
Fear
Heat
Iridium

Block 4 :
Lamb
Militant
Tight

这个过程就是一个常规的“50-50分裂”的索引分裂过程。

而与此相对应的，是这篇文章要介绍的“90-10分裂”的过程。90-10分裂发生在当新插入的值是当前“最右”的值的时候。简单的说，当这个索引是一个自动正常的类型，比如是一个序列，交易订单的id或者是一个日期类型，交易订单的日期，在这种新插入值总是比当前存在的值都要大的情况下，就会发生90-10分裂，此时，oracle会把“最右”的值复制到分裂产生的新的数据块中。

查询索引分裂产生的数据块的信息，可以通过查询 V$SYSSTAT / V$SESSTAT 表中的 "leaf node splits" 的值来分析。值得注意的是，表中的 leaf node 90-10 splits 这个字段的值，实际上是属于leaf node splits的一个子集，即是说，leaf node splits 的值代表了索引分裂的90-10分裂和50-50分裂的信息。

下面将通过新建一张表，并通过几轮数据新增的操作来展示索引分裂的过程。

Table dropped.

SQL>
SQL> -- REM create the demo table
SQL> create table demo_ibs_90_10 (
2    employee_id  number not null,
3    country_name varchar2(10) not null,
4    dept_name varchar2(18) not null,
5    employee_name  varchar2(128) not null,
6    join_date  date)
7  /

Table created.

创建表的索引

SQL> -- create the index with a PCTFREE of 1 to pack it tightly
SQL> create unique index demo_ibs_90_10_u1 on demo_ibs_90_10 (employee_id,country_name,employee_name) pctfree 1;

Index created.

SQL>

对表进行新增数据操作

SQL> delete source_table;

50653 rows deleted.

SQL> insert into source_table select * from dba_objects where object_id is not null;

50653 rows created.

SQL> select max(object_id) from source_table;

MAX(OBJECT_ID)
--------------
   53214

SQL>

然后创建一个新的会话，并进行数据插入操作，并对插入操作进行分析。

SQL> connect scott/tiger
Connected.
SQL> insert into demo_ibs_90_10
2  select object_id, substr(owner,1,10),substr(object_type,1,18),rpad(object_name,20,dbms_random.string(‘X‘,3)),created
3  from source_table
4  where object_id is not null
5  order by object_id
6  /

50653 rows created.

SQL> commit;

Commit complete.

对插入数据进行分析

SQL>
SQL> select sn.name, ms.value
2  from v$statname sn, v$mystat ms
3  where sn.statistic#=ms.statistic#
4  and sn.name like ‘%leaf node %‘
5  order by 1
6  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits                                                  262
leaf node splits                                                        262

SQL>
SQL> exec dbms_stats.gather_table_stats(‘‘,‘DEMO_IBS_90_10‘,estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select num_rows, distinct_keys, blevel, leaf_blocks, distinct_keys/leaf_blocks
2  from user_indexes where index_name = ‘DEMO_IBS_90_10_U1‘
3  /

NUM_ROWS DISTINCT_KEYS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
50653         50653          1         263                192.596958

SQL> analyze index demo_ibs_90_10_u1 validate structure;

Index analyzed.

SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

LF_ROWS    LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ---------- ----------- ----------
50653        263          1           0        100

这个分析过程，首先从v$statname , v$mystat表中查询到索引分裂的数量信息，然后通过调用dbms_stats的gather_table_stats函数搜集信息，再通过user_indexes 查询展示搜集的数据信息，最后对索引的结构进行分析操作，并通过index_stats查询索引数据块的信息。

结论：

• 在插入50653数据的过程中，产生了262次索引分裂，且都是90-10分裂
• 产生了263个数据块，且每个数据块中大概有193个索引值

接下来进行第二次数据插入操作，需要重新建立一个新的会话，目的是方便分析插入的索引信息。

SQL> connect scott/tiger
Connected.
SQL>
SQL> declare
2  i number;
3
4  begin
5  for i in 1..1000
6  loop
7  insert into demo_ibs_90_10
8  select object_id+100000+i,substr(owner,1,10),substr(object_type,1,18),rpad(object_name,20,dbms_random.string(‘X‘,3)),created+vsize(object_name)
9  from source_table
10  where object_id is not null
11  and object_id = 1000+i;
12  commit;
13  end loop;
14  end;
15  /

PL/SQL procedure successfully completed.

和之前一样，分析插入数据

SQL>
SQL> select sn.name, ms.value
2  from v$statname sn, v$mystat ms
3  where sn.statistic#=ms.statistic#
4  and sn.name like ‘%leaf node %‘
5  order by 1
6  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits                                                    5
leaf node splits                                                          5

SQL>
SQL> exec dbms_stats.gather_table_stats(‘‘,‘DEMO_IBS_90_10‘,estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select num_rows, distinct_keys, blevel, leaf_blocks, distinct_keys/leaf_blocks
2  from user_indexes where index_name = ‘DEMO_IBS_90_10_U1‘
3  /

NUM_ROWS DISTINCT_KEYS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
51653         51653          1         268                192.735075

SQL> analyze index demo_ibs_90_10_u1 validate structure;

Index analyzed.

SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

LF_ROWS    LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ---------- ----------- ----------
51653        268          1           0        100

SQL>

这里，通过新建一个过程，将数据的object_id的值增加100000再进行插入。
可以发现，第二轮插入产生了5次90-10分裂，数据块的数量也增加到了268个。（每个块大概是193个索引值，第二轮中插入了1000个值，所以是分裂了5次）

然后进行第三次的数据插入操作， 操作和上面两轮基本一致。

SQL> connect scott/tiger
Connected.
SQL>
SQL> insert into demo_ibs_90_10
2  select object_id+200000,substr(owner,1,10),substr(object_type,1,18),rpad(object_name,20,dbms_random.string(‘X‘,3)),created+vsize(object_name)
3  from source_table
4  where object_id is not null
5  and object_id between 1000 and 2000
6  /

1001 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select sn.name, ms.value
2  from v$statname sn, v$mystat ms
3  where sn.statistic#=ms.statistic#
4  and sn.name like ‘%leaf node %‘
5  order by 1
6  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits                                                    6
leaf node splits                                                          6

SQL>
SQL> exec dbms_stats.gather_table_stats(‘‘,‘DEMO_IBS_90_10‘,estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select num_rows, distinct_keys, blevel, leaf_blocks, distinct_keys/leaf_blocks
2  from user_indexes where index_name = ‘DEMO_IBS_90_10_U1‘
3  /

NUM_ROWS DISTINCT_KEYS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
 52654         52654          1         274                192.167883

SQL> analyze index demo_ibs_90_10_u1 validate structure;

Index analyzed.

SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used from index_stats;

LF_ROWS    LF_BLKS    BR_BLKS DEL_LF_ROWS   PCT_USED
---------- ---------- ---------- ----------- ----------
 52654        274          1           0        100

SQL>

经过三轮的数据操作，可以发现，对一个单调递增的序列上进行新值的插入，索引分裂是90-10分裂类型的。

思考：
如果在插入的过程中有删除的操作，会发生什么样的情况呢？
实际上，oracle并不总是把新值直接插入到任意的一个数据块中的，即是该数据块当前是90%空闲的。当原有的叶子节点上的旧数据被删除的时候，这个数据块就可以被重复利用，作为当前“最右”的数据块的下一个块（即是说，数据块和索引值并不是物理上的连续的排序，只是逻辑上的往右排序下去）。