Re: Index Organized Tables

From: Riyaj Shamsudeen <>
Date: Tue, 17 Jun 2008 08:50:09 -0500
Message-ID: <>


   I designed an IOT of over 500GB few years ago. It is a compressed, range partitioned IOT with over 1600 partitions last I saw.   

 Considerations are :

  1. How is the table accessed. Is it always by primary key?
  2. What kind of DML activity goes on in that table? If there are deletes, how is it done? Any need for maintenance?
  3. If there is a need for secondary index, how long is primary key columns? Secondary index is secondary key columns + primary key columns.
  4. Any special objects?
  5. If partitioning is considered, then how easy is to add future partitions? Range or hash?

  Overall, it worked out great for me, except initially, we were running in to corruption bugs (during excessive inserts + block splits) with compressed IOT in 8i.But, bug was fixed in 9i.

Riyaj Shamsudeen
The Pythian Group    

Sanjay Mishra wrote:
> I am looking for some practical guidance on IOT (Index Organized
> Tables). Oracle has steadily enhanced them over the releases since
> they were first introduced. What is the biggest IOT you have seen?
> Should one consider to design a large fact table (> 300 GB) in a data
> warehouse application as an IOT? What are the implications /
> considerations?
> Thanks,
> Sanjay

Received on Tue Jun 17 2008 - 08:50:09 CDT

Original text of this message