Database optimization apparatus and method

An apparatus comprising:

• at least one processor;
• a memory coupled to the at least one processor;
• a database residing in the memory;
• a database optimizer residing in the memory and executed by the at least one processor, the database optimizer comprising:
• a data access mechanism 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;
• customization settings that may be set by a human user to determine the function of the database optimizer;
• a data coherency mechanism that maintains coherency of reflective columns in the database that are created by the data access mechanism and that contain the same data in different data types;
• and
• 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 a requesting application.

An apparatus comprising:

• at least one processor;
• a memory coupled to the at least one processor;
• a database residing in the memory;
• a database optimizer residing in the memory and executed by the at least one processor, the database optimizer comprising:
• a mechanism that reads statistics regarding the type of applications accessing data in the database, the frequency with which the applications access the data, and the columns being accessed by the applications;
• if the statistics indicate that a selected type of application has a number of accesses to a selected column of a first data type in the database that exceeds a first threshold level, the database optimizer determines whether the statistics indicate that the selected type of application has a number of accesses to the selected column that exceeds a second threshold level, and if so, the database optimizer changes the data type of the selected column in the database;
• if the statistics indicate that a selected type of application has a number of accesses to a selected column of a first data type in the database that exceeds a first threshold level, the data optimizer determines whether the statistics indicate that the selected type of application has a number of accesses to the selected column that exceeds a second threshold level, and if not, the data optimizer adds a new column of a second data type to the database that contains the same data in the selected column, the selected column and the new column being defined as reflective columns because they contain the same data in different data types;
• wherein the data optimizer detects when one of the plurality of applications requests access to data in the selected column, determines the preferred data type for the requesting application, determines if the data in the selected column is of the preferred data type for the requesting application, and if the data in the selected column is of the preferred data type for the requesting application, returning the data in the selected column to the requesting application;
• if the data in any column reflective of the selected column is of the preferred data type for the requesting application, the database optimizer returns the data from the reflective column to the requesting application;
• if the data in the selected column and in all reflective columns, if any, is not of the preferred data type for the requesting application, the database optimizer converts the data to the preferred data type for the requesting application, and returns the converted data to the requesting application.

A method for reading data from a database comprising the steps of:

• (1) specifying a preferred data type for at least one of a plurality of applications that access the database;
• (2) detecting when one of the plurality of applications requests access to data in the database;
• (3) determining the preferred data type for the requesting application;
• (4) determining if the data is stored in the database in the preferred data type for the requesting application;
• (5) if the data is stored in the database in the preferred data type for the requesting application, returning the data to the requesting application;
• (6) if the data is not stored in the database in the preferred data type for the requesting application, performing the steps of:
• (6A) converting the data to the preferred data type for the requesting application;
• and
• (6B) returning the converted data to the requesting application;
• (7) reading statistics regarding the type of applications accessing data in the database, the frequency with which the applications access the data, and the location of the data being accessed by the applications;
• and
• (8) 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.

A method for optimizing accesses to a database comprising the steps of:

• (1) reading statistics regarding the type of applications accessing data in the database, the frequency with which the applications access the data, and the columns being accessed by the applications;
• (2) if the statistics indicate that a selected type of application has a number of accesses to a selected column of a first data type in the database that exceeds a first threshold level, performing the steps of:
• (2A) if the statistics indicate that the selected type of application has a number of accesses to the selected column that exceeds a second threshold level, changing the data type of the selected column in the database;
• (2B) if the statistics indicate that the selected type of application has a number of accesses to the selected column that does not exceed a second threshold level, adding a new column of a second data type to the database that contains the same data in the selected column, the selected column and the new column being defined as reflective columns because they contain the same data in different data types.

A program product comprising:

• (A) a database optimizer comprising:
• a data access mechanism 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;
• customization settings that may be set by a human user to determine the function of the database optimizer;
• a data coherency mechanism that maintains coherency of reflective columns in the database that are created by the data access mechanism and that contain the same data in different data types;
• and
• 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 the requesting application;
• and
• (B) computer-readable signal bearing media bearing the database optimizer.

A program product comprising:

• (A) a database optimizer comprising:
• a mechanism that reads statistics regarding the type of applications accessing data in the database, the frequency with which the applications access the data, and the columns being accessed by the applications;
• if the statistics indicate that a selected type of application has a number of accesses to a selected column of a first data type in the database that exceeds a first threshold level, the database optimizer determines whether the statistics indicate that the selected type of application has a number of accesses to the selected column that exceeds a second threshold level, and if so, the database optimizer changes the data type of the selected column in the database;
• if the statistics indicate that a selected type of application has a number of accesses to a selected column of a first data type in the database that exceeds a first threshold level, the data optimizer determines whether the statistics indicate that the selected type of application has a number of accesses to the selected column that exceeds a second threshold level, and if not, the data optimizer adds a new column of a second data type to the database that contains the same data in the selected column, the selected column and the new column being defined as reflective columns because they contain the same data in different data types;
• wherein the data optimizer detects when one of the plurality of applications requests access to data in the selected column, determines the preferred data type for the requesting application, determines if the data in the selected column is of the preferred data type for the requesting application, and if the data in the selected column is of the preferred data type for the requesting application, returning the data in the selected column to the requesting application;
• if the data in any column reflective of the selected column is of the preferred data type for the requesting application, the database optimizer returns the data from the reflective column to the requesting application;
• if the data in the selected column and in all reflective columns, if any, is not of the preferred data type for the requesting application, the database optimizer converts the data to the preferred data type for the requesting application, and returns the converted data to the requesting application;
• and
• (B) computer-readable signal bearing media bearing the database optimizer.