Re: Index Organized Tables

In your case, for deletes, you are in better position since you can partition the table along date column and drop older partitions. I was trying to point out the rare case of deletes creating holes in the index structure, easily repaired with coalesce, but need to factor that in the design.

I would say that following are guide lines not to choose IOTs. These are guidelines, not design criterias.

1. if the access to the table is not by leading columns of the primary key ( in most cases )
2. if there are many secondary indices and if the length of primary key column is higher.
3. if there are too many columns in the table structure or row length is high compared to block size.

IOT is useful to reduce access cost by avoiding table block lookup. It also reduces storage cost, if there are many indices with nearly same leading columns, since we can remove those indices without much performance penalty. If these two primary factors are not appealing, then IOTs are not very appealing ( at least to me).

Cheers
Riyaj Shamsudeen
The Pythian Group www.pythian.com
orainternals.wordpress.com

> 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.
>
>
>
> 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?
>
>
>
> Thanks,
>
> Sanjay
>
> --- 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
> To: sanjay_mishra0_at_yahoo.com
> Cc: oracle-l_at_freelists.org
> 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:
> >
> > 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
> >
> >
> >
> >
> >
> >
>
>

--
http://www.freelists.org/webpage/oracle-l