db2 udb v8 AIX
in our oltp application we have a few dynamic tables that may contain
0 to 5000 rows. the tables have about 5-8 columns, totalling 50 bytes.
depending on the time of the month, these tables we experience either
very low (75% of the time) or very high (25% of the time) activity by
the application.
Question: other than a primary key, is there any point in creating
indexes on these dynamic tables?
arguments:
1) creating/maintaining indices is not worth the effort in this case
since the optimizer will likely ignore them anyway since there could
be a small number of rows in the table.
2) indexes should always be used since frequent/constant tablescans
might lock the entire table for the majority of the time, causing
deadlocks/rollbacks.
create the tables as 'volatile' so an index is always used.
What should I do?
all comments appreciated.
AP