oracle index 索引总结

1、之前的总结

关于索引的概念：http://blog.csdn.net/changyanmanman/article/details/7097318

关于索引组织表：http://blog.csdn.net/changyanmanman/article/details/7326505

索引段使用技巧：http://blog.csdn.net/changyanmanman/article/details/7270361

索引跳跃式扫描：http://blog.csdn.net/changyanmanman/article/details/7295443

本地和全局索引：http://blog.csdn.net/changyanmanman/article/details/7299515

三索引性能比较：http://blog.csdn.net/changyanmanman/article/details/7295534

2、分析索引结构

创建环境：

就用我们经典的t2表吧，

SQL> desc t2
 名称                                           是否为空?      类型
 ----------------------------------------- --------   ------------------
 OBJECT_ID                                               NUMBER
 OBJECT_NAME                                        VARCHAR2(128)

SQL> select count(*) from t2;
  COUNT(*)
----------
     48940

在t2表的object_id 列创建索引：

SQL> create index t2_idx on t2(object_id);
索引已创建。

找出这个索引对应的id号：

SQL> select object_id from dba_objectS where object_name='T2_IDX';
 OBJECT_ID
----------
     59676

根据这个id，把索引的结构dump出来：

SQL> alter session set events 'immediate trace name treedump level 59676';
会话已更改。

现在这个文件到哪了呢？我们这个会话是一个用户进程，（反正至少不是后台进程）所以dump出来的文件会在 udump 目录下。怎么找到对应的那个dunp出来的文件呢？ 可能你会说，根据时间排列一下，最接近现在的那个就是。。。。额 。。。这样说算是有道理的，但是，问题是：如果有很多个用户进程都连接了，都进行了各种dump操作。。。这下这么多文件，时间几乎是同样的，怎么办？？

所以说，我们需要一个普遍，通用而准确的方法：
我们观察了一下这些文件的命名方式，大概都是这个样子的：orcl_ora_3488.trc  我们只看那个数字，这个数字是为这个用户进程服务的操作系统进程的ID（或线程id）。

ok，第一步，我们找到这个用户会话进程的id：

SQL> select distinct sid from v$mystat;

       SID
----------
       149

第二步，根据当前session的id （即 sid） 找出为这个sid 服务的进程的地址：

SQL> select paddr from v$session where sid = 149;

   PADDR
     --------
 6C8F0744

第三步，根据父进程的地址，找到父进程的id号，也就找到了跟踪文件的名字：

SQL> select spid from v$process where addr='6C8F0744';
SPID
------------
5508

好了，找到对应的文件：orcl_ora_5508.trc

打开文件：

Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 14
Windows thread id: 5508, image: ORACLE.EXE (SHAD)
*** 2012-10-08 13:05:00.968
*** SERVICE NAME:(SYS$USERS) 2012-10-08 13:05:00.843
*** SESSION ID:(149.18) 2012-10-08 13:05:00.843
----- begin tree dump
branch: 0x40ec52 4254802 (0: nrow: 108, level: 1)    ——这一行其实就是根节点的开始。
   leaf: 0x40ec53 4254803 (-1: nrow: 485 rrow: 485)   ——叶子节点的开始，如果索引多的话，还会向下分一个等级，隔一段出现一个branch。
   leaf: 0x40ec54 4254804 (0: nrow: 479 rrow: 479)
   leaf: 0x40ec55 4254805 (1: nrow: 479 rrow: 479)
   leaf: 0x40ec56 4254806 (2: nrow: 479 rrow: 479)
   leaf: 0x40ec57 4254807 (3: nrow: 479 rrow: 479)
   leaf: 0x40ec58 4254808 (4: nrow: 479 rrow: 479)
   leaf: 0x40ec59 4254809 (5: nrow: 479 rrow: 479)
   leaf: 0x40ec5a 4254810 (6: nrow: 479 rrow: 479)
   leaf: 0x40ec5b 4254811 (7: nrow: 479 rrow: 479)
   leaf: 0x40ec5c 4254812 (8: nrow: 479 rrow: 479)
   leaf: 0x40ec5d 4254813 (9: nrow: 479 rrow: 479)
   leaf: 0x40ec5e 4254814 (10: nrow: 479 rrow: 479)
   leaf: 0x40ec5f 4254815 (11: nrow: 479 rrow: 479)
   leaf: 0x40ec60 4254816 (12: nrow: 479 rrow: 479)
   leaf: 0x40ec61 4254817 (13: nrow: 479 rrow: 479)
   leaf: 0x40ec62 4254818 (14: nrow: 479 rrow: 479)
   leaf: 0x40ec63 4254819 (15: nrow: 479 rrow: 479)
   leaf: 0x40ec64 4254820 (16: nrow: 479 rrow: 479)
   leaf: 0x40ec65 4254821 (17: nrow: 479 rrow: 479)
   leaf: 0x40ec66 4254822 (18: nrow: 472 rrow: 472)
   leaf: 0x40ec67 4254823 (19: nrow: 449 rrow: 449)
   leaf: 0x40ec68 4254824 (20: nrow: 449 rrow: 449)
   leaf: 0x40ec69 4254825 (21: nrow: 449 rrow: 449)
   leaf: 0x40ec6a 4254826 (22: nrow: 449 rrow: 449)
   leaf: 0x40ec6b 4254827 (23: nrow: 449 rrow: 449)
   leaf: 0x40ec6c 4254828 (24: nrow: 449 rrow: 449)
   leaf: 0x40ec6d 4254829 (25: nrow: 449 rrow: 449)
   leaf: 0x40ec6e 4254830 (26: nrow: 449 rrow: 449)
   leaf: 0x40ec6f 4254831 (27: nrow: 449 rrow: 449)
   leaf: 0x40ec70 4254832 (28: nrow: 449 rrow: 449)
   leaf: 0x40ec71 4254833 (29: nrow: 449 rrow: 449)
   leaf: 0x40ec72 4254834 (30: nrow: 449 rrow: 449)
   leaf: 0x40ec73 4254835 (31: nrow: 449 rrow: 449)
   leaf: 0x40ec74 4254836 (32: nrow: 449 rrow: 449)
   leaf: 0x40ec75 4254837 (33: nrow: 449 rrow: 449)

。。。。。。。。。。。。。。。

 leaf: 0x40ed3d 4255037 (105: nrow: 449 rrow: 449)
   leaf: 0x40ed3e 4255038 (106: nrow: 297 rrow: 297)
----- end tree dump

恩，这就全部导出来了。。我们拿出一行来分析一下：

就叶子块的第一行吧：leaf: 0x40ec53 4254803 (-1: nrow: 485 rrow: 485)

leaf 不用解释，就是指的叶子节点。下面ox40ec53 是十六进制数，等于后面的十进制数4254803，指示了这个索引块所在的数据文件和数据块号。oracle有专门的函数来转换进制和包来转换块号。

SQL> select to_number('40ec53','xxxxxxx') from dual;
TO_NUMBER('40EC53','XXXXXXX')
-----------------------------
                      4254803

SQL> select dbms_utility.data_block_address_file(4254803) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4254803)
---------------------------------------------
                                            1

SQL> select dbms_utility.data_block_address_block(4254803) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4254803)
----------------------------------------------
                                         60499

这下，可以看到这个索引在1号数据文件的60499块内了。

我们通过dba_extents这个数据字典视图核实一下这个60499块是不是有这个索引。

SQL> select * from dba_extents where segment_name='T2_IDX'

可以看到从block_id 列，起始的60497向后数出8个，算是一个extent，都是段名子为T2_IDX的数据。也就是我们之前查询的也在这个extent里面。

刚才我们dump出来的是索引对象。（dump出的是一个对象，很多块） ，现在我们dump一个块出来，就是60499那个块。看看里面存了什么数据。（如果另起一个session的话，就会重新建一个dump文件，如果还用现在的session ，即 sid=149 ，就还在那个文件里。这个无所谓的）。。

执行：

SQL> alter system dump datafile 1 block 60499;
系统已更改。

进入用户dump目录找到对应的文件，打开：

*** 2012-10-08 14:51:06.375
Start dump data blocks tsn: 0 file#: 1 minblk 60499 maxblk 60499
buffer tsn: 0 rdba: 0x0040ec53 (1/60499)
scn: 0x0000.00b09e2e seq: 0x01 flg: 0x04 tail: 0x9e2e0601
frmt: 0x02 chkval: 0x62a2 type: 0x06=trans data
Block header dump:  0x0040ec53
 Object id on Block? Y
 seg/obj: 0xe91c  csc: 0x00.b09e2b  itc: 2  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00b09e2b
 
Leaf block dump
===============
header address 105906780=0x650025c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485                      ——这个说明本块一共存了485行数据，可以从下面的row# 进行验证。
kdxcofbo 1006=0x3ee
kdxcofeo 1834=0x72a
kdxcoavs 828
kdxlespl 0
kdxlende 0
kdxlenxt 4254804=0x40ec54
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036    ——这个块的大小。
row#0[8024] flag: ------, lock: 0, len=12   ——这类似于一个二维表，每一行，两列，分别是col0，col1，具体意思见下面我的分析。
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  00 40 ea 4a 00 2d
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  00 40 ea 4a 00 05
row#2[8000] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  00 40 ea 4a 00 2e
row#3[7988] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 06
col 1; len 6; (6):  00 40 ea 4a 00 19
row#4[7976] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 07
col 1; len 6; (6):  00 40 ea 4a 00 14
row#5[7964] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 08
col 1; len 6; (6):  00 40 ea 4a 00 10
row#6[7952] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 09
col 1; len 6; (6):  00 40 ea 4a 00 21
row#7[7940] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 0a
col 1; len 6; (6):  00 40 ea 4a 00 0c
row#8[7928] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 0b
col 1; len 6; (6):  00 40 ea 4a 00 22
row#9[7916] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 0c
col 1; len 6; (6):  00 40 ea 4a 00 35
row#10[7904] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 0d
col 1; len 6; (6):  00 40 ea 4a 00 25
row#11[7892] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 0e
col 1; len 6; (6):  00 40 ea 4a 00 0b
row#12[7880] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 0f
col 1; len 6; (6):  00 40 ea 4a 00 13

。。。。。。。。。。。。。。。。。。

row#481[1873] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 06 07
col 1; len 6; (6):  00 40 ea 4b 00 7d
row#482[1860] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 06 08
col 1; len 6; (6):  00 40 ea 4b 00 7e
row#483[1847] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 06 09
col 1; len 6; (6):  00 40 ea 4b 00 7f
row#484[1834] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 06 0a
col 1; len 6; (6):  00 40 ea 4b 00 80
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 60499 maxblk 60499

以上便是导出索引块的结果，下面分析索引块的内容。先看看每一行中两列的意思。拿出两列来分析分析：

row#0[8024] flag: ------, lock: 0, len=12  
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  00 40 ea 4a 00 2d
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  00 40 ea 4a 00 05

先看长度 len 2 的那列：c1 03 这是什么呢？猜测下，是不是我们的索引键object_id 呢？？恩，答案是是的。那我们如何证明呢？

SQL> select object_id,rowid from t2 where object_id=2;
 OBJECT_ID    ROWID
----------     ------------------

   2         AAAOiuAABAAAOpKAAt

我们把这个object_id=2(十进制)转换成十六进制：

SQL> select dump(2,16) from dual;
DUMP(2,16)
-----------------
Typ=2 Len=2: c1,3

这里type=2 表示number类型的2，十进制的2，len=2长度为2，占用两个字节。也就是说2这个十进制数，在数据库块内的存放位置就是c1 03 。

继续看col1 中的数据：00 40 ea 4a 00 2d 长度为6，这个会是神马呢？很明显，几乎确定是rowid的十六进制写法。如何证明出来与  AAAOiuAABAAAOpKAAt 是相同的呢？？

先把十六进制数写作 二进制吧：00 40 ea 4a 00 2d = 00000000 01000000 11101010 01001010 00000000 00101101

这rowid ：AAAOiu  AAB  AAAOpK  AAt 

二进制的前十位代表的是数据文件（数据段）编号00000000 01 = 1 就是1号文件 。 也就是rowid 中的 AAB 很明显 B 也代表1 。 哦了。

二进制的11位后数22位，代表的块编号：000000 11101010 01001010 = 59978  。而AAAOpK 代表的十进制数是：14*64*64+41*64+10=59978 证明是相同的。

二进制最后16位。代表行号：00000000 00101101=45 ；  rowid最后三位AAt 十进制为 45 。搞定了。

也就是说，col1 列存储的是rowid 的后面三部分，为什么没存储第一部分（段所在空间），没必要，因为在创建索引的时候已经做了关联，也就是说，我们没有必要为一本书的每个目录前面加上书的名字。

附上64进制rowid对应的十进制数字表：