Re: Index organized table

lve wrote:

> I have a table in an OLTP database which is subject to very frequent
> inserts. The table has a sequential number column which is the primary
> key of the tables. Rows are almost never deleted from this table-
> they are, of course, archived every couple of months.
>
> Is this table a good candidate for INDEX ORGANIZED TABLE?
> I am using oracle 8.1.7
>
> thanks
>
> lve
>

It depends (always true!).
Heavy insert with increasing sequence primary key will give a bad balanced indextable in the long run. That can be fixed in two ways. An IOT can be rebuild (on-the-fly) as a normal indexes can, or if you dont have or use index range scan's, build it as reverse key indextable. That will automatic balance the index, but range scans are not supported on the build-in index. And the row-density in a block might suffer at first but will 'normalize' in the long run. You can build other indexes on IOT's, but they take their maintenance toll to. There can be considerable space savings in converting normal table + PK index to IOT. That can have positive impackt on the buffer cache usage. I once converted a 800M table + 1200M index to 900M IOT. A good savings in my case, heavily random updated table (not the indexed columns - tree key columns and one value column updated). The row-length to block size ratio is also a question. Large rows is not suited for IOT's, because of the overflow mechanism. (½ block, if I remember)

To be more precise, I need table design and usage pattern, the inserts, select... against the table; type of application, users, sessions.... number of rows inserted/timeframe and, and ....

/Svend Jensen Received on Wed May 15 2002 - 14:06:05 CDT