Re: Why Index Organized Tables not suituable for OLTP

Thank you very much for an excellent explanation.

In article <8lsnjn$et$1_at_nnrp1.deja.com>,   oratune_at_aol.com wrote:
> In article <8lsf8v$pfl$1_at_nnrp1.deja.com>,
> vasarpota_at_my-deja.com wrote:
> > I know index organized tables are great for DSS applications. They
> > save space since index and table are one. They also improve query
> > performance. But why are they not suited for DSS applications now
 that
> > they can have multiple indexes on IOT? I keep on reading this but
 do
> > not understand why?
> >
> > Thanks for your help
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> The very nature of an index-organized table does not lend itself to
 OLTP
> applications; unlike tables, indexes do not reuse leaf blocks until
 the
> entire leaf block is cleared, which can leave blocks sparsely
 populated
> when records are deleted. An IOT is stored as a B*tree stucture, the
> same structure used by a regular index, and is subject to the block
> management restrictions common to indexes. OLTP applications do not
> merely access data, they insert, modify and delete data. The main
> problem with IOT's and OLTP is the result of data manipulation, i.e.,
> having few records in a large block due to deletions from the table.
> The IOT will tend to grow with the inserts and will not shrink with
 the
> deletes; when a leaf block is clear it will be reused but this will
> leave large numbers of data blocks with very few rows in them in
> high-volume OLTP applications requiring the IOT to be rebuilt on a
> fairly regular basis. DSS is fairly static in terms of inserts,
 updates
> and deletes (inserts/updates performed during batch operations,
 deletes
> are usually done in bulk. also during batch operations and are far
 fewer
> in number relative to the inserts/updates). An IOT is quite
 desirable
> for a DSS application that is only querying the data and not
 performing
> any DML; it is quite undesirable for OLTP because the daily, hourly or
> minutely transactions can skew the IOT structure, make the table large
> and sparsely populated and turn a once efficient object into a
 database
> nightmare. It's not the fact that additional indexes can now be
 created
> on an IOT, it's the structure of the IOT itself that precludes its use
> in an OLTP environment.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jul 28 2000 - 00:00:00 CDT