or should I say "indices"?

Anyway, do you think there would be a noticeable difference in performance of an INSERT statement against a table with 5 indices if 4 of those indices are unique or non-unique?

We have a table in a mixed OLTP/OLAP database (db2 v 8 fp 5 on Win2003) with about 150 million rows and significant number of inserts (say, few hundred thousand per day). In two of those indices cardinality is quite low: on the order of 30K to 100K.

I'm thinking about ways to improve insert performance without sacrificing query execution times and was wondering whether it would be worth trying to make indices unique? Of course, testing would be the best answer but it takes about 6 hours to execute RUNSTATS on that table so I would like to begin with theoretical discussion.

Thanks.

check the size and dept of both indexes. I think that non-unique indexes occupies less space, one reason is that nulls are not part of the index and more rows are in the leaf pages less I/O.

regards,

mujeeb

Unique and non-unique indexes are fundamentally the same, though, they're both b-trees. I guess it would help the optimizer to know that it can only ever find a single matching value, other than that I don't see where there'd be any benefit?

I have never heard that non-unique indexes in DB2 occupy less space. I have heard that about some other databases though, but I am not sure that DB2 works that way unless it was changed from the original design (but my knowledge of internals is a bit dated).

The problem with inserting in non-unique indexes is that DB2 has to find the end of the chain for that value to insert the next index row. If the cardinality is low (lots of the same index values) DB2 has to scan all the index entries for that value to find the end. Again, that may have been changed in recent releases (if IBM changed the internals of how indexes work), but I know that historically has been a problem.

You don't necessarily need to get the index cardinality unique, but getting it down to below 200 does help. Also, making sure your percent free is set up in an optimal manner is important to prevent index page splits (which are also very expensive if done frequently).

Yes, that's what I was thinking, too.

Thanks for your thoughts. I'll run some experiments and share the results (if any).