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