Overflow and Performance
Overflow chains slow concurrent performance. Overflow pages are attached to the main data page if a record must be added to a full main page. The query that touches one main data page must now touch that page plus each associated overflow page. This increases I/O, cause concurrency problems, and uses up locking system resources.
Here are suggestions:
• Monitor overflow chains.
Check the number of overflow pages for your tables and secondary indexes. In Actian Director, right-click a table or secondary index, and then select Properties. In VDBA, select a table or secondary index in the Database Object Manager window, and click the Pages tab. Use the legend to interpret the information displayed.
If the number of overflow pages is greater than 10-15% of the number of data pages, expect performance degradation.
• Check for duplicate keys. Overflow problems are often caused by them.
• Consider trying a different storage structure. Some table structures create long overflow chains when much new data is added. For details, see
Storage Structure and Overflow.
• Decrease overflow
Here are ways to decrease overflow and improve concurrency:
– Use unique keys.
– Modify the table to reorganize it; with a B-tree structure, simply specify the Shrink B-tree Index option.
– Consider tailoring the table’s fill factor.
For additional information, see the sections on overflow and fill factor in the chapters “Choosing Storage Structures and Secondary Indexes” and “Maintaining Storage Structures.”
Storage Structure and Overflow
Here are overflow considerations for each storage structure:
• Heap-Heap tables are created as one main page with an overflow chain. There is no overflow management.
• Hash-Overflow pages occur in a newly modified table if the key is repetitive; this is normal but undesirable. Check a freshly modified table. If there is overflow, consider using ISAM instead.
• ISAM-ISAM has a fixed index that can cause long overflow chains. Modify frequently or use B-tree for a non-static table. Use heap structure for large bulk updates and modify back to ISAM to avoid update performance problems.
• B-tree-No overflow if there are no duplicate keys, so consider making keys unique. Overflow occurs only at the leaf level and only when 2K pages are used. Use the Shrink B-tree Index option to reorganize it. Use heap structure for bulk loads, modify to B-tree.
Last modified date: 08/29/2024