US 5,822,749 A · Database system with methods for improving query performance with cache optimization strategies

Abstract

Database system and methods are described for improving execution speed of database queries (e.g., for transaction processing and for decision support) by optimizing use of buffer caches. The system includes an Optimizer for formulating an optimal strategy for a given query. More particularly, the Optimizer communicates with a Buffer Manager before it formulates the query plan. For instance, the Optimizer may query the Buffer Manager for the purpose of determining whether the object of interest (e.g., table or index to be scanned) exists in its own buffer cache (i.e., whether it has been bound to a particular named cache). If the object exists in its own cache, the Optimizer may inquire as to how much of the cache (i.e., how much memory) the object requires, together with the optimal I/O size for the cache (e.g., 16K blocks). Based on this information, the Optimizer formulates a query strategy or plan with "hints," which are ultimately passed to the Cache or Buffer Manager. By formulating "hints" for the Buffer Manager at the level of the Optimizer, knowledge of the query is, in effect, passed down to the Buffer Manager so that it may service the query using an optimal caching strategy--one based on the dynamics of the query itself. Based on the "hints" received from the Optimizer, the Buffer Manager can fine tune input/output (i.e., cache management) for the query. Specific Optimizer strategies are described for each scan method available to the system, including heap scan, clustered index, and non-clustered index access. Additional strategies are described for multi-table access during processing of join queries.

Claims25

Dependent claims

In a system comprising a database for storing and retrieving information from database tables, each database table comprising a plurality of data pages for storing data records, a method for retrieving information from database tables in response to a database query, the method comprising:

receiving a query specifying retrieval of specific information of interest from a database table, said database table having a clustered index which stores data pages at its terminal nodes;
and
retrieving the specific information of interest using a first input/output (I/O) block size and a second I/O block size for retrieving information by:
(i) navigating the clustered index for locating a storage location where said particular information of interest is stored using said first I/O block size to retrieve non-terminal nodes as the clustered index is traversed, and
(ii) after navigating to the storage location where said specific information of interest is stored, retrieving data pages storing said specific information of interest using said second I/O block size.

2 more elements

Dependencies are inferred from the claim text.

Database system with methods for improving query performance with cache optimization strategies

Life of this patent

Database system with methods for improving query performance with cache optimization strategies

Life of this patent

Abstract

Claims25