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 3rd
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')
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>
SQL>
