Friday, November 27, 2015

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 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')
  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>

1 comment:

kothha jobs jobs kothha said...

Awesome article about ORACLE and i loved it..thank u,,
Latest govt Jobs