从平衡树到oracle b-tree索引的原理探索
从平衡树到oracle b-tree索引的原理探索
先话唠一下,Oracle索引,有两类运用较广:
1)b-tree:OLTP(面向交易)
2)bitmap:OLAP(面向分析)
步入正题,先搭建测试环境:
- SQL> create table tt as select * from dba_objects;
- 表已创建。
- SQL> select count(*) from tt;
- COUNT(*)
- ----------
- 50441
- SQL> insert into tt select * from tt;
- 已创建50441行。
- SQL> /
- 已创建100882行。
- SQL> /
- 已创建201764行。
- SQL> /
- 已创建403528行。
- SQL> /
- 已创建807056行。
- SQL> create index tt_index on tt(object_id) tablespace users;
- 索引已创建。
把索引tt_index的结构给dump出来:
- SQL> select object_id from dba_objects where object_name='TT_INDEX';
- OBJECT_ID
- ----------
- 53042
- SQL> alter session set events 'immediate trace name treedump level 53042';
- 会话已更改。
- SQL> show parameter user_dump_dest
- NAME TYPE
- ------------------------------------ ----------------------
- VALUE
- ------------------------------
- user_dump_dest string
- G:\ORACLE\PRODUCT\10.2.0\ADMIN
- \ORCL\UDUMP
- SQL> select distinct sid from v$mystat;
- SID
- ----------
- 147
- SQL> select paddr from v$session where sid=147;
- PADDR
- --------
- CA280DDC
- SQL> select spid from v$process where addr='CA280DDC';
- SPID
- ------------------------
- 5360
到udump,把进程号为5360的文件打开,部分内容如下:
- *** 2012-08-07 01:21:34.944
- *** ACTION NAME:() 2012-08-07 01:21:34.902
- *** MODULE NAME:(SQL*Plus) 2012-08-07 01:21:34.902
- *** SERVICE NAME:(SYS$USERS) 2012-08-07 01:21:34.902
- *** SESSION ID:(147.92) 2012-08-07 01:21:34.902
- ----- begin tree dump
- branch: 0x10001bc 16777660 (0: nrow: 7, level: 2)
- branch: 0x100595f 16800095 (-1: nrow: 578, level: 1)
- leaf: 0x10001bd 16777661 (-1: nrow: 513 rrow: 513)
- leaf: 0x10001be 16777662 (0: nrow: 513 rrow: 513)
- leaf: 0x10001bf 16777663 (1: nrow: 513 rrow: 513)
- leaf: 0x10001c0 16777664 (2: nrow: 513 rrow: 513)
- leaf: 0x10001c1 16777665 (3: nrow: 513 rrow: 513)
- leaf: 0x10001c2 16777666 (4: nrow: 513 rrow: 513)
- leaf: 0x10001c3 16777667 (5: nrow: 484 rrow: 484)
- leaf: 0x10001c4 16777668 (6: nrow: 478 rrow: 478)
- leaf: 0x10001c5 16777669 (7: nrow: 478 rrow: 478)
- leaf: 0x10001c6 16777670 (8: nrow: 478 rrow: 478)
- leaf: 0x10001c7 16777671 (9: nrow: 478 rrow: 478)
- leaf: 0x10001c8 16777672 (10: nrow: 478 rrow: 478)
- leaf: 0x10001ca 16777674 (11: nrow: 481 rrow: 481)
- leaf: 0x10001cb 16777675 (12: nrow: 478 rrow: 478)
- leaf: 0x10001cc 16777676 (13: nrow: 478 rrow: 478)
- leaf: 0x10001cd 16777677 (14: nrow: 478 rrow: 478)
- leaf: 0x10001ce 16777678 (15: nrow: 478 rrow: 478)
- leaf: 0x10001cf 16777679 (16: nrow: 478 rrow: 478)
|
评论暂时关闭