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

[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,避免当天建立索引很小，没几天变大很快的情况。

时间： 2024-09-20 18:35:39

[20150626]建立索引pctfree=0.txt的相关文章

[20140714]关于在那些字段建立索引的问题

[20140714]关于在那些字段建立索引的问题.txt 前几天有人问我,如何确定在那些字段建立索引的问题,我的方法很简单,通过awr报表可以来确定一部分索引的建立,应用程序绑定做的很 好,方法很简单,可以使用toad的SGA trace,组合一些查询条件很容易确定,这个方法的缺陷就是,如果你应用绑定没做好,shared pool设置 很大的情况下,每次扫描真的是一种灾难!(不是很快,多次操作心里很烦!) 与别人交谈,提到利用col_usage$,可以知道谓词的使用情况,从而决定在那些字段做了索

java-Lucene3.0.3配置 建立索引

问题描述 Lucene3.0.3配置 建立索引 进行Luence配置的过程中,到"Luence开发包中Demo调试"这步,"建立索引",在DOS命令窗口中输入>java org.apache.lucene.demo.IndexFiles [D:Java],回车,之后提示"文件名.目录名或卷标语法不正确."问题出在哪里?输入Javac,有结果,应该不是环境变量设置的问题吧.求指点~ 解决方案 http://www.360doc.com/con

[20150926]索引压缩问题.txt

[20150926]索引压缩问题.txt --以前如果索引前缀重复值很多,我会选择索引压缩,这样减少磁盘空间占用,索引范围扫描也可以减少磁盘IO,虽然这样可能消耗一些 --CUP资源,感觉影响不大. --看了链接感觉自己忽略一些问题,可能导致达不到预期效果. --通过例子来说明: 1.建立测试环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                          

[20131015]关于索引块分裂.txt

[20131015]关于索引块分裂.txt 前几天看了链接:http://www.askmaclean.com/archives/index-split.html 提到:oracle中的索引块分裂主要分成 以下几种:     leaf node 90-10 splits    leaf node 50-50 splits    branch node splits    root node splits 按照 leaf Block Split 分裂时的行为 又可以分为: leaf node 90

Oracle管理索引（三）Oracle建立索引

1.建立b-tree索引 (1)相关概念 根块(一个):索引顶级块,它包含指向下一级节点(分支块或叶块)的信息. 分支块:它包含指向下一级的节点(分支块或叶块)的信息. 叶块:它包含索引入口数据,索引入口包含索引列值或受限ROWID (2)建立索引 如果在where子句中要经常引用某列或某几列,应该给予这些列值建立B-*树索引 10:23:58 SQL> create index ind_ename on scott.emp(ename) pctfree 30 10:24:32   2  tab

Solr DIH: 基于MySQL表数据建立索引

选择使用Solr,对数据库中数据进行索引,可以单独写程序将数据库中的数据导出并建立索引,这个过程可能对于数据处理的控制更灵活一些,但是却可能带来很大的工作量.选择使用Solr的DIH组件,可以很方便的对数据库表中数据进行索引,下面基于MySQL数据库实现建立索引. 首先,需要设计你的schema,最主要的工作是,将数据库表中字段映射为Lucene索引(Solr直接使用Lucene的索引格式和数据)的Field,从而将数据表中的一条记录映射为Lucene中的Document,然后进行索引.另外,在

c#删除移动硬盘中$RECYCLE.BIN的文件、建立索引文件

  using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.IO; using System.Threading; namespace 为硬盘文件建立索引 {     public partial c

Oracle数据库中建立索引的基本方法讲解_oracle

怎样建立最佳索引? 1.明确地创建索引 create index index_name on table_name(field_name) tablespace tablespace_name pctfree 5 initrans 2 maxtrans 255 storage ( minextents 1 maxextents 16382 pctincrease 0 ); 2.创建基于函数的索引 常用与UPPER.LOWER.TO_CHAR(date)等函数分类上,例: create index

[20171202]关于函数索引的状态.txt

[20171202]关于函数索引的状态.txt --//我曾经在一篇贴子提到索引可以disable吗?链接: --//http://blog.itpub.net/267265/viewspace-2123537/ --//实际上仅仅函数索引能disable,为什么呢?实际上自己以前并不搞清楚实际上这个跟oracle使用函数的特殊性有关. --//如果一个表删除某个字段,对应的索引也会删除.如果定义的函数删除了,对应的函数索引呢?通过例子来说明问题: 1.环境: SCOTT@test01p> @