Unique index on every column

Question

So here I am looking at this huge oracle 10g table. I looked at its indexes and see that ALL of the columns are under one unique index. Does this provide actually provide any performance benefits?

Answer 1

Possibly, possibly not. It could be that the unique index is implementing a constraint to ensure that rows are indeed unique, and is not intended to help with performance at all. There could be a performance benefit for indexed lookup queries, because they won't need to access the actual table at all.

On the face of it it sounds like this should have been created as an INDEX ORGANIZED table.

Answer 2

From a performance standpoint, I would say that having all fields in a single index (unique or not) is generally not a good idea.

• Any update to the table will result in that index being updated. In a "normal" table (the word normal used very loosely), there will be some fields not indexed. If one of those fields is updated, then it is more efficient because no indexes need to be updated.
• The described index is somewhat limited in optimization of queries. For example, suppose the table has fields a, b, c, d, and e and that the index is defined with the fields in that order. Any query that does not reference a in the WHERE clause cannot use that index.
• Depending on the number and size of fields involved, such an index could possibly have very large keys. With larger keys, it means that fewer keys can be stored in each page, and so an update to the index means that more page reads and writes will be involved.