索引组织表(IOT) OVERFLOW/PCTTHRESHOLD/INCLUDING参数浅析

Creating Index-Organized Tables

Optionly, you can specify the following:

• An 

  OVERFLOW

   clause, which preserves dense clustering of the B-tree index by enabling the storage of some of the nonkey columns in a separate overflow data segment.OVERFLOW子句允许你建立另一个段，如果IOT行数据太大就可以溢出到这个段中。

• A 

  PCTTHRESHOLD

   value, which, when an overflow segment is being used, defines the maximum size of the portion of the row that is stored in the index block, as a percentage of block size. Rows columns that would cause the row size to exceed this maximum are stored in the overflow segment. The row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the nonkey column values that fit the specified threshold, and a pointer to the rest of the row.当一行的数据超过块的这个百分比时行中余下列将被存储在溢出段中。
• An 

  INCLUDING

   clause, which can be used to specify the nonkey columns that are to be stored in the index block with the primary key.行中从第一列直到INCLUDING子句所指定的列都存储在索引叶块上，余下的存储在溢出段中。

--建立环境
create table iot
  (  x    int,
     y    int,
     z    varchar2(2000),
     constraint iot_pk primary key (x)
)
organization index
including y     --设置成z列保存在溢出段中
overflow
/

[email protected]> insert into iot values (1,2,'abcde');

1 row created.

[email protected]> insert into iot values (2,4,'aabbcc');

1 row created.

[email protected]> commit;

Commit complete.

[email protected]> select index_name, index_type, table_name, PCT_THRESHOLD, CLUSTERING_FACTOR from user_indexes where table_name like '%IOT%';

INDEX_NAME		       INDEX_TYPE		   TABLE_NAME       PCT_THRESHOLD      CLUSTERING_FACTOR
------------------------------ --------------------------- ------------------------------
SYS_IOT_TOP_85796	       IOT - TOP		   IOT_ADDRESSES      	   50		      0
IOT_PK			       IOT - TOP		   IOT          	   50

[email protected]>  select object_id, object_name from dba_objects where object_name like 'IOT_PK';

 OBJECT_ID       OBJECT_NAME
----------       -------------------------------------------
     85900          IOT_PK



[email protected]> alter session set events 'immediate trace name treedump level 85900';

Session altered.


----- begin tree dump
leaf: 0x1026473 16934003 (0: nrow: 2 rrow: 2)   --找到索引RBA
----- end tree dump

[email protected]> variable file# number
[email protected]> variable block# number
[email protected]> execute :file#:=dbms_utility.data_block_address_file(to_number('1026473','xxxxxxxxxx'));  

[email protected]> execute :file#:=dbms_utility.data_block_address_file(to_number('1026473','xxxxxxxxxx'));  

PL/SQL procedure successfully completed.

[email protected]> execute :block#:=dbms_utility.data_block_address_block(to_number('1026473','xxxxxxxxxx'));

PL/SQL procedure successfully completed.

[email protected]> print file#

     FILE#
----------
	 4

[email protected]> print block# 

    BLOCK#
----------
    156787
	
[email protected]> alter system dump datafile 4 block 156787;

System altered.

--查看dump文件
row#0[8015] flag: K-----, lock: 2, len=17
col 0; len 2; (2):  c1 02
tl: 12 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x0102646b.0  			--溢出段
col  0: [ 2]  c1 03
row#1[7998] flag: K-----, lock: 2, len=17
col 0; len 2; (2):  c1 03
tl: 12 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x0102646b.1
col  0: [ 2]  c1 05 
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 156787 maxblk 156787

[email protected]> select x,dump(x,16) from eoda.iot;    --与dump文件中符合

	 X            DUMP(X,16)
----------    ----------------------------------------------------------------------------------------------------
	 1            Typ=2 Len=2: c1,2         
	 2		      Typ=2 Len=2: c1,3

[email protected]> select y,dump(y,16) from eoda.iot;    --与dump文件中符合

	 Y            DUMP(Y,16)
----------    ----------------------------------------------------------------------------------------------------
	 2            Typ=2 Len=2: c1,3
	 4			  Typ=2 Len=2: c1,5
 
[email protected]> select z,dump(z,16) from eoda.iot;    --z字段确实并未出现在与x/y相同的字段中

Z                 DUMP(Z,16)
-----------   ---------------------------------------------------------------------------------------
abcde             Typ=1 Len=5: 61,62,63,64,65
aabbcc            Typ=1 Len=6: 61,61,62,62,63,63


--继续探寻0102646b
[email protected]> variable file# number
[email protected]> variable block# number  
[email protected]> execute :file#:=dbms_utility.data_block_address_file(to_number('0102646b','xxxxxxxxxx')); 

PL/SQL procedure successfully completed.

[email protected]> execute :block#:=dbms_utility.data_block_address_block(to_number('0102646b','xxxxxxxxxx'));

PL/SQL procedure successfully completed.

[email protected]> print file#

     FILE#
----------
	 4

[email protected]> print block#

    BLOCK#
----------
    156779

[email protected]> alter system dump datafile 4 block 156779;

System altered.

--查看dump文件
data_block_dump,data header at 0xbb9664
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x00bb9664
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f85
avsp=0x1f6f
tosp=0x1f6f
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f8f
0x14:pri[1]     offs=0x1f85
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 9 fb: -----L-- lb: 0x1  cc: 1
col  0: [ 5]  61 62 63 64 65   --与z列dump出来的数据相符说明就是z列
tab 0, row 1, @0x1f85
tl: 10 fb: -----L-- lb: 0x1  cc: 1
col  0: [ 6]  61 61 62 62 63 63   --与z列dump出来的数据相符说明就是z列
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 156779 maxblk 156779

--很明显设置了including子句后，xy保存在同一段中，而z保存在溢出段中。