For btree tables, the LEAFFILL parameter specifies how full to fill the leaf index pages. Leaf index pages are the index pages that are directly above the data pages. NONLEAFFILL specifies how full to fill the non-leaf index pages; non-leaf index pages are the pages above the leaf pages. Specify leaffill and nonleaffill as percentages. For example, if you modify a table to btree, specifying NONLEAFFILL=75, each non-leaf index page is 75% full when the modification is complete.

The LEAFFILLand NONLEAFFILL parameters can assist with controlling locking contention in btree index pages. If some open space is retained on these pages, concurrent users can access the btree with less likelihood of contention while their queries descend the index tree. Strike a balance between preserving space in index pages and creating a greater number of index pages. More levels of index pages require more I/O to locate a data row.

By default, modify to storage-structure resets these attributes back to their defaults.

Default: LEAFFILL=70; NONLEAFFILL=80

The MODIFY TO RECONSTRUCT operation does not affect these attributes.