B树叶子节点split

来源：互联网发布：ip网络对讲终端编辑：程序博客网时间：2024/04/27 16:15
一、B-Tree索引的分裂1. 创建测试表SQL> create table split_tab (id number, name varchar2(100));表已创建。SQL> alter table split_tab add constraint pk_split_tab primary key (id) using index;表已更改。SQL> create sequence seq_alex_tab  2  minvalue 1  3  maxvalue 99999999  4  start with 1  5  increment by 1  6  cache 200;序列已创建。2. 插入1万条数据SQL> declare    begin    for i in 1..10000 loop    insert into split_tab values (i, 'split');    end loop;   commit;   end;  2    3    4    5    6    7    8  /3.搜集下统计信息:BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',                                tabname          => 'SPLIT_TAB',                                estimate_percent => 30,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;3. 分析一下索引结构SQL> col ratio for a20SQL> col segment_name for a30SQL> analyze index PK_ALEX_TAB validate structure;索引已分析SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'PK_ALEX_TAB';    HEIGHT RATIO                  PCT_USED---------- -------------------- ----------         2 0%                           93SQL> select segment_name,bytes from user_segments where segment_name='PK_ALEX_TAB';SEGMENT_NAME                        BYTES------------------------------ ----------PK_ALEX_TAB                        196608SQL> select pct_free from user_indexes where index_name='PK_ALEX_TAB';  PCT_FREE----------        104. DUMP出主键索引的索引树结构SQL> select data_object_id,object_id from dba_objects where owner='SCOTT' and object_name='PK_SPLIT_TAB';DATA_OBJECT_IDOBJECT_ID-------------- ---------- 75152    75152SQL> alter session set events 'immediate trace name treedump level 75152';会话已更改。5. DUMP日志分析----- begin tree dumpbranch: 0x10206c3 16910019 (0: nrow: 18, level: 1)   leaf: 0x10206c5 16910021 (-1: nrow: 578 rrow: 578)   leaf: 0x10206c6 16910022 (0: nrow: 571 rrow: 571)   leaf: 0x10206c7 16910023 (1: nrow: 571 rrow: 571)   leaf: 0x10206c4 16910020 (2: nrow: 571 rrow: 571)   leaf: 0x10206cd 16910029 (3: nrow: 571 rrow: 571)   leaf: 0x10206ce 16910030 (4: nrow: 571 rrow: 571)   leaf: 0x10206cf 16910031 (5: nrow: 571 rrow: 571)   leaf: 0x10206c8 16910024 (6: nrow: 571 rrow: 571)   leaf: 0x10206c9 16910025 (7: nrow: 571 rrow: 571)   leaf: 0x10206ca 16910026 (8: nrow: 571 rrow: 571)   leaf: 0x10206cb 16910027 (9: nrow: 571 rrow: 571)   leaf: 0x10206cc 16910028 (10: nrow: 571 rrow: 571)   leaf: 0x10206e1 16910049 (11: nrow: 571 rrow: 571)   leaf: 0x10206e2 16910050 (12: nrow: 571 rrow: 571)   leaf: 0x10206e5 16910053 (13: nrow: 571 rrow: 571)   leaf: 0x10206e6 16910054 (14: nrow: 571 rrow: 571)   leaf: 0x10206e7 16910055 (15: nrow: 571 rrow: 571)   leaf: 0x10206e3 16910051 (16: nrow: 286 rrow: 286)----- end tree dump6. 小结（1）可以看到主键索引只在以下叶块上进行分裂，即最右侧的叶块上进行分裂： leaf: 0x10206e3 16910051 (16: nrow: 286 rrow: 286)
0 0