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.