Method and system for sample size determination for database optimizers

Abstract

A system and method for determining an adequate sample size for statistics collection is disclosed. A mechanism for automatically determining an adequate sample size for both statistics and histograms is provided. The sample size determination is accomplished via an iterative approach where the process starts with a small sample, and for each attribute which may need more data, the sample size is increased while restricting the information collected to only those attributes that require the larger sample.

1 . A method for collecting information used by an optimizer in a database system, comprising: 
 receiving a request to collect a statistic for a database object;    automatically selecting a sample size for accessing the database object;    collecting a sampled statistic using the sample size for accessing the database object; and    scaling the sampled statistic for the data object as appropriate for the sample size and type of statistic being collected.    
     
     
         2 . The method of  claim 1  in which the step of automatically selecting the sample size comprises an iterative procedure for increasing the amount of the data object until the sampled statistic is deemed acceptable.  
     
     
         3 . The method of  claim 2  in which the sampled statistic is deemed acceptable if the sampled statistic can be scaled for the entire data object.  
     
     
         4 . The method of  claim 1  in which the statistic comprises the number of rows in a database table.  
     
     
         5 . The method of  claim 4  further comprising determining if the sample size includes 2500 or more rows of data.  
     
     
         6 . The method of  claim 1  in which the statistic is elected from the group consisting of: average column length, maximum value, minimum value.  
     
     
         7 . The method of  claim 6  further comprising determining if the sample size includes at least  919  or more rows of data.  
     
     
         8 . The method of  claim 1  in which the sample size is expressed as a sampling fraction.  
     
     
         9 . The method of  claim 8  in which the sampling fraction is independently evaluated against each individual unit in the data object.  
     
     
         10 . The method of  claim 1  in which the statistic comprises a histogram.  
     
     
         11 . The method of  claim 1  in which the sample size is selected to attempt retrieval of at least 5500 units of the data object.  
     
     
         12 . A computer program product that includes a computer-usable medium comprising a sequence of instructions which, when executed by a processor, causes said processor to execute a process for collecting information used by an optimizer in a database system, said process comprising: 
 receiving a request to collect a statistic for a database object;    automatically selecting a sample size for accessing the database object;    collecting a sampled statistic using the sample size for accessing the database object; and    scaling the sampled statistic for the data object as appropriate for the sample size and type of statistic being collected.    
     
     
         13 . The computer program product of  claim 12  in which the step of automatically selecting the sample size comprises an iterative procedure for increasing the amount of the data object until the sampled statistic is deemed acceptable.  
     
     
         14 . The computer program product of  claim 13  in which the sampled statistic is deemed acceptable if the sampled statistic can be scaled for the entire data object.  
     
     
         15 . The computer program product of  claim 12  in which the statistic comprises the number of rows in a database table.  
     
     
         16 . The method of  claim 15  further comprising determining if the sample size includes 2500 or more rows of data.  
     
     
         17 . The computer program product of  claim 12  in which the statistic is elected from the group consisting of: average column length, maximum value, minimum value.  
     
     
         18 . The computer program product of  claim 17  further comprising determining if the sample size includes at least  919  or more rows of data.  
     
     
         19 . The computer program product of  claim 12  in which the sample size is expressed as a sampling fraction.  
     
     
         20 . The computer program product of  claim 19  in which the sampling fraction is independently evaluated against each individual unit in the data object.  
     
     
         21 . The computer program product of  claim 12  in which the statistic comprises a histogram.  
     
     
         22 . The computer program product of  claim 12  in which the sample size is selected to attempt retrieval of at least 5500 units of the data object.  
     
     
         23 . A system for collecting information used by an optimizer in a database system, comprising: 
 means for receiving a request to collect a statistic for a database object;    means for automatically selecting a sample size for accessing the database object;    means for collecting a sampled statistic using the sample size for accessing the database object; and    means for scaling the sampled statistic for the data object as appropriate for the sample size and type of statistic being collected.    
     
     
         24 . The system of  claim 23  in which the means for automatically selecting the sample size comprises means for an iterative procedure for increasing the amount of the data object until the sampled statistic is deemed acceptable.  
     
     
         25 . The system of  claim 24  in which the sampled statistic is deemed acceptable if the sampled statistic can be scaled for the entire data object.  
     
     
         26 . The system of  claim 23  in which the statistic comprises the number of rows in a database table.  
     
     
         27 . The method of  claim 26  further comprising determining if the sample size includes 2500 or more rows of data.  
     
     
         28 . The system of  claim 23  in which the statistic is elected from the group consisting of: average column length, maximum value, minimum value.  
     
     
         29 . The system of  claim 28 , further comprising determining the sample size is at least  919  rows or more of data.  
     
     
         30 . The system of  claim 23  in which the sample size is expressed as a sampling fraction.  
     
     
         31 . The system of  claim 30  in which the sampling fraction is independently evaluated against each individual unit in the data object.  
     
     
         32 . The system of  claim 23  in which the statistic comprises a histogram.  
     
     
         33 . The system of  claim 23  in which the sample size is selected to attempt retrieval of at least 5500 units of the data object.