Reduce disk space in tables with variable length rows

You can enable the database server to insert more rows per page into tables with variable-length rows, if you set the MAX_FILL_DATA_PAGES configuration parameter to 1. Allowing more variable length rows per page has advantages and disadvantages.

Potential advantages of allowing more variable length rows per page are:

• Reducing the disk space required to store data
• Enabling the server to use the buffer pool more efficiently
• Reducing table scan times

Possible disadvantages of using the MAX_FILL_DATA_PAGES allowing more variable length rows per page are:

• The server might store rows in a different physical order.
• As the page fills, updates made to the variable-length columns in a row could cause the row to expand so it no longer completely fits on the page. This causes the server to split the row onto two pages, increasing the access time for the row.

If the MAX_FILL_DATA_PAGES configuration parameter is enabled, the server will add a new row to a recently modified page with existing rows if adding the row leaves at least 10 percent of the page free for future expansion of all the rows in the page. If the MAX_FILL_DATA_PAGES configuration parameter is not enabled, the server will add the row only if there is sufficient room on the page to allow the new row to grow to its maximum length.

If you enable the MAX_FILL_DATA_PAGES configuration parameter and you want this to affect existing variable length rows, the existing tables must be reloaded.