Oracle Scratchpad

December 11, 2011

IOT Trap

Filed under: Infrastructure,IOT,Oracle — Jonathan Lewis @ 6:04 pm GMT 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 physically re-arranged the column ordering (internally) to put the primary key columns first. The OP had put the fifth column of the primary key after several extra 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 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.

rem
rem     Script:         iot_include.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sept 2011
rem 

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 physical 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.

We can use the view user_tab_cols (not user_tab_columns) to display the physical storage order by reporting the segment_column_id (and there’s a third “id” which is the internal_column_id, which comes into play in other circumstances).


select 
        column_id, column_name, segment_column_id 
from 
        user_tab_cols
where 
        table_name = 'T1'
order by 
        column_id
/

 COLUMN_ID COLUMN_NAME          SEGMENT_COLUMN_ID
---------- -------------------- -----------------
         1 ID1                                  1
         2 ID2                                  2
         3 V1                                   4
         4 V2                                   5
         5 ID3                                  3
         6 V3                                   6

6 rows selected.

Note how id3 is reported with column_id = 5, but segment_column_id = 3.

 

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 physical column ordering internally, so what you see is NOT what you get. You can check the view user_tab_cols to get a better pricture of how Oracle is handling the table.

3 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 GMT 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 GMT Jan 18,2012 | Reply

  3. […] A trap when using the “including” clause (Dec 2011) […]

    Pingback by IOTs | Oracle Scratchpad — February 11, 2021 @ 5:46 pm GMT Feb 11,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.