Re: 2 overlapping indexes

If you declare a unique constraint on A,B,C, then you will be creating an index implicitly.

The best way, in my opinion, to overcome this problem, is to use an index-organized table, but only in 8i and above (as IOTs in 8.0 do not support secondary indexes).

Consider that you have a heap organized ("normal") table. You will store:-

table segment - A,B,C,D,E
unique index segment - A,B,C
2nd index segment - A,B,C,D

Now that is a LOT of duplicated storage.

So in creating the table as an IOT, you must first include a primary key - this takes care of A,B,C uniqueness. You can then add A,B,C,D index over the top. You are now storing:-

IOT segment - A,B,C,D,E with "built-in" PK 2nd index segment - A,B,C,D

Now, personally I would find it difficult to convince myself that a second index was needed. The data for column D would be stored inline with the PK, so if you access the table by its primary key (A,B,C), then you have already reduced your search down to one row, so you can pick off the value for D without the need for that second index. You are now storing:-

IOT segment - A,B,C,D,E with "built-in" PK

Don't be shy of using IOTs - I use them all the time, including partitioned IOTs and I think they are excellent for "slim" tables.

Hope this helps.

Adrian Received on Fri Jan 25 2002 - 02:33:25 CST