Databases Reference
In-Depth Information
Index-organized tables
The characteristic feature of an index-organized table is the way in which it stores the
data. Instead of storing the data where it its, like with a heap table, the data is stored
in a structure similar to a b-tree index. This means that not only the key is stored in
the index structure, but also the other data resides there as well. Because you traverse
the index structure and immediately ind the data that you are looking for, this type
of table is very eficient for lookup purposes.
Because the data needs to be stored alongside the key, it can only be stored at a
certain location. Hence, performing the
INSERT
or
UPDATE
operations on data in
index-organized tables is not as eficient as storing data in a heap table. In the case
of lookup tables, which are being discussed here, data changes slowly and is created
once but queried over and over again, making the DML operations less prone to
an issue. Unlike the single-table hash cluster, where you need to know the size of
the table in advance and allocate this space as well, this is not necessary with an
index-organized table.
To compare an index-organized table to a regular heap table, an index-organized
table that resembles the previously created heap table is created.
SQL> create table lookup_io_t
2 (id primary key, description)
3 organization index
4 as
5 select rownum id
6 , object_name description
7 from (select * from dba_objects
8 union all
9 select * from dba_objects
10 )
11 where rownum <= 50000
12 /
Table created.
An index-organized table needs to have a primary key. The primary key is deined
on line 2 of the preceding code sample. To indicate that an index-organized table is
needed, include
organization index
on line 3.
Just like the single-table hash cluster and the heap table were populated with 50,000
records, the index-organized table is also populated with 50,000 records with the
same structure.
