下面来分析一下在Oracle中进行已索引关键字的UPDATE动作时,会发生什么事情。
索引更新完全不同于表更新,在表更新中,数据是在数据块内部改变的(假设数据块中有足够的空间来允许进行这种改变);但在索引更新中,如果有关键字发生改变,那么它在树中的位置也需要发生改变。请记住,一个关键字在B *树中有且只有一个位置。因此,当某个关键字
发生改变时,关键字的旧表项必须被删除,并且需要在一个新的叶节点上创建一个新的关键字。旧的表项有可能永远不会被重新使用,这是因为只有在非常特殊的情况下, Oracle才会重用关键字表项槽,例如,新插入的关键字正好是被删除的那个关键字(包括数据类型、长度
等等)。(这里重用的是块,但完全插入相同的值的时候,也不一定插入在原来的被删除的位置,只是插入在原来的块中,可能是该块中的一个新位置。也正因为如此,在索引块中保存的的记录可能并不是根据关键字顺序排列的,随着update等的操作,会发生变化。)那么,这种情况发生的可能性有多大呢?许多应用程序使用一个数列来产生NUMBER关键字(特别是主关键字)。除非它们使用了RECYCLE选项,否则这个数列将不会两次产生完全相同的数。这样,索引中被删除的空间一直没有被使用。这就是在大规模删除与更新过程中,表大小不断减小或至少保持不变但索引不断加大的原因。
通过上面对B *树的分析,可以得出以下的应用准则:
1)避免对那些可能会产生很高的更新动作的列进行索引。
2)避免对那些经常会被删除的表中的多个列进行索引。若有可能,只对那些在这样的表上会进行删除的主关键字与/或列进行索引。如果对多个列进行索引是不可避免的,那么就应该考虑根据这些列对表进行划分,然后在每个这样的划分上执行TRUNCATE动作(而不是DELETE动作)。TRUNCATE在与DROP STORAGE短语一同使用时,通过重新设置高水位标来模拟删除表与索引以及重新创建表与索引的过程。
3)避免为那些唯一度不高的列创建B*树索引。这样的低选择性将会导致树节点块的稠密性,从而导致由于索引“平铺( flat)”而出现的大规模索引扫描。唯一性的程度越高,性能就越好,因为这样能够减少范围扫描,甚至可能用唯一扫描来取代范围扫描。
4)空值不应该存储在单列索引中。对于复合索引的方式,只有当某个列不空时,才需要进行值的存储。在为DML语句创建IS NULL或IS NOT NULL短语时,应该切记这个问题。
5)IS NULL不会导致索引扫描,而一个没有带任何限制的IS NOT NULL则可能会导致完全索引扫描。
2. PCTFREE的重要性
对于B*树索引, PCTFREE可以决定叶节点拆分的extent。也就是说,PCTFREE用来说明在某个块中的自由空间数目,以便于后来的更新动作。但是,对于索引(与表不同),这些更新动作没有任何意义,因为更新会删除一个索引,然后又出现插入一个新索引。
对于索引,PCTFREE大多数是在索引创建过程中发生作用,可以用一个非零值来说明块拆分比例。如果在索引创建过程中,PCTFREE被设置为20,那么有80%的叶节点将可能会包含关键字信息。但是,剩余的20%将用来作为关键字信息后来插入到叶节点块中时使用。这样将能够保证在需要进行叶节点块的拆分时,运行时的插入开销最小。虽然一个较高的PCTFREE可能会使得索引创建时间增加,但它能够防止在实际的使用过程中命中性能的降低。因此,那些正在等待某个行被插入的终端用户并不会因为需要进行叶节点块的拆分而使得自己的性能受到影响。
基于上述信息,可以得出以下结论:
1)某个索引的PCTFREE主要是在索引创建时使用。在实际的应用过程中,PCTFREE将被忽略。
2)如果表是一个经常被访问、包含有大量DML改变(通过交互式用户屏幕)的表,那么就应该为OLTP应用程序指定一个较高的PCTFREE。
3)如果索引创建时间很关键,那么就应该指定一个较低PCTFREE。这样在每个叶节点块中将会压缩有多个行,从而可以避免在索引创建时进行更多的拆分。这一点对于2 4×7客户站点非常重要,因为在大多数情况下索引创建过程需要很多的系统停工时间(特别是在表有几百万行时更为如此)。
4)对于任何其值不断增加的列,最好是设置一个非常低的PCTFREE(甚至可以为0)。这是因为只有那些最右方的叶节点块总是会被插入,从而使得树向右增长。而左边的叶节点将一直为静止状态,因此没有必要使得这些块的任何部分为空(通过使用非零PCTFREE)。
SQL> create table test(id number,name varchar2(100));
表已创建
SQL> create index idx_test on test(id);
索引已创建
SQL> select file_id,extent_id,block_id from dba_extents where segment_name='IDX_TEST' ;
FILE_ID EXTENT_ID BLOCK_ID
---------- ---------- ----------
6 0 41
此时索引为空,我们来转储一下41号块的信息
SQL> alter system dump datafile 6 block 41;
系统已更改。
可以通过以下的方法得到转储的数据文件
在Oracle Database 11g之前,要想获得跟踪文件的名称,通常我们需要执行一系列的查询,常用的脚本如下:
SQL> SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc'
2 trace_file
3 FROM (SELECT VALUE
4 FROM v$parameter
5 WHERE NAME = 'user_dump_dest') a,
6 (SELECT SUBSTR (VALUE, -6, 1) symbol
7 FROM v$parameter
8 WHERE NAME = 'user_dump_dest') b,
9 (SELECT instance_name FROM v$instance) c,
10 (SELECT spid
11 FROM v$session s, v$process p, v$mystat m
12 WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
13 /
TRACE_FILE
--------------------------------------------------------------------------------
F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_10076.trc
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = "Default Trace File";
VALUE
-------------------------------------------------------------------------------
F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_10076.trc
既然已经找到了转储文件,那就看看转储文件的内容:
Start dump data blocks tsn: 7 file#: 6 minblk 41 maxblk 41 --转储的文件号和块号
buffer tsn: 7 rdba: 0x01800029 (6/41)
scn: 0x0000.000bddf9 seq: 0x01 flg: 0x04 tail: 0xddf92001
frmt: 0x02 chkval: 0x4b0e type: 0x20=FIRST LEVEL BITMAP BLOCK --这个表示它是一级位图块,直接和数据块打交道
Hex dump of block: st=0, typ_found=1
Dump of First Level Bitmap Block
--------------------------------
nbits : 2 nranges: 1 parent dba: 0x0180002a poffset: 0 --标红的表示它的父级位图块
unformatted: 12 total: 16 first useful block: 3
owning instance : 1
instance ownership changed at 04/20/2013 12:08:33
Last successful Search 04/20/2013 12:08:33
Freeness Status: nf1 0 nf2 1 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 52590
HWM Flag: HWM Set
Highwater:: 0x0180002d ext#: 0 blk#: 4 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01800029 Length: 16 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:25-50% free
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
--------------------------------------------------------
End dump data blocks tsn: 7 file#: 6 minblk 41 maxblk 41
-标红的这些块被用来存储元数据,其余的块用来存储数据,注意到总共有16个块,full表示数据块已经用完了。
如果这些块用完的话,oracle就会使用二级位图结构,以此类推,会出现三级位图结构,oracle目前支持到三级位图结构,上面的表示这些块暂时未被使用,根据以上的说明oracle会从第4个块(44)开始存放索引,索引的branch_code应该放在第44块中,接下来我们就分析索引是如何分布的。
下面是一个rdba转换为具体的文件和块号的方法,后面会用到。
create or replace function getbfno(p_dba in varchar2)
return varchar2
is
l_str varchar2(255) default null;
begin
l_str :='datafile# is:'
||dbms_utility.data_block_address_file(TO_NUMBER(LTRIM(P_DBA,'0x'),'xxxxxxxx'))
||chr(10) ||'datablock is:'
||dbms_utility.data_block_address_block(TO_NUMBER(LTRIM(P_DBA,'0x'),'xxxxxxxx'));
return l_str;
end;
--去上面的rdba得到如下的转换
SQL> select getbfno('0x01800029') from dual;
GETBFNO('0X01800029')
---------------------------------------------
datafile# is:6
datablock is:41
在开始插入索引前,我们下去看看 parent dba: 0x0180002a 这个里面有什么东西,也就是是二级位图块。
SQL> select getbfno('0x0180002a') from dual;
GETBFNO('0X0180002A')
-------------------------------------------------
datafile# is:6