IOT Trap

In a recent question on OTN someone asked why Oracle had put some columns into the overflow segment of an IOT when they had specified that they should be in the main index section (the “IOT_TOP”) by using the including clause.

The answer is simple and devious; there’s a little trap hidden in the including clause. It tells Oracle which columns to include, but it gets applied only after Oracle has re-arranged the column ordering (internally) to put the primary key columns first. The OP had put the last column of the primary key AFTER the dozen columns in the table that he wanted in the index section, but Oracle moved that column to the fifth position in the internal table definition, so didn’t include the desired 10 extra columns.

To demonstrate the feature, here’s a little sript to create an IOT and insert some data. I’ve given two possible including clauses to see how Oracle treats the columns. My primary key consists of the columns (id1, id2, id3) but I’ve put columns v1 and v2 ahead of id3 in my table definition.

create table t1 (
	id1	number,
	id2	number,
	v1	varchar2(10),
	v2	varchar2(10),
	id3	number,
	v3	varchar2(10),
	constraint t1_pk primary key (id1, id2, id3)
)
organization index
including id3
-- including v2
overflow
;


insert into t1 values(1,1,'AAAAAAAAAA','BBBBBBBBBB',1,'CCCCCCCCCC');
insert into t1 values(1,1,'AAAAAAAAAA','BBBBBBBBBB',2,'CCCCCCCCCC');
insert into t1 values(1,1,'AAAAAAAAAA','BBBBBBBBBB',3,'CCCCCCCCCC');
commit;

The including clause tells Oracle to include in the index segment all the columns “up to and and including the named column”, but if I dump the datablock that is the root block of the index, I get the following results. First when I include id3.

row#0[8016] flag: K-----, lock: 2, len=20
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
col 2; len 2; (2):  c1 02
tl: 9 fb: --H-F--- lb: 0x0  cc: 0
nrid:  0x0180008a.0
row#1[7996] flag: K-----, lock: 2, len=20
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
col 2; len 2; (2):  c1 03
tl: 9 fb: --H-F--- lb: 0x0  cc: 0
nrid:  0x0180008a.1
row#2[7976] flag: K-----, lock: 2, len=20
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
col 2; len 2; (2):  c1 04
tl: 9 fb: --H-F--- lb: 0x0  cc: 0
nrid:  0x0180008a.2

Notice that this segment holds data only for the three primary key columns – after rearranging the column ordering internally the columns “up to and including id3″ are just the primary key columns.

Now look what I get if I include v2:

row#0[7994] flag: K-----, lock: 2, len=42
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
col 2; len 2; (2):  c1 02
tl: 31 fb: --H-F--- lb: 0x0  cc: 2
nrid:  0x0180008a.0
col  0: [10]  41 41 41 41 41 41 41 41 41 41
col  1: [10]  42 42 42 42 42 42 42 42 42 42
row#1[7952] flag: K-----, lock: 2, len=42
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
col 2; len 2; (2):  c1 03
tl: 31 fb: --H-F--- lb: 0x0  cc: 2
nrid:  0x0180008a.1
col  0: [10]  41 41 41 41 41 41 41 41 41 41
col  1: [10]  42 42 42 42 42 42 42 42 42 42
row#2[7910] flag: K-----, lock: 2, len=42
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
col 2; len 2; (2):  c1 04
tl: 31 fb: --H-F--- lb: 0x0  cc: 2
nrid:  0x0180008a.2
col  0: [10]  41 41 41 41 41 41 41 41 41 41
col  1: [10]  42 42 42 42 42 42 42 42 42 42
----- end of leaf block dump -----

Notice that we now have both v1 and v2 in the index segment.

Summary: If you use the including clause when defining an IOT, you have to reference a non-key column if you want any column other than the key columns in the index segment. Oracle rearranges the column ordering internally, so what you see is NOT what you get.