NLEVELS=3? Or 4? Or 5?

NLEVELS is the number of levels within an index's hierarchy, with the bottom level referring to the data page for each row. DB2 does a binary search through the index levels to find the address of the data rows needed. The fewer index levels, the less searching needs to be done, so the better performance is, right? I've certainly read many articles over the years which say exactly this.

Not so fast...

I benchmarked partitioning a table with 30 million rows and NLEVELS=4 to get it to NLEVELS=3. I had to use 96 partitions to allow room for growth and still maintain NLEVELS=3. Under perfect conditions, the cputime increased by nearly 9% when I partitioned it. Apparently, searching index pages is more efficient than searching the table of partition ranges DB2 builds internally from SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART.

If a table has marginally increased from one NLEVEL to another, then adding one or two partitions is likely a good idea. Other than that, don't try to tune NLEVELS. Reducing it causes overhead in other areas which can more than use up any performance gain you get from reducing NLEVELS.

~~~~~