Re: Why Index Organized Tables not suituable for OLTP

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.