Oracle中的index學習 ---理解索引為何物，為什麼會使查詢變快____Oracle

索引index一般分為兩種:
1)b-tree(balance-tree),一般用於oltp(online transaction processing)
2)bitmap，一般用於olap(online analysis processing)

下面用執行個體來學習 index 的結構：
1. 先建立一個測試表，在測試表上建立索引
SQL> create table test_index as select * from dba_objects;
Table created.
SQL> create index t_idx on test_index(object_id);
Index created.

2. 獲得索引的data_object_id
SQL> select data_object_id from dba_objects where object_name='T_IDX';
DATA_OBJECT_ID
--------------
         52517

3. treedump出索引階層
SQL> alter session set events 'immediate trace name treedump level 52517';
Session altered.

4. 找到 udump 的目錄：
SQL> show parameter user_dump_dest
NAME               TYPE         VALUE
----------------  ---------    -----------
user_dump_dest      string     /u01/app/oracle/admim/jiagulun/udump

5. 找到 dump 檔案的名稱：
SQL> select distinct sid from v$mystat;
       SID
----------
       137
SQL> select paddr from v$session where sid=137;
PADDR
--------
2CE1F110
SQL> select spid from v$process where addr='2CE1F110';
SPID
------------
31964

6. 查看 dump 檔案的內容：
[root@redhat4 udump]# vi jiagulun_ora_31964.trc
[root@redhat4 udump]# cat jiagulun_ora_ 31964.trc
/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_31964.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      redhat4
Release:        2.6.9-89.ELsmp
Version:        #1 SMP Mon Apr 20 10:34:33 EDT 2009
Machine:        i686
Instance name: jiagulun
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 31964, image: oracle@redhat4 (TNS V1-V3)

*** 2012-09-11 21:08:39.075
*** SERVICE NAME:(SYS$USERS) 2012-09-11 21:08:39.073
*** SESSION ID:(137.8) 2012-09-11 21:08:39.073
----- begin tree dump
branch: 0x40ef5a 4255578 (0: nrow: 111, level: 1)
   leaf: 0x40ef5b 4255579 (-1: nrow: 485 rrow: 485)
   leaf: 0x40ef5c 4255580 (0: nrow: 479 rrow: 479)
   leaf: 0x40ef5d 4255581 (1: nrow: 478 rrow: 478)
   leaf: 0x40ef5e 4255582 (2: nrow: 479 rrow: 479)
   leaf: 0x40ef5f 4255583 (3: nrow: 479 rrow: 479)

   ......

   leaf: 0x40f244 4256324 (104: nrow: 449 rrow: 449)
   leaf: 0x40f245 4256325 (105: nrow: 449 rrow: 449)
   leaf: 0x40f246 4256326 (106: nrow: 449 rrow: 449)
   leaf: 0x40f247 4256327 (107: nrow: 449 rrow: 449)
   leaf: 0x40f248 4256328 (108: nrow: 449 rrow: 449)
   leaf: 0x40f249 4256329 (109: nrow: 328 rrow: 328)
----- end tree dump

注釋：
leaf: 0x40ef5b 4255579 (-1: nrow: 485 rrow: 485)
leaf: 表示該資料區塊是葉子節點（leaf block）
0x40ef5b: 對應索引資料區塊的十六進位地址，編碼了檔案編號和塊編號資訊；
4255579：對應索引資料區塊的十進位地址，編碼了檔案編號和塊編號資訊；

我們可以驗證一下0x40ef5b是否等於4255579：
SQL> select to_number('40ef5b', 'xxxxxx') from dual;
TO_NUMBER('40EF5B','XXXXXX')
----------------------------
                     4255579

-1: 表示索引資料區塊的編號，編號起始是-1
nrow: 485 ：表示該索引資料區塊中總的行數，包含被刪除的行
rrow: 485：表示該索引資料區塊中實際存在有效行數

我們可以利用Oracle提供的預存程序計算出地址4255579的含義：
SQL> select dbms_utility.data_block_address_file(4255579) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4255579)
---------------------------------------------
                                            1
SQL> select dbms_utility.data_block_address_block(4255579) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4255579)
----------------------------------------------
                                         61275
這個結果表明該索引的第一個葉子節點位於 file_id 為 1 的第 61275 個block中。
我們驗證一下索引 t_idx 是否有一個這樣的 block：
SQL> select file_id,block_id,blocks from dba_extents where segment_name='T_IDX';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
        1     61273        8   <<====== 61275 包含在該extents中。
         1      61281          8
         1      61289          8
         1      61297          8
         1      61305          8
         1      61313          8
         1      61961          8
         1      61969          8
         1      61977          8
         1      61985          8
         1      61993          8
         1      62001          8
         1      62009          8
         1      62017          8
         1      62025          8

15 rows selected.

既然我們知道了：該索引的第一個葉子節點位於 file_id 為 1 的第 61275 個block中，那麼我就將這個block 給 dump 出來，研究一下它的內容：
SQL> alter system dump datafile 1 block 61275;
System altered.

然後用vi開啟剛才dump出來的檔案，其中有下面的內容：
......

Leaf block dump
===============
header address 214086748=0xcc2b45c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1830=0x726
kdxcoavs 824
kdxlespl 0
kdxlende 0
kdxlenxt 4255580=0x40ef5c
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  00 40 ed da 00 2d
row#1[8008] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  00 40 ed da 00 05
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  00 40 ed da 00 2e

......

我們來研究一下索引中的第一行 row#0[8020]，顯然索引的第一行儲存的應該是關於表的第一行資料的資訊。第一有兩列：
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  00 40 ed da 00 2d
那索引 t_idx 這兩列的含義是什麼呢。

因為索引是建立在表test_index的object_id列上的，那麼我們大膽的猜測一下：第一列表示的是表test_index的列object_id的值，而第二列表示的是表test_index的第一行的rowid的值。下面我們驗證一下我們的猜測：
SQL> select object_id,rowid from test_index where object_id=1;
no rows selected
SQL> select object_id,rowid from test_index where object_id=2;
 OBJECT_ID ROWID
---------- ------------------
         2 AAAM0kAABAAAO3aAAt
我們看一下object_id=2，這個 2 在Oracle是如何表示的：
SQL> select dump(2,16) from dual;
DUMP(2,16)
-----------------
Typ=2 Len=2: c1,3

顯然這個結果證明了索引 t_idx 的第一行的第一列表示的表 test_index 的第一行的object_id的值!!!
我們再來看第二列 00 40 ed da 00 2d 和 rowid AAAM0kAABAAAO3aAAt 的關係,我們先將十六進位轉換成二進位：
00         40         ed      da      00       2d     <<==16進位
00000000 01000000 11101101 11011010 00000000 00101101 <<==二進位

rowid的值：
AAAM0k   AAB    AAAO3a     AAt
  。      1     60890     45

oracle用左邊的10位來表示相對檔案id（為什麼是10位呢。因為每一個tablespace最多隻能有1024個資料檔案，而2的10次方等於1024）:
00000000 01 = 1
而 AAB = 1
oracle用接下來的22位表示block編號：
000000 11101101 11011010 = 0xedda = select to_number('edda','xxxx') from dual = 60890
而 AAAO3a = 14*64*64 + 55*64 + 26 = select 14*64*64 + 55*64 + 26 from dual = 60890
oracle用最後的8為00101101來表示行編號：
00101101 = 0x2d = 45;
而 AAt = 45

到這裡我們完成明白了一點： 索引的第二列表示的就是rowid的後面三個部分的資訊，即相對檔案id，塊id，行編號。

總結一下：
1）索引的第一列表示的是建立索引的那個列的值；
2）索引的第二列表示的是rowid的後面三個部分的資訊。

這樣我們就明白了為什麼索引可以快速的索引到資料行了。
索引是物理上存在的，而rowid是邏輯上存在的，物理上不存在的，而建立索引就是將邏輯上的rowid物化。