Re: Index Organized Tables

  • From: Sanjay Mishra <sanjay_mishra0@xxxxxxxxx>
  • To: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
  • Date: Tue, 17 Jun 2008 09:46:31 -0700 (PDT)

Thanks Riyaj.
The table is not always accessed through the primary key. Quite often a part of 
the PK may be queried, but very rarely the entire PK.
&nbsp;
Deletes will be done using sliding window technique. Range partitioned on a 
DATE column. How does this impact the IOT decision?
&nbsp;
What are the cases when IOT is not recommended?
&nbsp;
Thanks,
Sanjay

--- On Tue, 6/17/08, Riyaj Shamsudeen &lt;riyaj.shamsudeen@xxxxxxxxx&gt; wrote:

From: Riyaj Shamsudeen &lt;riyaj.shamsudeen@xxxxxxxxx&gt;
Subject: Re: Index Organized Tables
To: sanjay_mishra0@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Date: Tuesday, June 17, 2008, 8:50 AM

Sanjay
   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.

Cheers
Riyaj Shamsudeen
The Pythian Group
   

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2008