Designing tables to reduce index rebuilding time

 

Table design can also help reduce index recovery time.

You can divide a large master table into a history table and a transaction table. The transaction table is then used for adding new data and the history table is used for inquiry only. Each day, you can merge the transaction data into the history table and then clear the transaction file for the next day's data. With this design, the time to rebuild indexes can be shortened, because if the system abnormally ends during the day, the index to the smaller transaction table might need to be rebuilt. However, because the index to the large history table is read-only for most of the day, it might not be out of synchronization with its data, and might not have to be rebuilt.

Consider the trade-off between using a table design to reduce index rebuilding time and using system-supplied functions like access path journaling. The table design described in the previous paragraph might require a more complex application design. After evaluating your situation, you can decide whether to use system-supplied functions like access path journaling rather than design more complex applications.

 

Parent topic:

Journal management for distributed relational databases