This invention relates in general to database management systems performed by computers, and in particular, to processing query predicates having data types.
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 direct access storage devices (DASD) 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 doing the query.
A RDBMS can support certain data types. Applications that utilize the RDBMS (e.g., C or JAVA) may or may not support these same data types as a RDBMS. For example, the RDBMS of the DB2(copyright) product offered by IBM(copyright) for the OS/390(copyright) operating systems can support string and numeric data types. String data types include character and graphic, both of which may be fixed length or varying length. Numeric data types include binary integer, floating point, and decimal. Applications that are written in C, for example, do not have decimal data types, and JAVA strings are defined without character length attributes.
Conventional systems process query predicates with different data types by retrieving an entire set of data, converting the data type of the entire data set to match the data types of the predicate, then performing filtering on all of the rows of the data set based on the predicate conditions. As a result, conventional systems perform unnecessary data conversions and comparisons because there is not a technique to retrieve a subset of data as compared to an entire set of data, and to perform subsequent conversions and comparisons on the subset of data.
An SQL query predicate may request rows of data based on the comparison that a variable have a value greater than 1.5 (i.e., a floating or decimal data type). The table rows, however, may have an integer data type. In this case, conventional systems make a temporary copy of the integer, convert the integer to decimal, retrieve all of the table rows from the table, and then filter the results based on the query predicate. Consequently, the RDS performs tasks which otherwise would not be required if the data types matched. The RDS retrieves all of the rows, converts all of the rows, and compares all of the rows to the conditions set forth in the query predicate. As a result, more time is required to transfer, convert, process and filter the data to obtain a result leading to diminished system performance. These shortcomings are further amplified when large amounts of data are retrieved from an RDS or if complex query predicates are involved.
Thus, there is a need in the art for improved query optimization that is able to process query predicates with different data types than those data types utilized by a RDS and to relieve the RDS from having to transfer, convert, and compare all of the rows in order to obtain a result.
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 retrieving smaller data sets for query predicates with data type mismatches.
According to an embodiment of the invention, a technique is provided for optimizing execution of a query that accesses data stored on a data store connected to a computer. Initially, a first query predicate comparing different data types is identified. A second query predicate with matching data types is generated. Then, the generated second query predicate is executed against the database to obtain a subset of records.