Re: Index organized table

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sun, 15 Jun 2008 11:40:52 +0200
Message-ID: <6bk6d5F3cinplU1@mid.individual.net>


On 15.06.2008 09:49, buu wrote:
> I'm thinking about some changes in my DB app. to switch one table to an IOT.
> That table has pri. key wich is always stored in sequence order (in
> inserting new row).
> Currently, that table is 240Mb big (with more than 2 mil. rows) with an
> average row size of 100 bytes.

Unless you are using some ancient hardware I would not bother about data that size at all unless you are expecting this to grow to gigabytes shortly.

> There is an pri. key index and a single B-tree index.
> Size of an pri. key index at the moment is 110Mb.

Can you be more precise, i.e. post DDL of table and index along with Oracle version? With the information you have presented so far it is impossible to come up with a definitive answer.

> Is it reccomended to use IOT in my case?
> How would it affect insert time for a single row?

If you have an IOT with an additional index it may be that you actually spent more space because the secondary index needs to store the PK to identify rows. Depending on the size (# of columns as well as types) the IOT solution might actually take more space especially if you add more indexes.

Note also that IOT's make some administrative tasks harder. For example, you cannot just drop the PK constraint as easily as you can do with regular tables.

Kind regards

        robert Received on Sun Jun 15 2008 - 04:40:52 CDT

Original text of this message