Monday, June 1, 2009

Index Organized Tables - IOTs

IOTs are an Oracle table structured introduced since 9i. They are tables stored in an index segment with their data stored sorted according to their primary key.

e.g

create table iot(a int , b int ,c int ,d int, primary key (a,b)) organization index;

When to use :

1) When having selects that accessing a table always by using indexed columns on the where clause:

e.g

select b,d,c from iot where a=100;

2) Similar for tables that are accessed exclusively by their primery key:

e.g

select d,c from iot where a=100 and b=10;

3) When you want to ensure that your data are stored in a specific order (by PK). This is usefull to physically co-locate child data.

4) For tall "skinny" tables. Tables with small number of colums.

When not to use:

1) For tables that are have lots of inserts.
2) For tables with large number of columns
3) For potentially large and very large tables
4) Not in data warehouse and datamart schemas. Allthough you can use them for small child tables in such schemas.

No comments:

Post a Comment