|Publication number||US7136848 B2|
|Application number||US 10/230,641|
|Publication date||Nov 14, 2006|
|Filing date||Aug 29, 2002|
|Priority date||Jun 7, 2002|
|Also published as||US20030229621|
|Publication number||10230641, 230641, US 7136848 B2, US 7136848B2, US-B2-7136848, US7136848 B2, US7136848B2|
|Inventors||David Glenn Carlson, Kevin James Kathmann|
|Original Assignee||International Business Machines Corporation|
|Export Citation||BiBTeX, EndNote, RefMan|
|Patent Citations (4), Non-Patent Citations (4), Referenced by (4), Classifications (12), Legal Events (4)|
|External Links: USPTO, USPTO Assignment, Espacenet|
This patent application is a continuation-in-part of “OBJECT-ORIENTED QUERY EXECUTION DATA STRUCTURE”, U.S. Ser. No. 10/165,293, filed on Jun. 7, 2002 now U.S. Pat. No. 6,915,291 by Carlson et al., which is incorporated herein by reference.
This application is related to “PARALLEL DATABASE QUERY PROCESSING FOR NON-UNIFORM DATA SOURCES VIA BUFFERED ACCESS”, U.S. Ser. No. 10/165,235, filed on Jun. 7, 2002 by Carlson et al., “RUNTIME QUERY OPTIMIZATION FOR DYNAMICALLY SELECTING FROM MULTIPLE PLANS IN A QUERY BASED UPON RUNTIME-EVALUATED PERFORMANCE CRITERION”, U.S. Ser. No. 10/165,025, filed on Jun. 7, 2002 by Carlson et al., and “METHOD FOR EFFICIENT PROCESSING OF MULTI-STATE ATTRIBUTES”, U.S. Ser. No. 10/164,767, filed on Jun. 7, 2002 by Carlson et al., which are all incorporated herein by reference.
1. Technical Field
This invention generally relates to computer systems, and more specifically relates to apparatus and methods for accessing data in a computer database.
2. Background Art
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.
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 for and retrieve specific information in the database. For example, an insurance company may have a database that includes all of its policy holders and their current account information, including payment history, premium amount, policy number, policy type, exclusions to coverage, etc. A database system allows the insurance company to retrieve the account information for a single policy holder among the thousands and perhaps millions of policy holders in its database.
Retrieval of information from a database is typically done using queries. A query usually specifies conditions that apply to one or more columns of the database, and may specify relatively complex logical operations on multiple columns. The database is searched for records that satisfy the query, and those records are returned as the query result.
The prior art has recognized that repeatedly optimizing the same query results in a degradation of database performance. As a result, the prior art includes query optimizers that save a query once it is optimized so it may be reused if the exact same query needs to be executed again. In the prior art, however, if the query is different at all, even slightly different, the query must be optimized again from scratch. For example, if a complex query is evaluated, and an operand in the query is a 16 bit integer, the same query that specifies an operand that is a 32 bit integer in the place of the operand that is a 16 bit integer is deemed to be a new query in the prior art, causing the query optimizer to completely optimize the new query, thereby not benefitting from any of the work previously performed in optimizing the query that specifies the 16 bit integer operand. Without a way for a query optimizer to benefit from optimizing that was previously performed on queries that specify operands of different data types, the computer industry will continue to suffer from excessive overhead in optimizing database queries.
According to the preferred embodiments, previously-optimized database queries are stored in memory. When a new query needs to be optimized, the previously-optimized queries are examined to determine whether the new query has been previously optimized. If the new query has not been previously optimized, the previously-optimized queries are examined to determine whether any previously-optimized queries differ only in data type of one or more operands when compared to the new query. If a previously-optimized query that differs only in data type is located, the previously-optimized query is refreshed to reflect the different data type without the need of optimizing the new query from scratch. Portions of previously-optimized queries may thus be re-used even when a previously-optimized query is not identical to a new query to be optimized. As a result, the performance of query optimization in a database system is increased.
The foregoing and other features and advantages of the invention will be apparent from the following more particular description of preferred embodiments of the invention, as illustrated in the accompanying drawings.
The preferred embodiments of the present invention will hereinafter be described in conjunction with the appended drawings, where like designations denote like elements, and:
The present invention relates to optimizing database queries. For those not familiar with databases or queries, this Overview section will provide background information that will help to understand the present invention.
There are many different types of databases known in the art. The most common is known as a relational database (RDB), which organizes data in tables that have rows that represent individual entries or records in the database, and columns that define what is stored in each entry or record.
To be useful, the data stored in databases must be able to be efficiently retrieved. The most common way to retrieve data from a database is to generate a database query. A database query is an expression that is evaluated by a database manager. The expression may contain one or more predicate expressions that are used to retrieve data from a database. For example, lets assume there is a database for a company that includes a table of employees, with columns in the table that represent the employee's name, address, phone number, gender, and salary. With data stored in this format, a query could be formulated that would retrieve the records for all female employees that have a salary greater than $40,000. Similarly, a query could be formulated that would retrieve the records for all employees that have a particular area code or telephone prefix.
One popular way to define a query uses Structured Query Language (SQL). SQL defines a syntax for generating and processing queries that is independent of the actual structure and format of the database. One sample SQL query is shown in
In the prior art, a tool known as a query optimizer evaluates expressions in a query. The evaluation of expressions in a query can take considerable time. For this reason, known query optimizers often store optimized queries so they can be reused in the future without the need of re-processing their expressions. An example of a prior art method for optimizing a query is method 300 shown in
One deficiency in the prior art is that the query must be identical in step 310 in order to benefit from a previously-optimized query. The preferred embodiments recognize that some queries may be refreshed, which updates a query for a new data type without re-optimizing the entire query. Examples in accordance with the preferred embodiments are described in detail below.
2.0 Detailed Description
The preferred embodiments provide a way to refresh a previously-optimized query when the only change to the query is a change in data type of one or more operands in the query. Refreshing the query for a change in data type may be performed by a simple refresh operation, without re-optimizing all of the expressions in the query.
Referring now to
Main memory 120 in accordance with the preferred embodiments contains data 122, an operating system 123, a database 124, one or more database queries 125, a database query optimizer 126, and one or more optimized queries 128. Database query optimizer 126 preferably includes a query refresher 127 that allows for refreshing an optimized query 128 when the optimized query differs from a query 125 that needs to be optimized only in the data type of one or more operands in the query.
Computer system 100 utilizes well known virtual addressing mechanisms that allow the programs of computer system 100 to behave as if they only have access to a large, single storage entity instead of access to multiple, smaller storage entities such as main memory 120 and DASD device 155. Therefore, while data 122, operating system 123, database 124, database query 125, database query optimizer 126, and optimized queries 128 are shown to reside in main memory 120, those skilled in the art will recognize that these items are not necessarily all completely contained in main memory 120 at the same time. It should also be noted that the term “memory” is used herein to generically refer to the entire virtual memory of computer system 100, and may include the virtual memory of other computer systems coupled to computer system 100.
Data 122 represents any data that serves as input to or output from any program in computer system 100. Operating system 123 is a multitasking operating system known in the industry as OS/400; however, those skilled in the art will appreciate that the spirit and scope of the present invention is not limited to any one operating system. Database 124 is any suitable database, whether currently known or developed in the future. Database query 125 is a query in a format compatible with the database 124 that allows information stored in the database 124 that satisfies the database query 125 to be retrieved. Database query optimizer 126 optimizes a database query 125. Once database query optimizer 126 optimizes a query, optimized query 128 is stored in main memory 120. This allows the stored optimized queries to be used later if a similar query is encountered that differs only in data type of one or more of the operands of the query. Query refresher 127 is used to refresh an optimized query 128 to reference one or more different operands of different data types. An optimized query 128 may be refreshed much more quickly than re-optimizing the query due to a difference in data type (as the prior art would do).
Processor 110 may be constructed from one or more microprocessors and/or integrated circuits. Processor 110 executes program instructions stored in main memory 120. Main memory 120 stores programs and data that processor 110 may access. When computer system 100 starts up, processor 110 initially executes the program instructions that make up operating system 123. Operating system 123 is a sophisticated program that manages the resources of computer system 100. Some of these resources are processor 110, main memory 120, mass storage interface 130, display interface 140, network interface 150, and system bus 160.
Although computer system 100 is shown to contain only a single processor and a single system bus, those skilled in the art will appreciate that the present invention may be practiced using a computer system that has multiple processors and/or multiple buses. In addition, the interfaces that are used in the preferred embodiment each include separate, fully programmed microprocessors that are used to off-load compute-intensive processing from processor 110. However, those skilled in the art will appreciate that the present invention applies equally to computer systems that simply use I/O adapters to perform similar functions.
Display interface 140 is used to directly connect one or more displays 165 to computer system 100. These displays 165, which may be non-intelligent (i.e., dumb) terminals or fully programmable workstations, are used to allow system administrators and users to communicate with computer system 100. Note, however, that while display interface 140 is provided to support communication with one or more displays 165, computer system 100 does not necessarily require a display 165, because all needed interaction with users and other processes may occur via network interface 150.
Network interface 150 is used to connect other computer systems and/or workstations (e.g., 175 in
At this point, it is important to note that while the present invention has been and will continue to be described in the context of a fully functional computer system, those skilled in the art will appreciate that the present invention is capable of being distributed as a program product in a variety of forms, and that the present invention applies equally regardless of the particular type of signal bearing media used to actually carry out the distribution. Examples of suitable signal bearing media include: recordable type media such as floppy disks and CD ROM (e.g., 195 of
The parent of this application discloses an object oriented data structure for representing and executing queries. A query is comprised of a collection of live objects arranged in a tree relationship, along with an attribute data structure that is configured to manipulate one or more attributes in the attribute data structure. The attribute data structure includes an attribute operation list that contains logic that operates on one or more operands specified in a corresponding attribute descriptor array. The logic may include calls to methods that process data of a specified data type.
An example of a query object in accordance with the object oriented data structure disclosed in the parent application is shown in
Attribute operation object 530 shows operations that are performed to implement the equality test shown in object 514. Two operands are shown, which reference entries in the attribute descriptor array 550. Attribute descriptor array 550 includes information that describes the operand, including its data type, and a Value_Pointer that points to a location 552 in the database where the operand value resides. Thus, we see from attribute descriptor array 550 that entry 0) is a 16 bit integer, with a value pointer that points to the value of a.f1 in the database. Entry 1) is a 16 bit integer, with a value pointer that points to a constant with a value of 1. Entry 2) is a 16 bit integer, with a value pointer that points to the value of a.f2 in the database. Entry 3) is a 16 bit integer, with a value pointer that points to a constant with a value of 2. Entry 4) is a 16 bit integer, with a value pointer that points to the value of a.f3 in the database. Entry 5) is a 16 bit integer, with a value pointer that points to the value of a.f4 in the database. Entry 6) is a 16 bit integer, with a value pointer that points to the value of a.f3 divided by a.f4. Entry 7 is a 16 bit integer, with a value that points to a constant with a value of 3.
The two operands in attribute operation object 530 correspond to entries 2) and 3), respectively, in the attribute descriptor array 550, as shown by the dotted arrows pointing to entries 2) and 3). The three operands in attribute operation object 540 correspond to entries 4)–6), respectively, in the attribute descriptor array 550, as shown by the dotted arrows pointing to entries 4)–6).
Each attribute operation object 530 and 540 includes a call to a method that performs the desired operation on the specified operands. The method to be executed is determined during a refresh operation by examining a method vector that corresponds to the method being called. For example, object 530 includes an operation xMethod. A refresh operation determines from the Binary_Logic_Equal_Method_Vector 560 which method corresponds to the data type of the operands, and writes the pointer to the method to the xMethod in object 530. Thus, when xMethod is executed, the corresponding method code 562 is executed that corresponds to xMethod. Note from the entries in method vector 560 that a different method is invoked for each data type of the two operands. While method vectors 560 and 570 show methods that operate on operands of the same data type, it is also within the scope of the preferred embodiments to operate on operands of different data types by the same method. As shown in
In similar fashion, the method xMethod in object 540 determines from Ternary_Divide_Method_Vector 570 which method pointer corresponds to the type of the operands, and invokes the method code 572 that is identified by the method pointer for 16 bit operands. Method code 572 performs a division of two 16 bit integer operands, and returns the result in a third 16 bit integer operand.
Object oriented data structure 500 of
Referring now to
The preferred embodiments recognize that a change of data type of one or more operands may be efficiently optimized by refreshing a previously-optimized query (if one exists) to represent the new operand(s). Refreshing a previously-optimized query involves the steps of updating the attribute descriptor array to reference the new operand(s), and changing one or more method pointers to point to method code that processes the data type of the new operand(s). The first step in refreshing the data structure is to change the type of entries 4) through 7) to float. Thus, attribute descriptor array 650 in
Once the attribute descriptor array has been updated, the method pointer for the operations on the operands of the changed data type must be updated to reflect the data type of the changed operands. Thus, we see in
In the preferred embodiments, the refresh( ) method on the attribute operation list 610 may include the modification of the attribute descriptor array 650. In this manner, invoking the refresh( ) method would result in performing both the update of the attribute descriptor array 650 and the update of the method pointer in method vector 670. Note, however, that it is equally within the scope of the preferred embodiments to modify the attribute descriptor array 650 to reflect the new operands and/or data types before invoking the refresh( ) method, which would then only update the method pointer in the method vector 670.
The process of refreshing a database query takes substantially less time than the process of building a new query data structure. If prior art methods for optimizing queries were applied to the object oriented data structure for representing queries taught in the parent application, a query that is different only in the data type of one or more of its operands than a previously-optimized query would require full optimization of the query, meaning that a new data structure for the query would have to be generated. For the case shown in
Note that a change in data type described herein is used in a broad sense to mean any change in type that may be recognized in any computer system. Thus, a change in data type not only includes a change from an integer to a floating point number, but also includes a change from different lengths of the same kind, such as a change from a 16 bit integer to a 32 bit integer.
The preferred embodiments described herein allow using a previously-optimized query when the previously-optimized query varies from a current query only in the data type of one or more operands in the query. If a query to be executed varies only in data type from a previously-optimized query, the previously-optimized query may be refreshed to represent the query to be executed. This eliminates the overhead associated with optimizing the query if the query is not identical to a previously-optimized query. Note that a change of data type may include a change in the data type of output attributes of the query.
One skilled in the art will appreciate that many variations are possible within the scope of the present invention. Thus, while the invention has been particularly shown and described with reference to preferred embodiments thereof, it will be understood by those skilled in the art that these and other changes in form and details may be made therein without departing from the spirit and scope of the invention.
|Cited Patent||Filing date||Publication date||Applicant||Title|
|US5991754 *||Dec 28, 1998||Nov 23, 1999||Oracle Corporation||Rewriting a query in terms of a summary based on aggregate computability and canonical format, and when a dimension table is on the child side of an outer join|
|US6263328||Apr 9, 1999||Jul 17, 2001||International Business Machines Corporation||Object oriented query model and process for complex heterogeneous database queries|
|US6314430||Feb 23, 1999||Nov 6, 2001||International Business Machines Corporation||System and method for accessing a database from a task written in an object-oriented programming language|
|US20020069193 *||Dec 6, 2000||Jun 6, 2002||Beavin Thomas A.||Optimization of SQL queries using filtering predicates|
|1||G. Mitchell, "Extensible Query Processing in an Object-Oriented Database," May 1993, Thesis, Department of Computer Science, Brown University, pp. 1-166.|
|2||IBM Patent Application filed on even date herewith by Carlson et al., "Method for Efficient Processing of Multi-State Attributes".|
|3||IBM Patent Application filed on even date herewith by Carlson et al., "Parallel Database Query Processing for Non-Uniform Data Sources via Buffered Access".|
|4||IBM Patent Application filed on even date herewith by Carlson et al., "Runtime Query Optimization for Dynamically Selecting from Multiple Plans in a Query Based Upon Runtime-Evaluated Performance Criterion".|
|Citing Patent||Filing date||Publication date||Applicant||Title|
|US8046354 *||Sep 30, 2004||Oct 25, 2011||International Business Machines Corporation||Method and apparatus for re-evaluating execution strategy for a database query|
|US9607042||Sep 15, 2014||Mar 28, 2017||Mastercard International Incorporated||Systems and methods for optimizing database queries|
|US20060074874 *||Sep 30, 2004||Apr 6, 2006||International Business Machines Corporation||Method and apparatus for re-evaluating execution strategy for a database query|
|US20100211939 *||Feb 18, 2009||Aug 19, 2010||International Business Machines Corporation||Processing an object-oriented query to retrieve data from a data source|
|U.S. Classification||1/1, 707/999.003, 707/999.001, 707/999.004|
|Cooperative Classification||Y10S707/99933, Y10S707/99931, Y10S707/99934, G06F17/30463, G06F17/30471|
|European Classification||G06F17/30S4P3T5, G06F17/30S4P3T6|
|Aug 29, 2002||AS||Assignment|
Owner name: INTERNATIONAL BUSINESS MACHINES CORPORATION, NEW Y
Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:CARLSON, DAVID GLENN;KATHMANN, KEVIN JAMES;REEL/FRAME:013254/0907
Effective date: 20020829
|Jun 21, 2010||REMI||Maintenance fee reminder mailed|
|Nov 14, 2010||LAPS||Lapse for failure to pay maintenance fees|
|Jan 4, 2011||FP||Expired due to failure to pay maintenance fee|
Effective date: 20101114