Access path selection for SQL with variables in a RDBMS

Details Access path selection for SQL with variables in a RDBMS Access path selection for SQL with variables in a RDBMS

1999-09-08

2001-04-17

Alam, Hosain T. (Department: 2771)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C707S793000

Reexamination Certificate

active

06219660

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates in general to computer-implemented database systems, and, in particular, to improving an access path selection for Structured Query Language with variables in a relational database management system.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data Relational databases are organized into tables which consist of rows and columns of data. The rows are formally called tuples. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on random access storage devices (RASD) such as magnetic or optical disk drives for semi-permanent storage.
RDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO). The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages, such as C and COBOL. SQL allows the user to manipulate the data.
The definitions for SQL provide that a RDBMS should respond to a particular query with a particular set of data given a specified database content, but the method that the RDBMS uses to actually find the required information in the tables on the disk drives is left up to the RDBMS. Typically, there will be more than one method that can be used by the RDBMS to access the required data. The RDBMS will optimize the method used to find the data requested in a query in order to minimize the computer time used and, therefore, the cost of performing the query.
The RDBMS software determines an access path for an SQL statement during a bind process. SQL compilation is called a BIND process, and the output of the BIND process is a plan, which is a compiled run-time structure used for executing the SQL statement. The plan includes access paths, which are the paths the RDBMS uses to get to the data that SQL statements request. During the BIND process, an optimizer of the RDBMS software selects an access path for a SQL statement. The access path is the key to determining how well an SQL statement performs.
Currently, when an SQL statement contains a variable, such as a host variable, a parameter marker, the RDBMS uses a default filter factor to determine an access path for the SQL statement. A filter factor, also known as selectivity, is a reference to the number of qualified rows of a table that will be satisfied for the query. For example, if the filter factor is 10%, then 10% of the rows of a table qualify and the remaining rows are filtered out (i.e., are not relevant to processing the query). However, when a SQL statement includes a variable, the default filter factor selected for the SQL statement is typically not optimal, and, therefore, the access path that is determined is not necessarily optimal. There is a need in the art for an improved technique for determining an access path for a SQL statement that contains a variable.
SUMMARY OF THE INVENTION
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for a computer implemented query compiler.
In accordance with the present invention, a query is executed in a computer, the query being performed by the computer to retrieve data from a database stored on a data storage device connected to the computer. The query is received containing at least one variable. At execution time, a value is determined for each variable in the query. A filter factor is estimated using the determined value for each variable. Then, an access path is determined for the query using the estimated filter factor.
An object of the invention is to provide an improved technique for selecting an access path for an SQL statement. Another object of the invention is to provide a query compiler for SQL statements containing variables.


REFERENCES:
patent: 5091852 (1992-02-01), Tsuchida et al.
patent: 5301317 (1994-04-01), Lohman et al.
patent: 5615361 (1997-03-01), Leung et al.
patent: 5640555 (1997-06-01), Kleewein et al.
patent: 5649168 (1997-07-01), Huang et al.
patent: 5659728 (1997-08-01), Bhargava et al.
patent: 5758144 (1998-05-01), Eberhard et al.
patent: 5822750 (1999-02-01), Jou et al.
patent: 5826077 (1998-10-01), Blakeley et al.
patent: 5875334 (1999-02-01), Chow et al.
Yu, Philip S., Chen, Ming-Syan, Heiss, Hans-Ulrich, and Lee, Sukho, “Concise Papers On Workload Characterization of Relational Database Environments,” IEEE Transactions On Software Engineering, vol. 18, No. 4, pp. 347-355, Apr. 1992.

Affiliated with

Also associated with

Rating

Access path selection for SQL with variables in a RDBMS does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Access path selection for SQL with variables in a RDBMS, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Access path selection for SQL with variables in a RDBMS will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2455062