What are IOT indexes and what are all the disadvantages for this?

sakthikumar
sakthikumar used Ask the Experts™
on
What are IOT indexes and what are all the disadvantages for this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
IOT are Tables organized as Indexes

The advantage is it lets you organize a table in the order most commonly accessed, and lets you combine storage for a heap table + primary key.

A typical heap table in Oracle has no ordering. Then you create a primary key index which is stored in a 2nd segment. So you have to segments of data now. Also you have redundant storage of the indexed columns. So when querying by the primary key, Oracle has to first access the index, then the table in most cases.

An IOT gets rid of the redundancy, and also orders the rows in the actual table, because it is an index, with the additional difference that the IOT also has the full data for each row stored in it, not just the keys.
Top Expert 2009

Commented:
The disadvantage would be only in the case of access of the IOT by a key other than the primary key, that requires a secondary index, but the difference is, on an IOT, secondary indexes use logical rowids, not physical, so there is an extra layer of abstraction when accessing the rows.

There are some other minor constraints as well, in the docs, but most are special case issues.

In general, IOTs work very well for tables where most of the access is either unsorted scans, scans sorted by key, or random access by primary key.

Commented:
I raised similar question few months ago, it might help you

http://www.experts-exchange.com/Database/Oracle/10.x/Q_26636850.html

Commented:
You can always create a table, then an index of all its columns.  This way it is easier to drop and rebuild indexes if you need to.
Top Expert 2009
Commented:
>>You can always create a table, then an index of all its columns.  This way it is easier to drop and rebuild indexes if you need to.

Of course you can, and we would not even be discussing an IOT. The fact that you have an index with all columns is a good indicator that an IOT might be appropriate.

The whole purpose of an IOT are the special cases:
   1) Most of my selects are by the primary key or part of the primary key
   2) My inserts are in order, not random

Since IOTs (and indexes) slow down inserts, you just have to look at the case.

Do you insert a lot? Are inserts a huge amount of your queries? If so, IOT might not be best. Random inserts are slower on IOTs (and indexes). But keep in mind, if your table doesn't really hold much data besides the key itself, an IOT isn't going to hurt you much in this case, because the primary key index on a heap table also has the same property.

Do you query a lot by other keys? If so, an IOT might not be best. Secondary indexes on IOTs use logical rowids, slower than physical rowids.

Identify a true need for an IOT, rather than trying to find nails to hit with the IOT hammer. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial