Routine Database Maintenance Tips
To keep your tables in good condition, we recommend that you run the following maintenance tools periodically:
• Modify database tables periodically if they are subject to frequent updates or inserts. Frequent updates and inserts to all table structures except B-tree cause overflow data pages to be created, which are inefficiently searched.
Note: B-tree tables with 2K pages can develop overflow from leaf pages with highly duplicate keys; B-trees with larger pages cannot develop overflow.
If you do not have enough disk space to modify a large B-tree table, modify the table to shrink the B-tree index. This improves the structure of the B-tree index pages, but does not require the amount of free disk space required by other modify options.
For details on how to modify tables, see the chapter “Maintaining Storage Structures.”
Note: Choosing the correct storage structure for your needs makes maintaining the database easier. For a discussion of the four main storage structures, see the chapter “Choosing Storage Structures and Secondary Indexes.” If the storage structure you are using is not the best one, modify it using the information in the chapter “Maintaining Storage Structures.”
• Run system modification on the database if the database is active (that is, users frequently create or modify tables, views, or other database objects). Both system catalog data page overflow and locking contention is reduced by regular use of system modification. For details, see Example: Before and After Optimization in the chapter “Using the Query Optimizer.”
• Use optimization to help maintain databases. When you optimize a database, data distribution statistics are collected that help queries run more quickly and use fewer system resources. We recommend that you optimize your database when its data distribution patterns change.
Optimization cannot be run on all columns of all tables in your database. Instead, run it only on those columns that are commonly referenced in the WHERE clauses of queries. Collecting more statistics than you need consumes extra disk space and requires the query optimizer to consume more system resources to arrive at an appropriate query execution plan.
For details on optimization, see Database Statistics in the chapter “Using the Query Optimizer.”
Note: You can set up these routine maintenance tasks to be done inside maintenance batch jobs to avoid the need to run them interactively.