Database optimization apparatus and method

Abstract

A database optimizer collects statistics regarding applications accessing a database, and makes one or more changes to the database schema to optimize performance according to the collected statistics. In a first embodiment, the optimizer detects when a certain type of application accesses the database a percentage of time that exceeds a predefined threshold level, and if the data in the database is stored in a less-than-optimal format for the application, the data type of one or more columns in the database is changed to a more optimal format for the application. In a second embodiment, the optimizer detects when one type of application accesses a column a percentage of time that exceeds a first predefined threshold level and is less than a second predefined threshold level, and creates a new column in the database so the data is present in both formats.

1 . An apparatus comprising: 
 at least one processor;    a memory coupled to the at least one processor; and    a database optimizer residing in the memory and executed by the at least one processor, the database optimizer using statistics regarding the type of applications accessing data in a database, the frequency with which the applications access the data, and the location of the data being accessed by the applications to make at least one change to the database schema to optimize the performance of accessing data in the database.    
   
   
       2 . The apparatus of  claim 1  wherein the database optimizer makes the change to the database schema according to a set of rules that specify a preferred data type for each type of application accessing data in the database.  
   
   
       3 . The apparatus of  claim 1  wherein the change to the database schema comprises changing the data type of at least one column in the database.  
   
   
       4 . The apparatus of  claim 1  wherein the change to the database schema comprises adding a new column of a second data type to the database that contains the same data in an existing column of a first data type in the database.  
   
   
       5 . The apparatus of  claim 1  wherein the database optimizer receives requests from at least one application to access data in the database, and returns data from the database of a data type that is expected by the requesting application.  
   
   
       6 . The apparatus of  claim 1  wherein the database optimizer further comprises a run-time statistics gathering mechanism to gather the statistics.  
   
   
       7 . The apparatus of  claim 1  wherein the database optimizer operates according to customization settings set by a human user.  
   
   
       8 . The apparatus of  claim 1  wherein the database optimizer further comprises a data type conversion mechanism that converts data in a first data type retrieved from the database to a second data type that is preferred by an application requesting the data.  
   
   
       9 . A computer-implemented method for optimizing a database comprising the steps of: 
 (1) determining a preferred data type for at least one of a plurality of applications that access the database;    (2) dynamically changing a schema for the database to provide the preferred data type when at least one of the plurality of applications requests access to data in the database, wherein the step of dynamically changing the schema determines what change to make according to: 
 2A) the type of the plurality of applications accessing data in the database;  
 2B) the frequency with which the plurality of applications access the data; and  
 2C) the location of the data being accessed by the plurality of applications.  
   
   
   
       10 . The method of  claim 9  further comprising the steps of: 
 (3) determining when one of the plurality of applications accesses the database that has a different preferred data type than the data type specified in the database schema; and    (4) converting the data retrieved from the database to the different preferred data type.    
   
   
       11 . The method of  claim 9  wherein step of dynamically changing the schema for the database comprises the step of changing the data type of at least one column in the database.  
   
   
       12 . The method of  claim 9  wherein the step of dynamically changing the schema for the database comprises the step of adding a new column of a second data type to the database that contains the same data in an existing column of a first data type in the database.  
   
   
       13 . The method of  claim 9  further comprising the step of specifying a preferred data type for at least one of a plurality of applications that access the database.  
   
   
       14 . The method of  claim 9  further comprising the step of gathering the statistics.  
   
   
       15 . A computer-readable program product comprising: 
 (A) a database optimizer that uses statistics regarding the type of applications accessing data in a database, the frequency with which the applications access the data, and the location of the data being accessed by the applications to make at least one change to the database schema to optimize the performance of accessing data in the database; and    (B) recordable media bearing the database optimizer.    
   
   
       16 . The program product of  claim 15  wherein the database optimizer makes the change to the database schema according to a set of rules that specify a preferred data type for each type of application accessing data in the database.  
   
   
       17 . The program product of  claim 15  wherein the change to the database schema comprises changing the data type of at least one column in the database.  
   
   
       18 . The program product of  claim 15  wherein the change to the database schema comprises adding a new column of a second data type to the database that contains the same data in an existing column of a first data type in the database.  
   
   
       19 . The program product of  claim 18  wherein the database optimizer further comprises a data coherency mechanism for maintaining coherency between the existing column and the new column.  
   
   
       20 . The program product of  claim 15  wherein the database optimizer receives requests from at least one application to access data in the database, and returns data from the database of a data type that is expected by the requesting application.  
   
   
       21 . The program product of  claim 15  wherein the database optimizer further comprises a run-time statistics gathering mechanism to gather the statistics.  
   
   
       22 . The program product of  claim 15  wherein the database optimizer operates according to customization settings set by a human user.  
   
   
       23 . The program product of  claim 15  wherein the database optimizer further comprises a data type conversion mechanism that converts data in a first data type retrieved from the database to a second data type that is preferred by an application requesting the data.