Re: Index Organized Tables
Date: Tue, 17 Jun 2008 09:46:31 -0700 (PDT)
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.
Deletes will be done using sliding window technique. Range partitioned on a DATE column. How does this impact the IOT decision?
What are the cases when IOT is not recommended?
- On Tue, 6/17/08, Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com> wrote:
From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Subject: Re: Index Organized Tables
Date: Tuesday, June 17, 2008, 8:50 AM
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 :
- How is the table accessed. Is it always by primary key?
- What kind of DML activity goes on in that table? If there are deletes, how is it done? Any need for maintenance?
- If there is a need for secondary index, how long is primary key columns? Secondary index is secondary key columns + primary key columns.
- Any special objects?
- 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.
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 /