Increasing buffer-locality for multiple index based scans through intelligent placement and index scan speed control

Abstract

Decision support systems are characterized by large concurrent scan operations. A significant percentage of these scans are executed as index based scans of the data. This is especially true when the data is physically clustered on the index columns using the various clustering schemes employed by database engines. Common database management systems have only limited ability to reuse buffer content across multiple running queries due to their treatment of queries in isolation. Previous attempts to coordinate scans for better buffer reuse were limited to table scans only. Attempts for index based scan sharing were non existent or were less than satisfactory due to drifting between scans. In this paper, we describe a mechanism to keep scans using the same index closer together on scan position during scanning. This is achieved via intelligent placement of index scans at scan start time based on their scan ranges and speeds. This is then augmented by adaptive throttling of scan speeds based on the index scans' runtime behavior during scan execution. We discuss the challenges in doing it for index scans in comparison to the more common table scan sharing. We show that this can be done with minimal changes to an existing database management system as demonstrated in our DB2 UDB prototype. Our experiments show significant gains in end-to-end response times and disk I/O for TPC-H workloads.