A Simple Example of an Index Organised Table Without Overflow

For a long time I have had a note on my task list to learn about index organized tables. I never got round to doing it because I thought I would never see one. However, I came across several in a 3^rd party application recently. An index organized table is a kind of index and table combined. You can see how they work in the example below, which I tested in an Oracle 11.2 database:

 

First I created a sequence. You don’t need a sequence to create an index organized table. I just used it to ensure that the index always contained unique values:

SQL> create sequence seq1

  2  /

 

Sequence created.

SQL>

I read that an index organized table cannot contain a LONG column. When I tried to do this, Oracle returned an ORA-02160:

SQL> create table iot1

  2  (owner varchar2(30),

  3   object_name varchar2(30),

  4   seq_no number,

  5   column_not_allowed long,

  6   constraint iot1_pk

  7   primary key (owner, object_name, seq_no))

  8  organization index

  9  /

organization index

             *

ERROR at line 8:

ORA-02160: index-organized table can not contain

columns of type LONG

 

SQL>

Without the LONG column, the index organized table was created successfully. This was an index organized table without overflow. I will try to look at overflow in a future post:

SQL> create table iot1

  2  (owner varchar2(30),

  3   object_name varchar2(30),

  4   seq_no number,

  5   constraint iot1_pk

  6   primary key (owner, object_name, seq_no))

  7  organization index

  8  /

 

Table created.

 

SQL>

After creating the index organized table, it had an IOT_TYPE of IOT. The IOT_NAME column was empty as the index organized table did not have overflow:

SQL> select iot_type, nvl(iot_name,'NULL')

  2  from user_tables

  3  where table_name = 'IOT1'

  4  /

 

IOT_TYPE             NVL(IOT_NAME,'NULL')

-------------------- ------------------------------

IOT                  NULL

SQL>

I added data to the index organized table like this:

SQL> begin

  2   for i in 1..15 loop

  3    insert into iot1

  4    select owner, object_name, seq1.nextval

  5    from dba_objects;

  6   end loop;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL> select count(*) from iot1

  2  /

 

  COUNT(*)

----------

    996060

SQL>

The index organized table had an entry in DBA_TABLES:

SQL> select count(*) from dba_tables

  2  where table_name = 'IOT1'

  3  /

 

  COUNT(*)

----------

         1

SQL>

... but it did not appear in DBA_SEGMENTS:

SQL> select count(*) from dba_segments

  2  where segment_name = 'IOT1'

  3  /

 

  COUNT(*)

----------

         0

SQL>

... and its TABLESPACE_NAME entry in DBA_TABLES was empty: 

SQL> select nvl(tablespace_name,'NULL')

  2  from dba_tables

  3  where table_name = 'IOT1'

  4  /

 

NVL(TABLESPACE_NAME,'NULL')

------------------------------

NULL

SQL>

The index associated with the index organized table had an entry in DBA_SEGMENTS so I checked how big it was:

SQL> select bytes from dba_segments

  2  where segment_name = 'IOT1_PK'

  3  /

 

     BYTES

----------

  83886080

SQL>

Then I deleted several rows from the index organized table:

SQL> delete from iot1

  2  where owner = 'SYS'

  3  /

 

460995 rows deleted.

SQL>

... and checked that this deleted some leaf rows from the index:

SQL> analyze index iot1_pk validate structure

  2  /

 

Index analyzed.

 

SQL> select name, lf_rows, del_lf_rows

  2  from index_stats

  3  /

 

NAME          LF_ROWS DEL_LF_ROWS

---------- ---------- -----------

IOT1_PK        848331      313266

 

SQL>

When this happens, you can usually just rebuild the index but when I tried to do this to the index for the index organized table, Oracle returned an ORA-28650:

SQL> alter index iot1_pk rebuild

  2  /

alter index iot1_pk rebuild

*

ERROR at line 1:

ORA-28650: Primary index on an IOT cannot be rebuilt

 

SQL>

So I moved the table instead:

SQL> alter table iot1 move online

  2  /

 

Table altered.

SQL>

... then I analyzed the index again:

SQL> analyze index iot1_pk validate structure

  2  /

 

Index analyzed.

SQL>

... and saw that the deleted leaf rows had disappeared:

 

SQL> select name, lf_rows, del_lf_rows

  2  from index_stats

  3  /

 

NAME          LF_ROWS DEL_LF_ROWS

---------- ---------- -----------

IOT1_PK        535065           0

SQL>

... and the index was much smaller:

SQL> select bytes from dba_segments

  2  where segment_name = 'IOT1_PK'

  3  /

 

     BYTES

----------

  25165824

SQL>