Re: Index organized table

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Thu, 16 May 2002 22:19:54 +1000
Message-ID: <ac089g$frk$1@lust.ihug.co.nz>

"Svend Jensen" <Svend_at_OracleCare.Com> wrote in message news:3CE2B19D.2080507_at_OracleCare.Com...
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.

Define 'bad balance'. Oracle will rebalance any b-tree structure very efficiently. If you mean that an index on a monotonically incrementing sequence number is bad for concurrency (because all the index activity will be on the last [right=most] leaf node) then fair enough. And yes, an IOT built on a monotonically incrementing sequence number will suffer from the same defect.

That can be fixed in two ways. An
IOT can be rebuild (on-the-fly) as a normal indexes can

That's a 9i new feature, IIRC. And it's an 'alter table move online' command, not a rebuild one.

, or if you dont
have or use index range scan's, build it as reverse key indextable.

An interesting suggestion. Shame it can't actually be done. "REVERSE" is an attribute of the 'create index' command, not the 'create table' statement. Therefore, it's impossible to create an IOT as a reverse key b-tree structure. If you have the syntax, I'd love to see it. (I'm assuming that the term 'indextable' refers to an IOT).

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)

By default, yes, it's half. But the "PCTTHRESHOLD" keyword was invented precisely so you can specify your own overflow limits.

Regards
HJR

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 Thu May 16 2002 - 07:19:54 CDT