索引组织表(IOT) OVERFLOW/PCTTHRESHOLD/INCLUDING参数浅析-优快云博客

本文介绍了Oracle Index-Organized Tables (IOT) 中的OVERFLOW子句，用于处理当行数据过大时的存储策略。PCTTHRESHOLD参数定义了索引块中存储行的最大比例，超过该比例的列将存储在溢出段。INCLUDING子句则指定了哪些非键列与主键一起存储在索引块内，其余部分存于溢出段。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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
/

EODA@PROD1> insert into iot values (1,2,'abcde');

1 row created.

EODA@PROD1> insert into iot values (2,4,'aabbcc');

1 row created.

EODA@PROD1> commit;

Commit complete.

EODA@PROD1> 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

EODA@PROD1>  select object_id, object_name from dba_objects where object_name like 'IOT_PK';

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



EODA@PROD1> 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

EODA@PROD1> variable file# number
EODA@PROD1> variable block# number
EODA@PROD1> execute :file#:=dbms_utility.data_block_address_file(to_number('1026473','xxxxxxxxxx'));  

EODA@PROD1> execute :file#:=dbms_utility.data_block_address_file(to_number('1026473','xxxxxxxxxx'));  

PL/SQL procedure successfully completed.

EODA@PROD1> execute :block#:=dbms_utility.data_block_address_block(to_number('1026473','xxxxxxxxxx'));

PL/SQL procedure successfully completed.

EODA@PROD1> print file#

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

EODA@PROD1> print block# 

    BLOCK#
----------
    156787
	
SYS@PROD1> 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

SYS@PROD1> 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

SYS@PROD1> 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
 
SYS@PROD1> 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
SYS@PROD1> variable file# number
SYS@PROD1> variable block# number  
SYS@PROD1> execute :file#:=dbms_utility.data_block_address_file(to_number('0102646b','xxxxxxxxxx')); 

PL/SQL procedure successfully completed.

SYS@PROD1> execute :block#:=dbms_utility.data_block_address_block(to_number('0102646b','xxxxxxxxxx'));

PL/SQL procedure successfully completed.

SYS@PROD1> print file#

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

SYS@PROD1> print block#

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

SYS@PROD1> 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保存在溢出段中。