Oracle Scratchpad

December 11, 2011

IOT Trap

Filed under: Infrastructure,IOT,Oracle — Jonathan Lewis @ 6:04 pm BST Dec 11,2011

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.

2 Comments »

  1. Very nice documentation, explanation, and trap warning!

    Why someone wouldn’t start a table definition with the pk columns in order escapes me; though set definition doesn’t require it, it is just simpler to think about. And old habits die hard – I still endeavor a stab at ordering columns after the pk not nullable first and then least likely to actually be null if I have a notion (including evidence from existing systems). Of course that is aside from special considerations about where to put BIG columns in an IOT.

    Comment by Mark W. Farnham — December 11, 2011 @ 10:09 pm BST Dec 11,2011 | Reply

  2. [...] a great source of information on indexes and Oracle in general has previously discussed this same IOT Trap on his blog. Advertisement LD_AddCustomAttr("AdOpt", "1"); LD_AddCustomAttr("Origin", "other"); [...]

    Pingback by Index Organized Tables – Overflow Segment Part II (The Loneliest Guy) « Richard Foote’s Oracle Blog — January 18, 2012 @ 9:57 am BST Jan 18,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,014 other followers