One of our large tables (12 Mil rows that continues to grow 15,000 rows/week) on AIX UDB V7.1 has two indexes - one primary clustered and other a non-clustered, non-unique index. Reorgchk cmd (F6)recommands to reorg the second index (Nonclustered, non-unique). After the reorg, reorgchk F6 recommands the reorg.

Following desribes the Index characteristics.

NLEVEL = 5, PCTFREE = 20%, INDEXPAGESIZE = 4k, ISIZE = 48, CARDS = 12Million, Two VARCHAR columns make up the index, Column counts: col1 = 207,870, col2 = 11,051,777

One thought is to increase INDEXPAGESIZE to 8k. This should reduce the NLEVEL and hopefully will help. I am not sure what effects it will have on locking. Further, this is a growing workload and this is one of the heavily hit tables. Presently, we are experiencing lock waits and deadlocks.

Any suggeston/s to alleviate this problem would be grately appreciated. If there is a limitation to the F6 formula, I wonder what reorg indicators to watch out for a timely index reorg.

Thanks.

-- Sam (SSPAAH@aol.com), May 28, 2004