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.

Description

CROSS-REFERENCE TO RELATED APPLICATION [0001] This patent application is a continuation of “Database Optimization Apparatus and Method”, Ser. No. 10 / 075,651 filed on Feb. 14, 2002, which is incorporated herein by reference.BACKGROUND OF THE INVENTION [0002] 1. Field of the Invention [0003] This invention generally relates to computer systems, and more specifically relates to apparatus and methods for accessing data in a computer database. [0004] 2. Background Art [0005] Since the dawn of the computer age, computers have evolved and become more and more powerful. In our present day, computers have become indispensable in many fields of human endeavor including engineering design, machine and process control, information storage and retrieval, and office computing. One of the primary uses of computers is for information storage and retrieval. [0006] Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search f...

AI Technical Summary

Benefits of technology

[0009] According to the preferred embodiments, a database optimizer collects statistics regarding which types of applications are accessing the 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. This means that the database optimizer must recognize when a different type of application requests data from any changed column, and must potentially perform a conversion from the new data type to the old data type before returning the requested data. 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 that accesses the column a percentage of time that is less than a second predefined threshold level. In this case, a new column is created in the database so the data is present in both formats, thereby optimizing the performance of both old and new applications that access the data. The database optimizer looks at what type of application requested data, and returns the data in the format optimized for that type of application.

Problems solved by technology

This problem arises, for example, when data that was originally created by legacy applications in one data type now needs to be accessed by new languages and APIs that expect a different data type.

The format of the data stored in the database results in performance penalties for any application that needs to access the data, but could run faster if the data were of a different type.

Changing the data type to accommodate the new applications is generally not an acceptable option, because changing the data type would require significant manual rework.

This problem is especially apparent as companies with existing applications and databases become internet-enabled, which is commonly done using Java to access data in existing databases.

Using Java to access data in less-than-optimal data types in older databases results in performance penalties that are significant.

Without an apparatus and method that allows a database to dynamically evolve according to the applications accessing its data, the computer industry will continue to suffer from excessive overhead in porting existing data to new applications, such as web-enabled applications.

Images