这是一个关于oracle数据库索引的专题,准确的说是读书笔记,根据《oracle索引技术》 Darl Kuhn Sam R.Alapati Bill Padfield 著作为基础和引导做的实验和总结的一些内容。针对b-tree基本理论,应该以《数据结构》课程中树、查找等相关部分知识为基础去研究,为了通俗解释,推荐阅读www.cnblogs.com/sujing/p/11110292.html这篇文章,可以帮助大家快速的理解b树的基本原理和实现方法。最后,感谢这些技术文章的原作者,如果大家需要查阅《oracle索引技术》原文,网上可以购买或者到图书馆借阅。
rowid是数据表中的伪列,可以唯一标识一行记录(cluster table除外),并包含其物理位置(数据文件和数据块内行的位置)。对于oracle默认的B树索引,索引叶块转储后可以查询索引列的键值和rowid,后面我们会实验dump过程和rowid转换。
1.rowid(64进制)构成
OOOOOO FFF BBBBBB RRR
data object number relative file number block number row number
SQL> select object_name,rowid from t1227 where rownum=1;
OBJECT_NAME
--------------------------------------------------------------------------------
ROWID
------------------
ICOL$
AAAR7TAABAAAVFxAAA
1.1 data object id
object_id和data_object_id都是数据库对象的唯一标识,区别object_id标识逻辑id而data_object_id标识物理id,所以对于过程、视图等不存在物理段的对象是没有data_object_id的。
新建对象object_id和data_object_id取值分别为max(select max(object_id) from dba_objects)+1,max(select max(hwmincr) from seg$)+1
SQL> select max(object_id) from dba_objects;
MAX(OBJECT_ID)
--------------
73425
建立测试表
SQL> create table t1227 as select * from dba_objects;
Table created.
SQL> select max(object_id) from dba_objects;
MAX(OBJECT_ID)
--------------
73426
对表执行移动、截断操作会影响data_object_id取值变化但object_id不会改变。
SQL> select data_object_id from dba_objects where object_name='T1227';
DATA_OBJECT_ID
--------------
73426
SQL> alter table t1227 move tablespace system;
Table altered.
SQL> select data_object_id from dba_objects where object_name='T1227';
DATA_OBJECT_ID
--------------
73427
1.2 relative file number
相对数据文件编号,和数据文件编号一样用于唯一标识一个数据文件,区别是当数据文件不多于1023个两者相同,超过1023个时,相对文件编号归1进入下一个循环。
SQL> select tablespace_name from dba_tables where table_name='T1227';
TABLESPACE_NAME
------------------------------
SYSTEM
SQL> select file_id,tablespace_name,relative_fno from dba_data_files;
FILE_ID TABLESPACE_NAME RELATIVE_FNO
---------- ------------------------------ ------------
4 USERS 4
3 UNDOTBS1 3
2 SYSAUX 2
1 SYSTEM 1
5 TEST 5
1.3 block number
段、区、块是数据库存储物理结构,块编号查询数据在哪一个块上。
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select block_id,blocks from dba_extents where segment_name='T1227';
BLOCK_ID BLOCKS
---------- ----------
86384 8
86392 8
87296 8
87304 8
87312 8
87320 8
87328 8
87336 8
87344 8
87352 8
87360 8
…
23 rows selected.
1.4 row number
这里的rownum并不是查询排序的伪列row number(row number从1开始,AAA的十进制是0)而是数据在数据块中的行位置。
SQL> select name,value from v$diag_info where name='Default Trace File';
NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
Default Trace File
/app/oracle/diag/rdbms/yx/yx/trace/yx_ora_54594.trc
SQL> alter system dump datafile 1 block 86385;
System altered.
dump出数据块找到行编号列表,查询对应的键值,可以看到block 86385一共有88行数据。
0xe:pti[0] nrow=88 offs=0
0x12:pri[0] offs=0x1f3b
block_row_dump:
tab 0, row 0, @0x1f3b
tl: 77 fb: --H-FL-- lb: 0x0 cc: 14
col 0: [ 3] 53 59 53
col 1: [ 5] 49 43 4f 4c 24
col 2: *NULL*
col 3: [ 2] c1 15
col 4: [ 2] c1 03
col 5: [ 5] 54 41 42 4c 45
col 6: [ 7] 78 6d 08 0f 01 11 34
col 7: [ 7] 78 6d 08 0f 01 1e 1c
col 8: [19] 32 30 30 39 2d 30 38 2d 31 35 3a 30 30 3a 31 36 3a 35 31
col 9: [ 5] 56 41 4c 49 44
col 10: [ 1] 4e
col 11: [ 1] 4e
col 12: [ 1] 4e
col 13: [ 2] c1 02
col对应数据列,以col 1(object_name)为例,ICOL$转换为ascii码后查找对应16进制数。
SQL> select object_name from t1227 where rownum=1;
OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
SQL> select ascii('I') from dual;
ASCII('C')
----------
73
SQL> select to_char(73,'xx') from dual;
TO_
---
49
2.索引叶块rowid区别
SQL> create index t1227_index on t1227(object_name);
Index created.
SQL> select object_id from dba_objects where object_name='T1227_INDEX';
OBJECT_ID
----------
73514
SQL> alter session set events 'immediate trace name treedump level 73514';
Session altered.
----- begin tree dump
branch: 0x415101 4280577 (0: nrow: 2, level: 2)
branch: 0x4152c1 4281025 (-1: nrow: 318, level: 1)
leaf: 0x415102 4280578 (-1: nrow: 184 rrow: 184)
leaf: 0x415103 4280579 (0: nrow: 184 rrow: 184)
leaf: 0x415104 4280580 (1: nrow: 188 rrow: 188)
leaf: 0x415105 4280581 (2: nrow: 190 rrow: 190)
leaf: 0x415106 4280582 (3: nrow: 184 rrow: 184)
leaf: 0x415107 4280583 (4: nrow: 186 rrow: 186)
…
----- end tree dump
SQL> select dbms_utility.data_block_address_file(4280581)fno,dbms_utility.data_block_address_block(4280581)bno from dual;
FNO BNO
---------- ----------
1 86277
dump索引树查找4280581叶块的数据块位置。
SQL> alter system dump datafile 1 block 86277;
System altered.
…
row#0[7992] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 33 61 37 30 39 66 63 5f 4a 76 6d 4d 65 6d 4d 61 6e 61 67 65 72 45 6e
74 72 79 49 6d
col 1; len 6; (6): 00 41 55 eb 00 28
row#1[7952] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 33 61 37 30 39 66 63 5f 4a 76 6d 4d 65 6d 4d 61 6e 61 67 65 72 45 6e
74 72 79 49 6d
col 1; len 6; (6): 00 41 55 eb 00 29
…
SQL> select idx_rowid('00 41 55 eb 00 28') from dual;
File# = 1, Block# = 87531, Row# = 40
索引叶块中的rowid用6个字节 48bit保存,其中1-10bit表示相对文件编号,17-32bit表示块编号,33-48bit表示行编号。和数据表rowid相比缺少data_object_id,前者被称为限制rowid后者被称为扩展rowid。
SQL> alter system dump datafile 1 block 87531;
System altered.
tab 0, row 40, @0xeb3
tl: 107 fb: --H-FL-- lb: 0x0 cc: 14
col 0: [ 3] 53 59 53
col 1: [30]
2f 31 33 61 37 30 39 66 63 5f 4a 76 6d 4d 65 6d 4d 61 6e 61 67 65 72 45 6e
74 72 79 49 6d
col 2: *NULL*
col 3: [ 4] c3 02 50 3b
col 4: *NULL*
col 5: [10] 4a 41 56 41 20 43 4c 41 53 53
col 6: [ 7] 78 6d 08 0f 01 1a 28
col 7: [ 7] 78 6d 08 0f 01 1a 28
col 8: [19] 32 30 30 39 2d 30 38 2d 31 35 3a 30 30 3a 32 35 3a 33 39
col 9: [ 5] 56 41 4c 49 44
col 10: [ 1] 4e
col 11: [ 1] 4e
col 12: [ 1] 4e
col 13: [ 2] c1 02
参考资料:
m.blog.itpub.net/30174570/viewspace-2140058/
https://www.linuxidc.com/Linux/2016-11/137542.htm