[20150626]建立索引pctfree=0.txt

简介: [20150626]建立索引pctfree=0.txt --昨天看了链接: https://richardfoote.wordpress.com/2015/06/25/quiz-time-why-do-deletes-cause-an-index-to-g...

[20150626]建立索引pctfree=0.txt

--昨天看了链接:
https://richardfoote.wordpress.com/2015/06/25/quiz-time-why-do-deletes-cause-an-index-to-grow-up-the-hill-backwards/

--自己测试看看来解答问题,不知道是否正确:-)

1.建立测试环境:

SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table bowie (id number, name varchar2(42));
insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level commit;

--建立pctfree=0 的索引:

create index bowie_id_i on bowie(id) pctfree 0;

SCOTT@test> select object_id from dba_objects where owner=user and object_name='BOWIE_ID_I';
OBJECT_ID
----------
    306580

alter session set events 'immediate trace name treedump level 306580';

----- begin tree dump
branch: 0x100070b 16779019 (0: nrow: 19, level: 1)
   leaf: 0x100070c 16779020 (-1: nrow: 540 rrow: 540)
   leaf: 0x100070d 16779021 (0: nrow: 533 rrow: 533)
   leaf: 0x100070e 16779022 (1: nrow: 533 rrow: 533)
   leaf: 0x100070f 16779023 (2: nrow: 533 rrow: 533)
   leaf: 0x1000710 16779024 (3: nrow: 533 rrow: 533)
   leaf: 0x1000711 16779025 (4: nrow: 533 rrow: 533)
   leaf: 0x1000712 16779026 (5: nrow: 533 rrow: 533)
   leaf: 0x1000713 16779027 (6: nrow: 533 rrow: 533)
   leaf: 0x1000714 16779028 (7: nrow: 533 rrow: 533)
   leaf: 0x1000715 16779029 (8: nrow: 533 rrow: 533)
   leaf: 0x1000716 16779030 (9: nrow: 533 rrow: 533)
   leaf: 0x1000717 16779031 (10: nrow: 533 rrow: 533)
   leaf: 0x1000719 16779033 (11: nrow: 533 rrow: 533)
   leaf: 0x100071a 16779034 (12: nrow: 533 rrow: 533)
   leaf: 0x100071b 16779035 (13: nrow: 533 rrow: 533)
   leaf: 0x100071c 16779036 (14: nrow: 533 rrow: 533)
   leaf: 0x100071d 16779037 (15: nrow: 533 rrow: 533)
   leaf: 0x100071e 16779038 (16: nrow: 533 rrow: 533)
   leaf: 0x100071f 16779039 (17: nrow: 399 rrow: 399)
----- end tree dump
--说明:作者测试环境是12c,转储的信息有点不同。里面还包含剩余空间的大小。
--每块533条记录(大多数情况)。

2.删除数据看看:
--session 1:

SCOTT@test> delete bowie where id in (select rownum*499 from dual connect by level20 rows deleted.

--根据这个条件,可以看出机会每块索引叶子都有索引信息删除。看看dba=0x100070c的叶子的情况:

SCOTT@test> @dfb 100070c

    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
         4       1804 alter system dump datafile 4 block 1804 ;

--需要执行1次alter system checkpoint;这样脏块才能写盘。

SCOTT@test> alter system checkpoint;
System altered.

SCOTT@test> alter system dump datafile 4 block 1804 ;
System altered.

Block header dump:  0x0100070c
Object id on Block? Y
seg/obj: 0x4ad94  csc: 0x02.f4bd366b  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1000708 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x000c.019.00003ba9  0x00c000ce.0fc4.18  ----    1  fsc 0x000f.00000000
Leaf block dump
===============
header address 182923336292=0x2a97149a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 540
kdxcofbo 1116=0x45c
kdxcofeo 1116=0x45c
kdxcoavs 0
kdxlespl 0
kdxlende 1
kdxlenxt 16779021=0x100070d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
....
row#498[1648] flag: ---D--, lock: 2, len=13
col 0; len 3; (3):  c2 05 64
col 1; len 6; (6):  01 00 05 e5 00 94
row#499[1636] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c2 06
col 1; len 6; (6):  01 00 05 e5 00 95
...
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1804 maxblk 1804

--可以看到row#498 的lock=2,对应itl=2的事务。

3.打开新会话,执行delete操作:

--如果这个时候不提交,打开新的会话
--session 2:

SCOTT@test> delete bowie where id in (select rownum*500 from dual connect by level20 rows deleted.

SCOTT@test> alter system checkpoint;
System altered.

----- begin tree dump
branch: 0x100070b 16779019 (0: nrow: 37, level: 1)
   leaf: 0x100070c 16779020 (-1: nrow: 278 rrow: 278)
   leaf: 0x1000722 16779042 (0: nrow: 262 rrow: 260)
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   leaf: 0x100070d 16779021 (1: nrow: 271 rrow: 271)
   leaf: 0x1000723 16779043 (2: nrow: 262 rrow: 260)
   leaf: 0x100070e 16779022 (3: nrow: 271 rrow: 271)
   leaf: 0x1000724 16779044 (4: nrow: 262 rrow: 260)
   leaf: 0x100070f 16779023 (5: nrow: 271 rrow: 271)
   leaf: 0x1000725 16779045 (6: nrow: 262 rrow: 260)
   leaf: 0x1000710 16779024 (7: nrow: 271 rrow: 271)
   leaf: 0x1000726 16779046 (8: nrow: 262 rrow: 260)
   leaf: 0x1000711 16779025 (9: nrow: 271 rrow: 271)
   leaf: 0x1000727 16779047 (10: nrow: 262 rrow: 260)
   leaf: 0x1000712 16779026 (11: nrow: 271 rrow: 271)
   leaf: 0x1000720 16779040 (12: nrow: 262 rrow: 260)
   leaf: 0x1000713 16779027 (13: nrow: 271 rrow: 269)
   leaf: 0x1000721 16779041 (14: nrow: 262 rrow: 262)
   leaf: 0x1000714 16779028 (15: nrow: 271 rrow: 269)
   leaf: 0x100072f 16779055 (16: nrow: 262 rrow: 262)
   leaf: 0x1000715 16779029 (17: nrow: 271 rrow: 269)
   leaf: 0x1000729 16779049 (18: nrow: 262 rrow: 262)
   leaf: 0x1000716 16779030 (19: nrow: 271 rrow: 269)
   leaf: 0x100072a 16779050 (20: nrow: 262 rrow: 262)
   leaf: 0x1000717 16779031 (21: nrow: 271 rrow: 269)
   leaf: 0x100072e 16779054 (22: nrow: 262 rrow: 262)
   leaf: 0x1000719 16779033 (23: nrow: 271 rrow: 269)
   leaf: 0x100072b 16779051 (24: nrow: 262 rrow: 262)
   leaf: 0x100071a 16779034 (25: nrow: 271 rrow: 269)
   leaf: 0x100072c 16779052 (26: nrow: 262 rrow: 262)
   leaf: 0x100071b 16779035 (27: nrow: 271 rrow: 269)
   leaf: 0x100072d 16779053 (28: nrow: 262 rrow: 260)
   leaf: 0x100071c 16779036 (29: nrow: 271 rrow: 271)
   leaf: 0x1000732 16779058 (30: nrow: 262 rrow: 260)
   leaf: 0x100071d 16779037 (31: nrow: 264 rrow: 264)
   leaf: 0x1000733 16779059 (32: nrow: 269 rrow: 267)
   leaf: 0x100071e 16779038 (33: nrow: 271 rrow: 271)
   leaf: 0x1000734 16779060 (34: nrow: 262 rrow: 260)
   leaf: 0x100071f 16779039 (35: nrow: 399 rrow: 397)
----- end tree dump

----索引发生了分裂。注意看~的信息,nrow: 262 rrow: 260,说明删除的信息在这块里面。

SCOTT@test> @dfb16 0x1000722
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
         4       1826 alter system dump datafile 4 block 1826 ;

SCOTT@test> alter system dump datafile 4 block 1826 ;
System altered.

Block header dump:  0x01000722
Object id on Block? Y
seg/obj: 0x4ad94  csc: 0x02.f4bd3bc8  itc: 3  flg: E  typ: 2 - INDEX
     brn: 1  bdba: 0x1000718 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.001.0000d575  0x00c00aad.35a8.01  CB--    0  scn 0x0002.f4bd3bc7
0x02   0x000c.019.00003ba9  0x00c000ce.0fc4.18  ----    1  fsc 0x000f.00000000
0x03   0x0005.00c.0000d571  0x00c00aa9.35a8.0b  ----    1  fsc 0x000e.00000000
Leaf block dump
===============
header address 182923336316=0x2a97149a7c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 262
kdxcofbo 560=0x230
kdxcofeo 4605=0x11fd
kdxcoavs 4045
kdxlespl 0
kdxlende 2
kdxlenxt 16779021=0x100070d
kdxleprv 16779020=0x100070c
kdxledsz 0
kdxlebksz 8008
....
row#220[7463] flag: ---DS-, lock: 2, len=13
col 0; len 3; (3):  c2 05 64
col 1; len 6; (6):  01 00 05 e5 00 94
row#221[7476] flag: ---D--, lock: 3, len=12
col 0; len 2; (2):  c2 06
col 1; len 6; (6):  01 00 05 e5 00 95
....
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1826 maxblk 1826

SCOTT@test> @xid
X
------------------------------


XIDUSN XIDSLOT XIDSQN     UBAFIL     UBABLK     UBAREC     UBASQN STATUS  USED_UBLK  USED_UREC XID              ADDR             START_DATE          C70
------ ------- ------ ---------- ---------- ---------- ---------- ------ ---------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
     5      12  54641          3       2729         30      13736 ACTIVE          2         40 05000C0071D50000 00000000BA497588 2015-06-26 10:09:54 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU5_2973757209$' XID 5 12 54641;
                                                                                                                                                     ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU5_2973757209$';

    12      25  15273          3        206         42       4036 ACTIVE          1         39 0C001900A93B0000 00000000BA559658 2015-06-26 10:02:58 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU12_1585900997$' XID 12 25 15273;
                                                                                                                                                     ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU12_1585900997$';

--说明在第2个session删除记录时,要增加1个ITL,增加24字节,由于空间不足,索引块发生了50:50分裂,由于几乎每个索引块都发生
--这种分裂,导致索引变大1倍。

--如果你仔细看最后1个叶子节点:leaf: 0x100071f 16779039 (35: nrow: 399 rrow: 397),空间足够,没有发生分裂。

SCOTT@test> set verify off
SCOTT@test> @dfb16 0x100071f
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
         4       1823 alter system dump datafile 4 block 1823 ;

SCOTT@test> alter system dump datafile 4 block 1823 ;
System altered.

Block header dump:  0x0100071f
Object id on Block? Y
seg/obj: 0x4ad94  csc: 0x02.f4bd3c11  itc: 3  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1000718 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.000.0000d571  0x00c00acf.35a8.02  C---    0  scn 0x0002.f4bd3c10
0x02   0x000c.019.00003ba9  0x00c000ce.0fc4.2a  ----    1  fsc 0x000f.00000000
0x03   0x0005.00c.0000d571  0x00c00aa9.35a8.1e  ----    1  fsc 0x000e.00000000
Leaf block dump
===============
header address 182923336316=0x2a97149a7c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 399
kdxcofbo 834=0x342
kdxcofeo 2825=0xb09
kdxcoavs 1991
kdxlespl 0
kdxlende 2
kdxlenxt 0=0x0
kdxleprv 16779060=0x1000734
kdxledsz 0
kdxlebksz 8008

--可以发现空间足够,没有分裂。也许会问为什么不用itl=0x01槽,实际上作为索引分裂使用而保留下来的。而第2个会话执行的是
--delete,itl=0x02已经占用,只能请求itl=0x03,而空间不足,导致索引分裂。

4.如果保留pctfree=1这个问题就可以避免:

--drop table bowie purge;
create table bowie (id number, name varchar2(42));
insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level commit;

create index bowie_id_i on bowie(id) pctfree 1;

select object_id from dba_objects where object_name='BOWIE_ID_I';


--session 1:
delete bowie where id in (select rownum*499 from dual connect by level

--session 2:
delete bowie where id in (select rownum*500 from dual connect by level

 

SCOTT@test> alter session set events 'immediate trace name treedump level 306583';
Session altered.

----- begin tree dump
branch: 0x100070b 16779019 (0: nrow: 19, level: 1)
   leaf: 0x100070c 16779020 (-1: nrow: 534 rrow: 532)
   leaf: 0x100070d 16779021 (0: nrow: 528 rrow: 526)
   leaf: 0x100070e 16779022 (1: nrow: 528 rrow: 526)
   leaf: 0x100070f 16779023 (2: nrow: 528 rrow: 526)
   leaf: 0x1000710 16779024 (3: nrow: 528 rrow: 526)
   leaf: 0x1000711 16779025 (4: nrow: 528 rrow: 526)
   leaf: 0x1000712 16779026 (5: nrow: 528 rrow: 526)
   leaf: 0x1000713 16779027 (6: nrow: 528 rrow: 526)
   leaf: 0x1000714 16779028 (7: nrow: 528 rrow: 526)
   leaf: 0x1000715 16779029 (8: nrow: 528 rrow: 526)
   leaf: 0x1000716 16779030 (9: nrow: 528 rrow: 526)
   leaf: 0x1000717 16779031 (10: nrow: 528 rrow: 526)
   leaf: 0x1000719 16779033 (11: nrow: 528 rrow: 526)
   leaf: 0x100071a 16779034 (12: nrow: 528 rrow: 526)
   leaf: 0x100071b 16779035 (13: nrow: 528 rrow: 526)
   leaf: 0x100071c 16779036 (14: nrow: 528 rrow: 526)
   leaf: 0x100071d 16779037 (15: nrow: 528 rrow: 525)
   leaf: 0x100071e 16779038 (16: nrow: 528 rrow: 525)
   leaf: 0x100071f 16779039 (17: nrow: 490 rrow: 488)
----- end tree dump

--这样就不存在分裂了。

总结:
--看来重建索引不要太贪心,选择pctfree=0的索引,当然如果你的dml不存在删除修改索引字段的操作,而索引字段是线性增加的类型,
--可以考虑pctfree=0,不过即使这样最好也保留1,避免当天建立索引很小,没几天变大很快的情况。

目录
相关文章
|
SQL 测试技术 索引
[20180509]函数索引问题.txt
[20180509]函数索引问题.txt https://jonathanlewis.wordpress.com/2018/05/07/fbis-dont-exist/ --//重复测试: 1.
1123 0
|
关系型数据库 Linux 索引
[20180212]函数索引问题.txt
[20180212]函数索引问题.txt --//11g下,如果函数索引,字段出现重复,出现ORA-54015: Duplicate column expression was specified.
995 0
|
Linux Windows
[20170925]建立文件分配大小.txt
[20170925]建立文件分配大小.txt --//有时候工作需要建立一个文件.一般在linux下使用dd.总结一下其他方法: 1.方法1: $ cat a.c #include #include #include #include #inc...
780 0
|
Oracle 关系型数据库 数据库管理
[20170209]索引范围访问2.txt
[20170209]索引范围访问2.txt --ITPUB网友问的问题: http://www.itpub.net/thread-2083504-1-1.html --索引范围扫描是如何访问数据块的? 1 FOR  (根节点-> 分支节点->叶节点->...
695 0
|
索引 关系型数据库 Oracle
[20170210]索引范围扫描3.txt
[20170210]索引范围扫描3.txt --昨天写了一篇索引范围扫描文章,链接:http://blog.itpub.net/267265/viewspace-2133289/ --才想起来我以前理解的一个错误,链接:http://blog.
828 0
|
SQL Shell 测试技术
[20161023]为什么以前可以这样的表.txt
[20161023]为什么以前可以这样的表.txt --上午看https://oracleblog.org/working-case/ora-01401-impdp-same-character/ CREATE TABLE ASS_ACC...
788 0
|
Oracle 关系型数据库 物联网
[20160908]唯一索引与非唯一索引.txt
[20160908]唯一索引与非唯一索引.txt --唯一索引与非唯一索引的区别在于rowid信息在索引的位置,唯一索引rowid在row header(数据部分).而非唯一索引在最后.
765 0
|
索引 关系型数据库 Oracle
[20160526]建立主键问题.txt
[20160526]建立主键问题.txt --生产系统有1个表没有主键,要求建立发现无法建立,有重复.而且这个索引查询是需要. --实际上可以限制以后的记录不再重复,以前我自己也遇到过,做一个记录.
627 0
|
Oracle 关系型数据库
[20151024]关于ctas与建立主键.txt
[20151024]关于ctas与建立主键.txt --前一阵子别人问的问题,就是ctas是否可以建立主键,对于这种情况平时不这么建立,我给看看文档。 --平时我建立测试表 create table t as select rownum id ,'test' n...
925 0
|
物联网 索引
[20151008]索引组织表上创建BITMAP索引.txt
[20151008]索引组织表上创建BITMAP索引.txt --IOT 是一种特殊的索引结构,使用它能够解决特定场合的应用问题,但是在许多应用中很少使用,更多的是使用堆表。
955 0