本文章為網絡筆記，看了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
，這個就是索引的原理