Oracle中的index学习 ---理解索引为何物，为什么会使查询变快

索引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物化。