METHOD AND APPARATUS FOR OPTIMIZING DATA RETRIEVAL USING INDEX SCANNING,申请号CA19942124094-传众专利搜索

METHOD AND APPARATUS FOR OPTIMIZING DATA RETRIEVAL USING INDEX SCANNING

申请公布号：CA2124094(C)

申请号：CA19942124094

申请日期：1994.05.20

申请公布日期：1999.07.20

发明人：SCHIEFER, BERNHARD;SWAMI, ARUN N.

分类号：G06F17/30;(IPC1-7):G06F17/30

主分类号：G06F17/30

摘要：A method is disclosed for operating a data processing system having a processing unit and physical storage in which data is stored and in which the storage is operably connected to the processing unit for transferring pages of data to the processing unit. The storage contains at least one data base table stored on a plurality of pages, each data base table having a plurality of rows of data, and one or more indexes composed of a sequence of entries which reference the rows. The processing unit has a memory associated with it, a portion of the memory comprising a buffer pool for caching data pages from the table. The method comprises the following steps. Scanning one or more selected indexes and determining the relationship of the number of data transfers required to traverse all or a portion of each selected index for a selected range of buffer pool sizes; and storing a description of the relationship. For a query submitted by a data base user, determining the number of page transfers required to scan a whole table of interest, and for each index of the table relevant to the query, determining the number of page transfers required to scan each the relevant index in accordance with the size of buffer pool made available for the query, as determined from the relationship, scaling down the number of page transfers required in proportion to the selectivity of any starting and stopping conditions present in search criteria in the query, applying a suitable correction factor to the number of page transfers if any of the following conditions are found, very few rows of data remain to be transferred after applying the search criteria, the buffer pool to table size ratio is large, or, an index being used has a low degree of clustering, and accounting for the effect on the number of page transfers by any remaining index sargable search criteria, comparing the number of page transfers required for scanning of the relevant indexes and for scanning the entire table, and selecting a search procedure from the results of said comparison that results in the least number of page transfers.