Database index profile based weights for cost computation

Abstract

Database tables can have different types of database indices defined for the database tables and different numbers of database indices. The efficiency of reading the indexes can vary with the different profiles of the indexes, which impacts the costs of access plans that use the indexes. Weights can be predefined to reflect the relative efficiencies of the different characteristics. Costs can be computed in accordance with a variety of techniques (e.g., based on edge traversals). The weights can be predefined to reduce costs, increase costs, or a combination thereof. A database management application or associated application or program can also refine or revise these weights based on statistical data gathered about the operation of the database and/or heuristics that are developed based on observations/research. The corresponding weights can be adjusted accordingly.

1 . A method comprising:
 collecting profile data for each of a plurality of database indices of a database hosted across a plurality of storage devices, wherein the profile data at least indicates for each database index whether the database index is clustered or non-clustered and a type of storage device that hosts the database index;   selecting weights for the plurality of database indices based, at least in part, on the profile data;   calculating a cost for each of the plurality of database indices using the selected weights; and   associating in storage the cost for each database index with the database index.   
     
     
         2 . The method of  claim 1 , wherein said associating in storage the cost for each database index with the database index comprises storing the cost as metadata of the database index. 
     
     
         3 . The method of  claim 1 , wherein the cost for each of the plurality of database indices represents a cost of reading the database index. 
     
     
         4 . The method of  claim 1 , wherein the cost comprises at least one of number of input/output operations and units of time. 
     
     
         5 . The method of  claim 1  further comprising:
 selecting writing related weights for the plurality of database indices based, at least in part, on the profile data; 
 calculating a writing cost for each of the plurality of database indices using the selected writing related weights, wherein the writing cost for each database index represents a cost of writing the database index; and 
 associating in storage the writing cost for each database index with the database index. 
 
     
     
         6 . The method of  claim 1 , wherein the profile data also indicates whether the database index resides on an outer track or inner track of a magnetic disk for those of the plurality of database indices that reside on a magnetic disk type of storage device. 
     
     
         7 . The method of  claim 1 , wherein said selecting weights for the plurality of database indices based, at least in part, on the profile data comprises selecting from a plurality of weights that comprise a weight for an index residing on a solid state type storage device, a weight for a clustered index on an outer track of a magnetic type of storage device, a weight for a clustered indexed on an inner track of a magnetic type of storage device, a weight for a non-clustered index on an outer track of a magnetic type of storage device, and a weight for a non-clustered index on an inner track of magnetic type of storage device. 
     
     
         8 . The method of  claim 1  further comprising computing access plan costs for a plurality of access plans generated for a query responsive to receiving the query, wherein said computing the access plan costs uses the costs calculated for those of the plurality of database indices that correspond to the plurality of access plans. 
     
     
         9 . The method of  claim 8 , further comprising:
 for each of the plurality of access plans,
 determining the storage device of the plurality of storage devices indicated by the access plan; 
 computing a heat rise of the storage device expected from accessing the storage device in accordance with the access plan; and 
 incorporating the heat rise into the access plan cost of the access plan. 
   
     
     
         10 . A computer program product for database system infrastructure informed computation of access plan costs, the computer program product comprising:
 a computer readable storage medium having computer readable program code embodied therewith, the computer readable program code configured to,
 determine physical locations of a plurality of database indices in a database system, wherein the physical locations correspond to storage devices of the database system; 
 determine whether each of the plurality of database indices is clustered or non-clustered; 
 determine characteristics of the storage devices that host the plurality of indices based, at least in part, on the location of the plurality of database indices in the database system; 
 select weights for the plurality of database indices based on the characteristics and whether the database index is clustered or non-clustered; 
 compute costs of accessing the plurality of database indices with the weights; and 
 store the computed costs of the plurality of database indices to later compute access plan costs of access plans that use the plurality of database indices. 
   
     
     
         11 . The computer program product of  claim 10 , wherein the computer readable program code is further configured to:
 use a set of one or more of the computed costs in computing a plurality of access plan costs for a plurality of access plans generated for a query, wherein the set of computed costs are for those of the plurality of database indices used in the plurality of access plan costs.   
     
     
         12 . The computer program product of  claim 11 , wherein the computer readable program code is further configured to:
 for each of the plurality of access plans,
 determining the storage device of the storage devices indicated by the access plan; 
 computing a heat rise of the storage device expected from accessing the storage device in accordance with the access plan; and 
 incorporating the heat rise into the access plan cost of the access plan. 
   
     
     
         13 . The computer program product of  claim 12 , wherein the computer-readable program code is further configured to maintain an estimated current temperature of the storage device based, at least in part, on computed heat rises expected from a set of access plans pending in a queue for the storage device. 
     
     
         14 . The computer program product of  claim 10 , wherein the computer readable program code is further configured to:
 select writing related weights for the plurality of database indices based, at least in part, on the profile data;   compute a writing cost for each of the plurality of database indices using the selected writing related weights, wherein the writing cost for each of the plurality of database indices represents a cost of writing the database index; and   associating in storage the writing cost for each of the plurality of database indices with the database index.   
     
     
         15 . The computer program product of  claim 14 , wherein the characteristics of the storage devices comprise storage device type, wherein the type storage device type indicates one of solid state and magnetic. 
     
     
         16 . The computer program product of  claim 15 , wherein the computer readable program code is further configured to determine, for each of the plurality of database indices that reside on a magnetic type of storage device, whether the database index resides on an outer track or inner track. 
     
     
         17 . A computer program product for database system infrastructure informed computation of access plan costs, the computer program product comprising:
 a computer readable storage medium having computer readable program code embodied therewith, the computer readable program code configured to,
 collect profile data for each of a plurality of database indices of a database hosted across a plurality of storage devices, wherein the profile data at least indicates for each database index whether the database index is clustered or non-clustered and a type of storage device that hosts the database index; 
 select weights for the plurality of database indices based, at least in part, on the profile data; 
 calculate a cost for each of the plurality of database indices using the selected weights; and 
 associate in storage the cost for each database index with the database index. 
   
     
     
         18 . The computer program product of  claim 17 , wherein the computer readable program code is further configured to:
 select writing related weights for the plurality of database indices based, at least in part, on the profile data;   calculate a writing cost for each of the plurality of database indices using the selected writing related weights, wherein the writing cost for each database index represents a cost of writing the database index; and   associate in storage the writing cost for each database index with the database index.   
     
     
         19 . The computer program product of  claim 17 , wherein the profile data also indicates whether the database index resides on an outer track or inner track of a magnetic disk for those of the plurality of database indices that reside on a magnetic disk type of storage device. 
     
     
         20 . The computer program product of  claim 17 , wherein the computer readable program code configured to select weights for the plurality of database indices based, at least in part, on the profile data comprises the computer readable program code being configured to select from a plurality of weights that comprise a weight for an index residing on a solid state type storage device, a weight for a clustered index on an outer track of a magnetic type of storage device, a weight for a clustered indexed on an inner track of a magnetic type of storage device, a weight for a non-clustered index on an outer track of a magnetic type of storage device, and a weight for a non-clustered index on an inner track of magnetic type of storage device. 
     
     
         21 . The computer program product of  claim 17 , wherein the computer readable program code is further configured to compute access plan costs for a plurality of access plans generated for a query responsive to receiving the query, wherein the computer readable program code to compute the access plan costs comprises the computer readable program code being configured to use the costs calculated for those of the plurality of database indices that correspond to the plurality of access plans. 
     
     
         22 . The computer program product of  claim 21 , wherein the computer readable program code is further configured to:
 for each of the plurality of access plans,
 determine the storage device of the plurality of storage devices indicated by the access plan; 
 compute a heat rise of the storage device expected from accessing the storage device in accordance with the access plan; and 
 incorporate the heat rise into the access plan cost of the access plan. 
   
     
     
         23 . An apparatus comprising:
 a processor unit;   a network interface operable to receive database queries;   an index profile based cost calculator configured to:
 determine physical locations of a plurality of database indices in a database system, wherein the physical locations correspond to storage devices of the database system; 
 determine whether each of the plurality of database indices is clustered or non-clustered; 
 determine characteristics of the storage devices that host the plurality of indices based, at least in part, on the location of the plurality of database indices in the database system; 
 select weights for the plurality of database indices based on the characteristics and whether the database index is clustered or non-clustered; 
 compute access plan costs for the plurality of database indices with the weights; and 
 store the computed access plan costs of the plurality of database indices for the access plan selection by an access plan optimizer. 
   
     
     
         24 . The apparatus of  claim 23  further comprising a computer readable storage medium encoded with program instructions to implement the index profile based cost calculator. 
     
     
         25 . The apparatus of  claim 24 , wherein the computer readable storage medium also encodes program instructions to implement a database query optimizer that invokes the program instructions to implement the index profile base cost calculator.