索引组织表中逻辑ROWID的物理猜

最新推荐文章于 2021-07-18 10:29:39 发布

闭眼忘世界

最新推荐文章于 2021-07-18 10:29:39 发布

阅读量1k

点赞数

分类专栏： Oracle Basic Knowledge 文章标签： oracle 逻辑猜索引组织表

Oracle Basic Knowledge 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

第一次看CONCEPT的时候，一直不是很明白，Oracle索引组织表中逻辑ROWID的物理猜是如何实现的，而这次看的时候很自然的就想明白其中的实现。

Oracle的普通表即堆表，存储数据时没有顺序可言，而Oracle的索引组织表是根据主键顺序来存储表中的数据的。
如果表中的数据都是通过主键来访问，那么没有任何，可是很多时候，需要在表上建立除主键之外的索引，而这就导致了一个问题。
对于普通表而言，Oracle保证数据插入到表中之后，数据的物理地址ROWID不会再发生改变。当然对表进行MOVE，或者ENABLE ROW MOVEMENT之后对分区表的分区键值进行修改等明确导致表数据位置发生变化的操作除外。也就是说，普通的增、删、改不会导致现有记录的物理地址发生变化。即使记录的长度发生了变化，导致当前数据块中无法容纳这条记录，Oracle也会在原位置上留下一个ROWID信息，通过这个ROWID信息可以找到这条记录的新的位置。这也就是行迁移、行链接的实现方式。虽然增加了额外的IO，但是确保了ROWID不发生变化。
索引正是利用了这种特性。索引中包括被索引的字段和这条记录的ROWID，Oracle在索引中找到对应的键值后，根据这个键值对应的ROWID，就可以找到表中的这条对应的记录。正是ROWID不发生变化的特性使得索引可以正常的工作。可以看到，前面提到的MOVE，以及一些导致ROWID发生变化的分区操作，在使得ROWID变化的同时，也会导致索引处于不可用状态。
那么现在存在一个问题，对于索引组织表而言，为了保证数据存储是根据主键顺序进行的，就必须根据数据的增、删、改随时调整表中数据的位置，这使得ROWID不发生改变这个前提无法实现。而对于索引组织表，第二个索引需要一个方法来找到表中数据的具体位置，因此也就有了逻辑ROWID。
对于索引组织表，虽然存储位置可能会经常发生变化，但是主键是必须存在的。如果不能通过物理位置来寻找，那么通过主键来查找也可以找到这条记录。不过Oracle的实现并不是这么简单。
逻辑ROWID除了包含表的主键信息外，还包括了这条记录在索引创建时的物理地址信息。关于逻辑ROWID相信结构描述，可以参考：http://yangtingkun.itpub.net/post/468/11363。
而这个地址信息，就是用来实现物理猜的。如果物理猜能够在目标数据块中找到这条记录，那么这个效率和物理ROWID的效率是一样的，只需要一次IO就找到了目标。如果通过物理猜找不到对应的记录，那么Oracle只能通过逻辑ROWID中包含的主键信息，通过主键扫描来定位这条记录，根据索引的层高，这个操作可能会多消耗几次IO操作。
学习Oracle其实就是一个不断积累的过程，如果基础打的比较扎实，再加上足够的积累，那么对数据库的知识和概念的理解都是水到渠成的。

第一次看CONCEPT的时候，一直不是很明白，Oracle索引组织表中逻辑ROWID的物理猜是如何实现的，而这次看的时候很自然的就想明白其中的实现。
索引组织表中逻辑ROWID的物理猜：http://yangtingkun.itpub.net/post/468/477286
通过一个具体的例子简单描述一下物理猜的过程。

可能有人对这个概念不是很理解，通过一个小例子，说明一下索引组织表如何根据第二索引来访问数据的。
SQL> CREATE TABLE T_INDEX_ORG
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 TYPE VARCHAR2(18))
5 ORGANIZATION INDEX;
表已创建。
SQL> CREATE INDEX IND_INDEX_ORG_NAME ON T_INDEX_ORG (NAME);
索引已创建。
SQL> INSERT INTO T_INDEX_ORG VALUES (0, 'FIRST', 'TEST');
已创建1行。
SQL> INSERT INTO T_INDEX_ORG
2 SELECT ROWNUM, OBJECT_NAME, OBJECT_TYPE
3 FROM DBA_OBJECTS
4 WHERE ROWNUM < 1000;
已创建999行。
SQL> COMMIT;
提交完成。
建立了一个索引组织表，并插入一些记录。每条记录的插入的同时会将当时的物理位置存储在逻辑ROWID中，作为物理猜的基础。对于第一条插入的语句而言，随后的插入会导致索引组织表不断进行调整，因此在后一个插入完成时，第一次插入的记录已经不在开始的位置，这时物理猜就不会准确，而会导致额外的主键扫描。而第二次插入的记录，逻辑ROWID记录的位置就是这条记录的真实位置，因此不会导致物理猜，下面看看二者访问时逻辑读是否有差异：
SQL> SELECT * FROM T_INDEX_ORG WHERE ID = 0;
        ID NAME                           TYPE
---------- ------------------------------ ------------------
         0 FIRST                          TEST
SQL> SELECT * FROM T_INDEX_ORG WHERE ID = 999;
        ID NAME                           TYPE
---------- ------------------------------ ------------------
       999 V$SORT_SEGMENT                 SYNONYM
SQL> SET AUTOT ON
SQL> SELECT * FROM T_INDEX_ORG
2 WHERE NAME = 'FIRST';
        ID NAME                           TYPE
---------- ------------------------------ ------------------
         0 FIRST                          TEST

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=41)
   1    0   INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_32243' (UNIQUE) (Cost=1 Card=1 Bytes=41)
   2    1     INDEX (RANGE SCAN) OF 'IND_INDEX_ORG_NAME' (NON-UNIQUE) (Cost=1 Card=1)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          6 consistent gets
          0 physical reads
          0 redo size
        484 bytes sent via SQL*Net to client
        372 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
SQL> SELECT * FROM T_INDEX_ORG
2 WHERE NAME = 'V$SORT_SEGMENT';
        ID NAME                           TYPE
---------- ------------------------------ ------------------
       999 V$SORT_SEGMENT                 SYNONYM

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        498 bytes sent via SQL*Net to client
        372 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
可以看到，虽然二者的执行计划完全一样，但是物理猜没有命中，导致额外的主键扫描会增加逻辑IO，这个例子就增加了2次逻辑IO。
SQL> SELECTROWID,ID, NAME FROM T_INDEX_ORG
2 WHERE NAME IN ('FIRST', 'V$SORT_SEGMENT');
ROWID                                             ID NAME
----------------------------------------- ---------- ------------------------------
*BAJAABwBgP4                                       0 FIRST
*BAJAAB4Dwgpk/g                                  999 V$SORT_SEGMENT

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=47)
   1    0   INLIST ITERATOR
   2    1     INDEX (RANGE SCAN) OF 'IND_INDEX_ORG_NAME' (NON-UNIQUE) (Cost=2 Card=1 Bytes=47)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          5 consistent gets
          0 physical reads
          0 redo size
        557 bytes sent via SQL*Net to client
        372 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          2 rows processed
顺便提一句，由于索引组织表包含的ROWID信息为逻辑ROWID，而逻辑ROWID有包含了主键信息，所以上面这个包含了ROWID、主键和索引列的查询，仅通过索引扫描就可以实现，而不需要再扫描主键了。

第一次看CONCEPT的时候，一直不是很明白，Oracle索引组织表中逻辑ROWID的物理猜是如何实现的，而这次看的时候很自然的就想明白其中的实现。
索引组织表中逻辑ROWID的物理猜：http://yangtingkun.itpub.net/post/468/477286
索引组织表中逻辑ROWID的物理猜（二）：http://yangtingkun.itpub.net/post/468/477404
通过分析数据块验证上一篇文章中的例子。

上一篇文章已经分析了物理猜的过程，不过这个过程基于了一个假设，就是先插入的记录已经发生了位置上的变化，即物理猜无法命中，而随后查询的记录可以直接通过物理猜来命中查询，下面对表的数据块进行DUMP，检查这个假设是否成立。
SQL> DROP TABLE T_INDEX_ORG;
表已删除。
SQL> CREATE TABLE T_INDEX_ORG
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 TYPE VARCHAR2(18))
5 ORGANIZATION INDEX;
表已创建。
SQL> CREATE INDEX IND_INDEX_ORG_NAME ON T_INDEX_ORG (NAME);
索引已创建。
SQL> INSERT INTO T_INDEX_ORG VALUES (0, 'FIRST', 'TEST');
已创建1行。
SQL>COLD_ROWID FORMAT A45
SQL> SELECT DUMP(ROWID, 16) D_ROWID FROM T_INDEX_ORG;
D_ROWID
---------------------------------------------
Typ=208 Len=9: 2,4,2,40,0,1c,1,80,fe
SQL> INSERT INTO T_INDEX_ORG
2 SELECT ROWNUM, OBJECT_NAME, OBJECT_TYPE
3 FROM DBA_OBJECTS
4 WHERE ROWNUM < 1000;
已创建999行。
SQL> COMMIT;
提交完成。
SQL> SELECT DUMP(ROWID, 16) D_ROWID FROM T_INDEX_ORG
2 WHERE ID = 0;
D_ROWID
---------------------------------------------
Typ=208 Len=9: 2,4,2,40,0,1f,1,80,fe
先删除测试表，然后根据上一篇文章的步骤重复测试，不过在插入记录0之后马上检查这条记录的ROWID信息。
然后插入大量的记录，再次检查记录0的ROWID信息，这时可以发现，记录0的ROWID已经发生了变化。
这也是逻辑ROWID和物理ROWID不同之处，由于数据的存储位置发生了变化，逻辑ROWID也随之发生了变化。
下面看看索引组织表的第二索引中保存中保存的ROWID是否是旧的ROWID信息：
SQL> SELECT HEADER_FILE, HEADER_BLOCK
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_NAME = 'IND_INDEX_ORG_NAME'
4 AND WNER = USER;
HEADER_FILE HEADER_BLOCK
----------- ------------
          9           51
第51个BLOCK是索引的头信息，所以BLOCK 52开始保存索引数据：
SQL> ALTER SYSTEM DUMP DATAFILE 9 BLOCK 52;
系统已更改。
结果如下：
Start dump data blocks tsn: 9 file#: 9 minblk 52 maxblk 52
buffer tsn: 9 rdba: 0x02400034 (9/52)
scn: 0x0001.74f1b951 seq: 0x02 flg: 0x00 tail: 0xb9510602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x02400034
Object id on Block? Y
seg/obj: 0x7e09 csc: 0x01.74f1b951 itc: 1 flg: E typ: 2 - INDEX
     brn: 0 bdba: 0x2400031 ver: 0x01
     inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0009.016.0000230e 0x00800273.00b4.03 C---    0 scn 0x0001.74f1b951

Branch block dump
=================
header address 55119436=0x3490e4c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 6
kdxcofbo 40=0x28
kdxcofeo 7997=0x1f3d
kdxcoavs 7957
kdxbrlmc 37748791=0x2400037
kdxbrsno 3
kdxbrbksz 8060
row#0[8052] dba: 37748792=0x2400038
col 0; len 2; (2): 41 55
col 1; TERM
row#1[8042] dba: 37748789=0x2400035
col 0; len 4; (4): 495f4d4c
col 1; TERM
row#2[8032] dba: 37748790=0x2400036
col 0; len 4; (4): 4d 45 54 48
col 1; TERM
row#3[7997] dba: 37749345=0x2400261
col 0; len 10; (10): 56 24 42 41 43 4b 55 505f53
col 1; TERM
row#4[8013] dba: 37749352=0x2400268
col 0; len 3; (3): 56 24 48
col 1; TERM
row#5[8022] dba: 37749351=0x2400267
col 0; len 4; (4): 565f24 4d
col 1; TERM
----- end of branch block dump -----
显然这是根节点，下面寻找包含FIRST记录的叶节点：
SQL> SELECT CHR(TO_NUMBER('41', 'XX')) FROM DUAL;
CH
--
A
SQL> SELECT CHR(TO_NUMBER('49', 'XX')) FROM DUAL;
CH
--
I
显然0X2400038地址对应的BLOCK就是包含FIRST记录的地址：
SQL> ALTER SYSTEM DUMP DATAFILE 9 BLOCK 56;
系统已更改。
找到FIRST键值对应的ASCII码：
SQL> SELECT DUMP('FIRST', 16) FROM DUAL;
DUMP('FIRST',16)
----------------------------
Typ=96 Len=5: 46,49,52,53,54
从DUMP文件中找到对应的结果：
Dump of memory from 0x034926E2 to 0x034926E6
34926E0 4002040102061C00                    [...@....]
row#84[6274] flag: K---S, lock: 2
col 0; len 5; (5): 46 49 52 53 54
col 1; len 1; (1): 80
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4]
可以清晰的看到逻辑ROWID并没有被整体保存，而是物理地址0240001C和主键0（NUMBER类型保存格式为80）分别保存。
而这里保存的地址信息是记录发生变化之前的信息，所以这个物理猜是不准确的，Oracle需要根据主键再次定位记录的位置。
从这里也可以看到，索引组织表的逻辑ROWID是随时发生变化的，因此一旦索引组织表的数据发生大量的修改，就会导致第二索引中保存的物理猜不再准确，所以定期或在大量修改索引组织表之后，重建第二索引还是对提高性能有帮助的。