Re: Why Index Organized Tables not suituable for OLTP

vasarpota_at_my-deja.com wrote:
>
> 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.

Just to add...

IOT's can be valuable in OLTP for your static tables - OLTP apps commonly read from lots of small reference tables for validation purposes. IOT-ing these tables can improve the lookup times...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse