本文章为网络笔记，看了warehouse老师的视频受益匪浅，更是感觉自己技术太过初级，特写了本笔记，方便以后反复学习！
如有任何不妥，请发邮件至[email protected]删除文章！
关于warehouse：
http://blog.itpub.net/19602/viewspace-1059211/
11gR2视频第四版 8_03_index的原理
1.index
bitmap index
（位图索引）
b-tree index（balance（平衡） tree）
不是二叉树，特点是根节点（
root
）很少，叶子节点（
leaf
）很多，所以通俗的讲这颗树不高所以查找的很快，数据最终都是放在叶子节点上
root
下不止有一个
branches
，可以有多个，
branches
下面还有
branches
那时候的数据很大了，差不多就上亿了
1.为啥要建索引？
2.索引为什么会提高查询效率？
3.是不是有了索引之后就一定会提高查询效率？
索引可以提高查询效率，但是它本身也是一个
segment
，需要大量的存储空间，做
DML
操作的时候索引也需要维护
实验：
SQL> create table tt2 tablespace users as select * from dba_objects;
Table created.
SQL> insert into tt2 select * from tt2;
15358 rows created.
SQL> insert into tt2 select * from tt2;
30716 rows created.
SQL> insert into tt2 select * from tt2;
61432 rows created.
SQL> insert into tt2 select * from tt2;
122864 rows created.
SQL> select count(1) from tt2;
COUNT(1)
----------
245728
建一个索引，索引是一个
segment
，所以他也有
metadata id
和
data id
SQL> create index idx_tt2 on tt2(object_id) tablespace users;
Index created.
查看一下这个
index
的
metadata id
和
data id
，
Oracle
有一条内部命令可以分析
b-tree
索引的结构，这个命令就用到了
object_id
（元数据
id
）
SQL> select object_id,data_object_id from dba_objects where object_name = 'IDX_TT2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
15762 15762
这个内容就放在了为这个
session
服务的
process
的
trace
文件里
SQL> alter session set events 'immediate trace name treedump level 15762';
Session altered.
当前
session sid
SQL> select distinct sid from v$mystat;
SID
----------
136
找出为这个
session
服务的
process
的操作系统的进程号
SELECT *
FROM v$process
WHERE ADDR = (SELECT paddr FROM v$session WHERE sid = '136')
后台进程
dump
下来的文件就是在
background_dump_dest
里
SQL> show parameter dump;
NAME TYPE VALUE
-------------------------- ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/diag/rdbms/buy/sales/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/buy/sales/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/diag/rdbms/buy/sales/trace
就是这个
sales_ora_6598.trc
[[email protected] /u01/app/oracle/diag/rdbms/buy/sales/trace]$ ls -lrt
-rw-r----- 1 oracle oinstall 865 Mar 17 10:40 sales_mman_5417.trc
-rw-r----- 1 oracle oinstall 60 Mar 17 10:40 sales_ora_5451.trm
-rw-r----- 1 oracle oinstall 993 Mar 17 10:40 sales_ora_5451.trc
-rw-r----- 1 oracle oinstall 69 Mar 17 10:40 sales_j000_5473.trm
-rw-r----- 1 oracle oinstall 981 Mar 17 10:40 sales_j000_5473.trc
-rw-r----- 1 oracle oinstall 80 Mar 17 10:41 sales_dbrm_5413.trm
-rw-r----- 1 oracle oinstall 1282 Mar 17 10:41 sales_dbrm_5413.trc
-rw-r----- 1 oracle oinstall 175003 Mar 17 12:30 alert_sales.log
-rw-r----- 1 oracle oinstall 71 Mar 17 14:23 sales_ora_6598.trm
-rw-r----- 1 oracle oinstall 29781 Mar 17 14:23 sales_ora_6598.trc
-rw-r----- 1 oracle oinstall 625 Mar 17 14:37 sales_mmon_5430.trm
-rw-r----- 1 oracle oinstall 6666 Mar 17 14:37 sales_mmon_5430.trc
trace
文件的一部分
Trace file /u01/app/oracle/diag/rdbms/buy/sales/trace/sales_ora_6598.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: oracle
Release: 3.10.0-862.el7.x86_64
Version: #1 SMP Fri Apr 20 16:44:24 UTC 2018
Machine: x86_64
VM name: VMWare Version: 6
Instance name: sales
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 6598, image: [email protected] (TNS V1-V3)
*** 2021-03-17 14:23:36.913
*** SESSION ID:(136.23) 2021-03-17 14:23:36.913
*** CLIENT ID:() 2021-03-17 14:23:36.913
*** SERVICE NAME:(SYS$USERS) 2021-03-17 14:23:36.913
*** MODULE NAME:([email protected] (TNS V1-V3)) 2021-03-17 14:23:36.913
*** ACTION NAME:() 2021-03-17 14:23:36.913
##下面这个就是已经生成index了，这个branch可以看成是一个特殊的root，因为数据量比较少所以branch下面就是leaf
----- begin tree dump
branch: 0x100010b 16777483 (0: nrow: 526, level: 1)
leaf: 0x100010c 16777484 (-1: nrow: 512 rrow: 512)
leaf: 0x100010d 16777485 (0: nrow: 512 rrow: 512)
leaf: 0x100010e 16777486 (1: nrow: 512 rrow: 512)
leaf: 0x100010f 16777487 (2: nrow: 481 rrow: 481)
再插入一些数据，索引对应的
segment
也是会增大的
SQL> insert into tt2 select * from tt2;
245728 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from tt2;
COUNT(1)
----------
491456
SQL> alter session set events 'immediate trace name treedump level 15762';
Session altered.
重新打开这个文件
sales_ora_6598.trc
让他加载一下，注意这个
begin tree dump
上面的时间是不是对应执行
alter session set events
的时间，因为这个文件是累加的防止上面的文件干扰，还是只截取一部分
*** 2021-03-17 14:49:45.710
----- begin tree dump
branch: 0x100010b 16777483 (0: nrow: 2, level: 2) ##这个branch其实就是可以看成是root
branch: 0x1001073 16781427 (-1: nrow: 482, level: 1) ##这里有一个branch（第一个）
leaf: 0x100010c 16777484 (-1: nrow: 512 rrow: 512)
leaf: 0x1001064 16781412 (0: nrow: 512 rrow: 512)
leaf: 0x100010d 16777485 (1: nrow: 512 rrow: 512)
leaf: 0x1001068 16781416 (2: nrow: 512 rrow: 512)
leaf: 0x100010e 16777486 (3: nrow: 512 rrow: 512)
leaf: 0x1001045 16781381 (4: nrow: 512 rrow: 512)
leaf: 0x100010f 16777487 (5: nrow: 480 rrow: 480)
leaf: 0x1001055 16781397 (6: nrow: 481 rrow: 481)
leaf: 0x1000110 16777488 (7: nrow: 479 rrow: 479)
leaf: 0x1001059 16781401 (8: nrow: 463 rrow: 463)
leaf: 0x1000111 16777489 (9: nrow: 482 rrow: 482)
一共是有两个
branch
，然后就是
leaf
了，所以
balance tree
的特点就是扁平的
leaf: 0x1000efd 16781053 (479: nrow: 478 rrow: 478)
leaf: 0x1001303 16782083 (480: nrow: 480 rrow: 480)
branch: 0x1001077 16781431 (0: nrow: 570, level: 1) ##这里也是一个branch（第二个）
leaf: 0x1000efe 16781054 (-1: nrow: 480 rrow: 480)
leaf: 0x1001307 16782087 (0: nrow: 462 rrow: 462)
leaf: 0x1000eff 16781055 (1: nrow: 482 rrow: 482)
leaf: 0x1001317 16782103 (2: nrow: 477 rrow: 477)
leaf: 0x1000f02 16781058 (3: nrow: 499 rrow: 499)
leaf: 0x100131b 16782107 (4: nrow: 459 rrow: 459)
leaf: 0x1000f03 16781059 (5: nrow: 481 rrow: 481)
leaf: 0x1001250 16781904 (6: nrow: 477 rrow: 477)
下面分析叶子节点（
leaf
）
使用
Oracle
函数转化一下
16
进制数验证一下
SQL> select to_number('100010c','xxxxxxx') from dual;
TO_NUMBER('100010C','XXXXXXX')
------------------------------
16777484
知道了十进制数之后
Oracle
提供了包，包中有函数可以分解成是哪个数据文件上的哪个块
SQL> select dbms_utility.data_block_address_file(16777484) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777484)
----------------------------------------------
4
SQL> select dbms_utility.data_block_address_block(16777484) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777484)
-----------------------------------------------
268
知道这个
index
中的
leaf
是在哪个块了就把这个块
dump
下来
SQL> alter system dump datafile 4 block 268;
System altered.
SQL> select distinct sid from v$mystat;
SID
----------
137
SQL> select spid from v$process where addr = (select paddr from v$session where sid = '137');
SPID
------------------------
4691
SQL> show parameter dump;
NAME TYPE VALUE
------------------------ ------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/diag/rdbms/buy/sales/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/buy/sales/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/diag/rdbms/buy/sales/trace
[[email protected] ~]$ cd /u01/app/oracle/diag/rdbms/buy/sales/trace
[[email protected] /u01/app/oracle/diag/rdbms/buy/sales/trace]$ ls -lrt
-rw-r----- 1 oracle oinstall 92610 Apr 20 11:33 sales_ora_4691.trc
简单介绍
dump
块中的内容
从这里开始
dump
块
Start dump data blocks tsn: 4 file#:4 minblk 268 maxblk 268
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777484
Block dump from disk:
buffer tsn: 4 rdba: 0x0100010c (4/268)
scn: 0x0000.0005f8e7 seq: 0x01 flg: 0x06 tail: 0xf8e70601
frmt: 0x02 chkval: 0x85fa type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F2B7FE15A00 to 0x00007F2B7FE17A00
下面都是
16
进制数，看不懂也不需要看他
Dump of memory from 0x00007F2B7FE15A00 to 0x00007F2B7FE17A00
7F2B7FE15A00 0000A206 0100010C 0005F8E7 06010000 [................]
7F2B7FE15A10 000085FA 00000002 00003D92 0005E057 [.........=..W...]
7F2B7FE15A20 00000000 00320002 01000108 00130002 [......2.........]
7F2B7FE15A30 0000016D 00C00873 000100CD 00006001 [m...s........`..]
7F2B7FE15A40 0005F629 001E0003 0000017A 00C00A6C [).......z...l...]
7F2B7FE15A50 001B006B 00002100 0005F8E7 00000000 [k....!..........]
7F2B7FE15A60 00000000 02800100 00000001 04240200 [..............$.]
7F2B7FE15A70 033C0760 00000000 01001064 00000000 [`.<.....d.......]
7F2B7FE15A80 00000000 00001F60 120411F8 121C1210 [....`...........]
7F2B7FE15A90 12341228 124C1240 12641258 127C1270 [([email protected].|.]
从这里开始就是
index segment
真正记录的东西，注意看
index segment
也是二维结构，两个字段（col1、col2）也就是
object_id
和
rowid
header address 139824805796452=0x7f2b7fe15a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 512
kdxcofbo 1060=0x424
kdxcofeo 1888=0x760
kdxcoavs 828
kdxlespl 0
kdxlende 0
kdxlenxt 16781412=0x1001064
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4600] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03 --第一行
col 1; len 6; (6): 01 00 00 8b 00 30
row#1[4612] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 01 e9 00 30
row#2[4624] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 02 ed 00 01
row#3[4636] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 03 d3 00 1c
我们创建
index
的时候是建在了
object_id
上，所以就会按照
object_id
进行排序，但是
object_id
是有重复的，按照
object_id
排序之后就按
rowid
进行排序了，如果
object_id
是唯一的就不用
rowid
我们来验证
col1
记录的是什么
col 0; len 2; (2): c1 03 --第一行
下面是表中的数据
以
object_id
排序了，第一行是
2
，看一下
2
的
16
进制数是多少
SQL> select dump(2,16) from dual;
DUMP(2,16)
-----------------
Typ=2 Len=2: c1,3
--Typ2表示number类型，Len2表示占用两个字节，c1,3中间的0省略了，也就是说c1,3就是0
也可以把
c1 03
转成
number
类型
SQL> select utl_raw.cast_to_number('c103') from dual;
UTL_RAW.CAST_TO_NUMBER('C103')
------------------------------
2
tt2
表中一共有
32
条
object_id
为
2
的，看
dump
下来的文件正好是
32
行
c1 03
SQL> select count(1) from tt2 where object_id = 2;
COUNT(1)
----------
32
row#0[4600] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 00 8b 00 30
--中间省略了....
row#31[1960] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 1e ce 00 19
上面的实验就是说明了你在哪个字段上创建了索引，他就把这个字段的值保存到了
B
树索引中的
leaf
节点上了，而且这个
B
树的叶子节点中的值是排好序的，最小的在最左边，最大的在最右边
下面看
col 1
存放的是什么，他是
16
进制数
col 1; len 6; (6): 01 00 00 8b 00 30
把第一行的
rowid
拿出来
AAAD2RAAEAAAACLAAw
，这是
64
进制数
要先把十六进制数转换成二进制数（不足八位的前边补
）
01 00 00 8b 00 30
转换成
00000001 00000000 00000000 10001011 00000000 00110000
rowid：AAAD2RAAEAAAACLAAw
col1
二进制数的前十位就是表示的相对文件编号对应
rowid
的
7-9
位（
AAE
）
再把前十位转换成十进制数
0000000100
的十进制数是
4
col1
二进制数的
11-32
位表示的是块编号对应
rowid
的第
10-15
位（
AAAACL
）
11-32
位
000000 00000000 10001011
的十进制数是
139
再看
rowid
的结果，查出来的块编号也是
139
SQL> select dbms_rowid.rowid_block_number('AAAD2RAAEAAAACLAAw') from dual;
DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAD2RAAEAAAACLAAW')
---------------------------------------------------
139
col1
二进制数的
33-
最后表示的就是数据在这个数据块的哪一行
00000000 00110000
十进制数是
48
看
rowid
的计算结果，同样也是
48
SQL> select dbms_rowid.rowid_row_number('AAAD2RAAEAAAACLAAw') from dual;
DBMS_ROWID.ROWID_ROW_NUMBER('AAAD2RAAEAAAACLAAW')
-------------------------------------------------
48
综上所述，我们
dump
下来的
index
的
leaf
中
col1
记录的就是
rowid
的后三部分（
rowid
一共有四部分组成），数据文件位置、在哪个块上、在数据块的哪一行上
rowid
的第一个部分是数据段
id
，这个没有保存在
leaf
中因为不需要为了节省空间，在元数据中能够找到这个
index
的关联信息所以没有必要在
leaf
中保存
leaf
中的
col1
就相当于是书的目录，
col1
就是具体的页码，
leaf
存放在哪个字段上建索引那个索引的值和这个值所在行的
rowid
，这个就是索引的原理