Re: What are the advantages of an IOT?

Harry Wild <h.wild_at_elegene.de> wrote:
>Hi!
>
>Can someone tell me the advantages of an index-organized table over the
>standard one?
>Is it faster? I have a table with some 100.000 records (small ones), some
>10.000 new entries per week, and once inserted records don't change. Is it
>better to make an IOT of this table?

They're faster if you have fairly small rows, that are usually accessed via the PK. You only have a single I/O (ignoring branch-blocks) to retrieve the data rather than looking up the index, then getting the row.

They're also useful for getting the same physical locality advantages that index clusters give.

Many updates/deletes will fragment the table, like any B*Tree index.

See Chapter 14 of Jonathan Lewis' book for an excellent, and much more detailed description. As he says at the end of the chapter, test *everything* before committing to using an IOT.

>BTW, I'm using Oracle 8.1.7

That'll allow you secondary indexes, and reduce a number of bugs.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/