‘Pause and resume’ functionality for index operations

Online index functionality allows concurrent queries and updates during index maintenance tasks, e.g., creation of a new secondary index. Nonetheless, index operations can be both extremely resource-intensive and also extremely long-running. As such, necessary system activities such as adding a new secondary index or changing the primary index may introduce significant contention for resources such as CPUs, memory, and space and bandwidth in temporary storage, thus starving the primary database workload. Danger of an additional workload at inopportune times inhibits adoption of automatic index tuning. Whether initiated by a database administrator or by a soft ware component, ´pause and resume´ is a step towards self managing database systems. However, realizing even this step in a commercial system is more difficult than many researchers may realize. This paper summarizes a design for ´pause and resume´ functionality and explores the many issues that must be addressed in order to implement such designs in a commercial system. The design is deliberately kept general such that it applies to many software packages. One unique contribution is that in addition to the basic mechanisms, this paper also enumerates functional requirements and covers interaction with other functions commonly used in database management systems, server shutdown and restart, tables with multiple indexes, concurrent queries and updates, failures in the index builder or in concur rent transactions, log volume, and performance expectations.