Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-12-06
2003-05-27
Mizrahi, Diane D. (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000
Reexamination Certificate
active
06571233
ABSTRACT:
FIELD OF THE INVENTION
This invention relates in general to database management systems performed by computers, and in particular, to processing query predicates having data types.
BACKGROUND OF THE INVENTION
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® product offered by IBM® for the OS/390® 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.
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 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.
REFERENCES:
patent: 5367675 (1994-11-01), Cheng et al.
patent: 5590324 (1996-12-01), Leung et al.
patent: 5615361 (1997-03-01), Leung et al.
patent: 5680603 (1997-10-01), Bhargava et al.
patent: 5706495 (1998-01-01), Chadha et al.
patent: 5826076 (1998-10-01), Bradley et al.
patent: 5864847 (1999-01-01), Goel et al.
patent: 5875447 (1999-02-01), Goel et al.
patent: 5895465 (1999-04-01), Guha
patent: 5960427 (1999-09-01), Goel et al.
patent: 5963933 (1999-10-01), Cheng et al.
patent: 6081799 (2000-06-01), Beavin et al.
patent: 6327587 (2001-12-01), Forster
patent: 2002/0083049 (2002-06-01), Forster et al.
Song Xue, Microsoft Technet Article: SQL Server 2000 Windows CE Edition 2.0 Query Processor Overview and Performance Tuning Approaches, Oct. 2002, pp. 15-16.*
“DB2 Cost Formula”, IBM Technical Disclosure Bulletin, 34:12, No. 12, p. 389-394, May 1992.
“Access Path Selection in a Relational Database Management System”, IBM Technical Disclosure Bulletin, vol. 22, No. 4, pp. 1657-1660, Sep. 1979.
Dalamagas, T. et al. “A visual database system for spatial and non-spatial data management”, Visual Database Systems 4 (VDB4). IFIP TC2/WG2.6 Fourth Working Conference on Visual Database Systems 4 (VDB4), pp. 105-122, Published: London, UK 1998, vii+380 pp.
Ubell, M. et al. “Embedding image query operations in an object-relational database management system”, Proceedings of the SPIE—The International Society for Optical Engineering, vol. 2420, pp. 203, 1995.
Griffiths, S. P. et al. “Access path selection in a relational database management system”, Rpt. No. RJ2429 (32240). Aug. 1, 1979, 59 pp.
O'Neil, P. et al. “Improved query performance with variant indexes”, SIGMOD Record, vol. 26, No. 2, pp. 38-49, Jun. 1997.
Beavin Thomas A.
Malone Patrick M.
Mukai Jerry
Nakagawa Randy M.
Gates & Cooper LLP
International Business Machines - Corporation
Mizrahi Diane D.
Spiegel Michael
LandOfFree
Optimization of SQL queries using filtering predicates does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Optimization of SQL queries using filtering predicates, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Optimization of SQL queries using filtering predicates will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3046476