US 6,526,403 B1Grant
Method, computer program product, and system for rewriting database queries in a heterogenous environment
Issue Date:2003-02-25
•17 Claims
•9 Drawing Sheets
Abstract
A method, computer product, and system for rewriting database without decreasing pushdownability is provided. First, a pushdown analysis of the query in its entirety is performed prior to the application of any query rewrite rules in order to establish a baseline on pushdownability for the query. The results of this analysis is stored with the internal query representation. After each rule is applied to rewrite a portion of a query, that rewritten portion is analyzed again for pushdownability. If pushdownability is not decreased, then the rewritten query remains and the internal representation of the query is updated to reflect the pushdownability of that portion. If pushdownability is decreased, then an undo operation is applied to the rewritten portion of the query to back out the effects of the rule and leave the query in the same state as before the rewrite. Finally, additional rewrite rules that are directed to the heterogeneous database environment are also provided.
Metadata
Assignee
- International Business Machines Corporation
Inventors
- Eileen Tien Lin
- Tina Louise Mukai
- Shivakumar Venkataraman
- Tian Zhang
Application Information
Application Number:US 09/466,492
Filing Date:1999-12-17
Priority Date:1999-12-17
Art Unit:7
Classifications
IPC:
G06F 1730
Field of Search:
707200707203
Patent Drawings (9 sheets)
Description
Cross-Reference to Related Applications
[0002] This application is related to the following co-pending and commonly assigned application, which application is incorporated by reference herein:
[0003] Application Ser. No. 09/466,560, entitled “Method, Computer Program Product, and System for Pushdown Analysis During Query Plan Generation,” filed on the same date herewith, by Tian Zhang, Shivakumar Venkataraman, Tina Mukai, and Eileen Lin,
Background of the Invention
[0004] 1. Field of the Invention
[0005] The present invention relates to the processing and optimization of database queries for increased execution performance. More particularly, the present invention relates to query optimization in a heterogeneous database environment where a local database system appears and responds to clients as if having at least some tables locally that are actually on remote database systems.
[0006] 2. Present State of the Art
[0007] An important phase of query compilation is query rewrite, that is, to apply some heuristic rules to rewrite a complex SQL query into a generally efficient or convenient form so that the query optimizer can determine the best query plan for execution. Often, optimizing a rewritten query leads to significant improvement in query execution time, since the query optimizer can generate a better query plan based on the rewritten query.
[0008] While the traditional query rewrite mechanisms in a database system may improve query performance of queries directed against local tables, performance degradation can occur in a heterogeneous database system context where the queries may contain references to both remote tables and local tables. If queries posed on a heterogeneous database system are rewritten by the query compiler in the same fashion as queries directed to a local database system's local tables, the query compiler [compile] may produce query forms that are not supported by the remote databases. Execution times of queries in a heterogeneous database system context that undergo rewrite in this manner will suffer if the query processing abilities of the remote database systems are not taken into consideration.
[0009] In a heterogeneous database environment, the pushdownability of a heterogeneous query (i.e., the portions of the query that can be executed at the remote databases) might be decreased due to the changes made by the traditional query rewrite heuristic rules. In a heterogeneous database environment, maximizing the pushdownability of a heterogenerous query has been proven to be very important for allowing the heterogeneous query optimizer to make highly efficient decisions.
[0010] Therefore what is needed are ways to preserve the pushdownability of heterogeneous queries so that the optimizer can select more alternatives for generating query execution plans.
Summary of the Invention
[0011] One aspect of the present invention is to extend traditional query rewrite rules so that they are effective in a heterogeneous environment.
[0012] Another aspect of the present invention is to not decrease the pushdownability of a query due to the application of a query rewrite rule.
[0013] Yet another aspect of the present invention is to present new query rewrite rules designed for the heterogeneous database environment.
[0014] Additional aspects and benefits of the invention will be set forth in the description that follows, and in part will be obvious from the description, or may be learned by the practice of the invention. The benefits of the invention may be realized and obtained by the combinations particularly pointed out in the appended claims.
[0015] In accordance with the invention as embodied and broadly described herein, a method, computer product, and system for rewriting database without decreasing pushdownability is provided.
[0016] First, a pushdown analysis of the query in its entirety is performed prior to the application of any query rewrite rules in order to establish a baseline on pushdownability for the query. The results of this analysis is stored with the internal query representation. After each rule is applied to rewrite a portion of a query, that rewritten portion is analyzed again for pushdownability. If pushdownability is not decreased, then the rewritten query remains and the internal representation of the query is updated to reflect the pushdownability of that portion. If pushdownability is decreased, then an undo operation is applied to the rewritten portion of the query to back out the effects of the rule and leave the query in the same state as before the rewrite.
[0017] By keeping the heterogeneous query in a form so that the portions that can be executed at the remote databases are not decreased, it provides the heterogenerous query optimizer more alternatives for generating the execution plans (i.e., to execute those portions remotely or to execute those portions locally). This way, a broader plan space can be explored by the optimizer to make the final decision either to evaluate some portions of the query remotely or locally based on a cost model that takes into account all the relevant factors such as CPU, I/O, and network costs in order to come up with the final query execution plan.
[0018] This invention extends query rewrite to a heterogeneous database system environment. It solves the problem of extending the traditional query rewrite mechanisms to a heterogeneous database environment so that the heterogeneous database queries can be rewritten into an efficient form to improve query execution times.
Brief Description of the Drawings
[0019] A more particular description of the invention briefly described above will be rendered by reference to specific embodiments thereof which are illustrated in the appended drawings. These drawings depict only one or more typical embodiments of the invention and are not therefore to be considered to be limiting of its scope.
[0020] FIG. 1 is a block diagram of a computing device, such as a workstation, wherein the present invention may be practiced;
[0021] FIG. 2 is a logical diagram of a database environment where a number of clients send database queries to a local server running a heterogeneous database system therein that in turn may contact remote database systems of different types to service the query;
[0022] FIG. 3 is a logical diagram showing the software phases used for query processing (i.e., query compilation) of one embodiment of the present invention;
[0023] FIG. 4 is a flowchart illustrating some of the processing steps taken in the Query Rewrite phase shown in FIG. 3 in order to ensure that the pushdownability of a query after application of a query rewrite rule is not decreased;
[0024] FIG. 5 is a flowchart illustrating some of the processing steps taken in the Optimizer phase shown in FIG. 3 in order to generate a greater number of alternatives for the Optimizer phase to use that takes into account pushdownability during query plan enumeration;
[0025] FIG. 6 is a logical diagram representing a set of portions of a query that may be generated by the Optimizer phase shown in FIG. 3;
[0026] FIG. 7 is a logical diagram representing a set of final query plans that can be generated using the portions shown in FIG. 6;
[0027] FIG. 8 is a logical diagram representing an example of one final query plan that does not make pushdown analysis during plan enumeration (FIG. 8(a)) and final query plan that generally would be more efficient in a heterogeneous database environment that can be generated by taking into account pushdown analysis during plan enumeration;
[0028] FIG. 9 is a logical diagram representing an example of one final query plan that does not make pushdown analysis during plan enumeration (FIG. 8(a)) and final query plan that generally would be more efficient in a heterogeneous database environment that can be generated by taking into account pushdown analysis during plan enumeration.
Detailed Description of the Invention
[0029] FIG. 1 is a block diagram of a computing device, such as a workstation, wherein the present invention may be practiced. The environment of FIG. 1 comprises a single representative computing device 100, such as a personal computer, workstation, hand-held computer, information appliance, etc., including related peripheral devices. The workstation 100 includes a microprocessor 102 or equivalent processing capability and a bus 104 to connect and enable communication between the microprocessor 102 and the components of the computing device 100 in accordance with known techniques. Note that in some computing devices there may be multiple processors incorporated therein.
[0030] The microprocessor 102 communicates with storage 106 via the bus 104. Memory 108, such as Random Access Memory (RAM), Read Only Memory (ROM), flash memory, etc. is directly accessible while secondary storage device 110, such as a hard disk, and removable storage device 112, such as a floppy diskette drive, CD ROM drive, tape storage, etc. is accessible with additional interface hardware and software as is known and customary in the art. The removable storage device 112 will have associated therewith an appropriate type of removable media 114, such as a diskette, CD, tape reel or cartridge, solid state storage, etc. that will hold computer useable data and is a form of computer useable medium. Note that a computing device 10 may have multiple memories (e.g., RAM and ROM), secondary storage devices, and removable storage devices (e.g., floppy drive and CD ROM drive).
[0031] The computing device 100 typically includes a user interface adapter 116 that connects the microprocessor 102 via the bus 104 to one or more interface devices, such as keyboard 118, a mouse or other pointing device 120, a display 122 (such as a CRT monitor, LCD screen, etc.), a printer 124, or any other user interface device, such as a touch sensitive screen, digitized entry pad, etc. Note that the computing device 100 may use multiple user interface adapters in order to make the necessary connections with the user interface devices.
[0032] The computing device 100 may also communicate with other computing devices, computers, workstations, etc. or networks thereof through a communications adapter 126, such as a telephone, cable, or wireless modem, DSL adapter, Local Area Network (LAN) adapter, or other communications channel. This gives the computing device direct access to networks 128 (LANs, Wide Area Networks (WANs), the Internet, etc.), telephone lines 130 that may be used to access other networks or computers, wireless networks 132, such cellular telephone networks, and other communication mechanisms. Note that the computing device 100 may use multiple communication adapters for making the necessary communication connections (e.g., a telephone modem card and a Cellular Digital Packet Data (CDPD). The computing device 100 may be associated with other computing devices in a LAN or WAN, or the computing device can be a client or server in a client/server arrangement with another computer, etc. All these configurations, as well as the appropriate communications hardware and software, are known in the art.
[0033] As will be understood by one of ordinary skill in the art, computer programs such as that described herein are typically distributed as part of a computer program product that has a computer useable media or medium containing the program code. Therefore, “media”, “medium”, “computer useable medium”, or “computer useable media”, as used herein, may include a diskette, a tape, a compact disc, an integrated circuit, a programmable logic array (PLA), a remote transmission over a communications circuit, a remote transmission over a wireless network such as a cellular network, or any other medium useable by computers with or without proper adapter interfaces. Note that examples of a computer useable medium include but are not limited to palpable physical media, such as a CD ROM, diskette, hard drive and the like, as well as other non-palpable physical media, such as a carrier signal, whether over wires or wireless, when the program is distributed electronically. Note also that “servlets” or “applets” according to JAVA technology available from Sun Microsystems of Mountain View, Calif. would be considered computer program products.
[0034] Although the enabling instructions might be “written on” on a diskette or tape, “stored in” an integrated circuit or PLA, “carried over” a communications circuit or wireless network, it will be appreciated, that for purposes of the present invention described herein, the computer useable medium will be referred to as “bearing” the instructions, or the instructions (or software) will be referred to as being “on” the medium. Thus, software or instructions “on” a medium is intended to encompass the above and all equivalent ways in which the instructions or software is associated with a computer useable medium.
[0035] For simplicity, the term “computer program product” is used to refer to a computer useable medium, as defined above, which bears or has embodied thereon any form of software or instructions to enable a computer system (or multiple cooperating systems) to operate according to the above-identified invention.
[0036] The term “data structure” refers to a particular organization of meaningful data values that can be used in a predetermined fashion. For example, a network packet has a variety of different data elements that are used and accessed by communications networks and computer nodes for transporting the packet between different computer systems. The packet, as described above, is a data structure and has a tangible embodiment in a computer useable medium when stored in a file, when loaded into system memory, when transported across a communications network, etc. in the same fashion as a computer program product.
[0037] It will be likewise appreciated that the computer hardware upon which the invention is effected contains one or more processors, operating together, substantially independently, or distributed over a network, and further includes memory for storing the instructions and calculations necessary to perform the invention.
[0038] Referring now to FIG. 2, a heterogeneous database environment is presented. Typically, a number of clients 200 communicate with a local server 202, or other computing device, that has running thereon a local database system 204 that is able to respond to queries across multiple database systems, such as DB2 DataJoiner available from IBM.
[0039] The local database system 204 will communicate with one or more remote database systems having remote tables thereon. Essentially, the local database system 204 makes it appear to the clients 200 that all the remote tables (e.g., employee table, project table, department table, etc.) are local tables and can be manipulated as if local using the native query language, such as SQL statements.
[0040] For example, in FIG. 2, Server A 206 is running remote database system A 208 that will have access to an Employee table and a Project table, and Server B 210 is running remote database system B 212 that will have access to a Department table. Any of the clients 200 can send queries to the local database system 204 that will be handled by a query processing component 218 where all of the remote tables will appear to the clients 200 as local tables and will function as such. The local database system 204 will handle all of the operations, such as making remote queries to acquire data, without the clients 200 even being aware of the existence of the remote database systems.
[0041] While the remote database systems A 208 and B 212 may be of the same type as the local database system 204, this is not required. Because the remote systems may be of different types (e.g., flat file, hierarchal, different “dialects” of SQL, etc.) available from different manufacturers, it is said to be a heterogeneous environment. For purposes of the examples that follow, we will assume that remote database systems are different versions of SQL databases from different manufacturers, such as Oracle, Sybase, Informix, Microsoft, etc.
[0042] Referring now to FIG. 3, the flow of query processing (i.e., query compilation) of incoming queries of one embodiment of the present invention will now be explained. As used herein, the term “query processing” refers to processing necessary to prepare a query for execution and is alternatively referred to as query compilation. Each phase of processing will operate successively as shown by the directional arrow 300 and may use common modules, etc. A brief description of these phases will be described here and a more complete description for many of the phases may be found in “Extensible/Rule Base Query Rewrite Optimization in Starburst” by Hamid Pirahesh et. al as published in conference proceedings for SIGMOD 1992 pages 39-48 and “Query Optimization in the IBM DB2 Family” by Peter Gassner et. al as published in Data Engineering Bulletin 16(4): 4-18 (1993); both of which are hereby incorporated by reference in their entirety.
[0043] Initially, the Parser phase 302 analyzes the original SQL statement received from a one of the clients 200 and constructs an internal Query Graph Model (QGM) that will be used for further processing. The details of the structure of the QGM can be found in Pirahesh at 40-41 (cited previously). Essentially, the QGM is a way of representing a query in an internal data structure. Throughout this application and in the claims appended hereto, reference will be made to rewriting the query or otherwise manipulating the query. These references will apply equally to the actual query text or any internal representation of the query, such as the QGM, unless otherwise specified.
[0044] Next, the Query Global Semantics phase 304 performs checks for constraints, triggers, etc. and transforms them into the corresponding update, delete, and insert operations.
[0045] The Static Pushdown Analysis phase 306 will traverse the QGM and invoke a Pushdown Analysis module 308 as indicated by arrow 310. The Pushdown Analysis module 308 is adapted to analyzing portions of a query and makes reference to locally stored information accessible by the local database system 204 regarding the capabilities of the remote database systems. Essentially, the Pushdown Analysis module 308 returns whether or not the portion is pushdownable and if so what those pushdownable entities would be (i.e., host variable support, expressions, predicates, SQL operations, etc. at the remote system).
[0046] As the Static Pushdown Analysis phase 306 traverses the QGM, it will call the code in the Pushdown Analysis module 308 in a “static” mode. As used herein, the term “static” refers to situations where reference to the Pushdown Analysis module 308 can be predetermined before query processing regardless of the query or any other contextual information, while the term “dynamic” refers to situations where reference to the Pushdown Analysis module 308 occurs depending on the state of query processing and is determined by the particular query being processed or other context sensitive situation. As will be explained hereafter in more detail, two occasions where dynamic pushdown analysis occurs is during the Query Rewrite phase 306 depending on the query and the query rewrite rule being applied and the Optimizer phase 324 depending on which plan or portion of a plan is being generated.
[0047] The Query Rewrite phase 316 applies heuristic rules to rewrite the QGM structure into a more efficient form. The more efficient form is generally based on the structure of SQL itself and has previously been done in a vacuum without considering pushdownability factors that are important to performance in the heterogeneous database environment.
[0048] As applied in database systems generally, queries are rewritten using an extendible rule-based architecture. This architecture consists of a suite of heuristic rules and a rule engine for selecting which rewrite rules to apply. Each rule is organized into a CONDITION portion that must be met in the query or a portion thereof and an ACTION part that is executed. Details of such an architecture can be found in “Extensible/Rule Based Query Rewrite Optimization in Starburst” by Harnid Pirahesh, et al., cited previously.
[0049] For example, given a complex query, the rule engine can use a simple sequential strategy to select the query rewrite rules and then apply them, one by one, to the query. Once a rule is selected, the CONDITION part is tested, if the result of the test turns out to be true, then the ACTION part is executed. As explained previously, the ACTION part rewrites the query into the functionally equivalent but more efficient form.
[0050] Some examples of the query rewrite rules include rules for: removing redundant predicates, removing redundant distinct requirements of query results, transforming a particular subquery (or portion of a query) into a join operation, transforming an intersection into join, transforming outer join into join, transforming OR predicate into IN predicate, transforming IN predicate into join, eager predicate evaluation, eager group by evaluation, etc. Note that the purpose of Query Rewrite rules is to allow the Optimizer phase 324 (explained hereafter) to generate the optimal query plan.
[0051] One aspect of the present invention combines the traditional query rewrite rules with the heterogeneous database system environment. As a general practice, system performance can be enhanced when as much of the query that is pushdownable to a remote database system is considered during the Optimizer phase 324 explained hereafter. This allows the Optimizer phase 324 to consider pushing down the portion of the query or executing it locally and making the decision within context as to which will be optimal. In other words and generally speaking, the more alternatives that can be considered by the Optimizer phase 324 during plan generation the higher performing the processed query. Therefore, the impact of the “pushdownability” of the query both before and after the query rewrite is assessed and the form with the greatest pushdownability is used.
[0052] For one embodiment of the present invention, the process for handling queries needing access to remote data is detailed in the flowchart shown in FIG. 4. Naturally, those queries that need access only to local data need not go through this processing.
[0053] Initially, at step 400, the original QGM (or other representation of the query) is analyzed in its entirety for pushdownability. This is done by the Static Pushdown Analysis phase 306 as it traverses the QGM and invokes the Pushdown Analysis module 308. The results of this analysis is stored within the QGM as part of the inherent query structure and is therefore distributed throughout the query representation at the relevant portions of the query. This allows the pushdownability of a given portion of the query to be readily available.
[0054] At step 402, the availability of any query rewrite rules is determined. If none are available, query processing is continued onto the next phase of processing at step 404.
[0055] If there are query rewrite rules as determined at step 402, the, at step 406, a rule that has been selected the Query Rewrite phase 316 is applied to the query or a portion thereof. If the CONDITION of the rule is not satisfied as determined at step 408, then processing will continue to the next rule selected by the Query Rewrite phase 316 (at step 402).
[0056] If the CONDITION of the rule is satisfied at step 408, then the ACTION of the rule will be executed to rewrite the relevant portion of the query at step 410 and a pushdown analysis is performed on the newly rewritten portion of the query at step 412. This is accomplished by the Query Rewrite phase 316 invoking the code in the Pushdown Analysis module 308 as indicated by arrow 318. This invocation is done in “dynamic” mode as explained previously since it depends on progress made on the query and cannot be predetermined before the query processing.
[0057] As part of the processing done by the Pushdown Analysis module 308, note that those skilled in the art will recognize different ways of ascertaining the capabilities of a remote database system. One embodiment will access information stored in the local database system 204. Other implementations, however, may query the remote system directly.
[0058] The pushdownability results from the Pushdown Analysis module 308 for the rewritten portion of the query are compared to the pushdownability of the original portion of the query at step 414. Since the goal is to rewrite the query into a more efficient form for a heterogeneous database environment, if the pushdownability is less for the rewritten portion than as it stood originally, an UNDO operation is performed at step 416 to place the query back into its original form. An UNDO operation is added to the definition of each rule and contains instructions for reversing the ACTION portion of the rule. Note that databases in a non-heterogeneous environment would not be concerned about pushdownability and not have a need for the additional UNDO operation associated with the rule.
[0059] If the pushdownability for the rewritten portion of the query is not decreased, then the QGM is updated to reflect the new pushdown characteristics at step 418. The net effect is that as rules are applied and the pushdownability of the overall query is changed by rewriting portions of the query, a current model of the pushdown characteristics of the query is maintained.
[0060] After either performing an undo operation at step 416 or updating the QGM with the new pushdown characteristics processing continues to step 402 where a test is made to determine whether additional query rewrite rules are available for application to the query. If so, they are applied as previously explained at step 406. If not, the processing of the query continues on at step 404.
[0061] Note that it is not necessary to use QGM in order to receive the benefits of query rewrite rules that take into account pushdownability. The initial pushdownability of the query is used as a starting point (regardless of form) and as the query (in whatever form) is changed, pushdownability is taken into account in order to determine whether the changed query has decreased pushdownability than the original query. If pushdownability has not been decreased (e.g., remains the same or is more pushdownable), the change in pushdownability is recorded, and if not, the query is restored to its state before the change.
[0062] Note also one effect of the foregoing is to prioritize pushdownability over the benefits derived from the application of the rule. Overall, pushdownability will be maintained or increased regardless of whether the rule is applied and the rule will not be applied (thus foregoing the rule benefit) if pushdownability would be decreased.
[0063] In order to illustrate the effect of rewriting queries that takes into account pushdownability, a number of examples will now be explored. Theses examples will make use of the following table definitions: Employee(empname, empno, deptno) representing an employee having a name, a number, and an associated department number and further having a unique index on empno; Project(prjname, projno, deptno) representing a project having a name, a number, and an associated department number; and Department(deptname, deptno, mgmo) representing a department having a name, a number, and an associated manager number. Unless otherwise specified, the Employee table and Project table will be on remote database system A 208 on server A 206, and the Department table will be on remote database system B 212 on server B 214.
[0064] Transforming IN predicate into JOIN. This example illustrates when the UNDO operation would be applied based upon the rewritten portion of the query having less pushdownability than the original portion. Given a query determines employees whose empno is in (100000, 200000) as illustrated in the SQL query below:
[0065] SELECT *
[0066] FROM Employee x
[0067] WHERE x.empno IN (100000, 200000)
[0068] The “Transforming IN predicate into JOIN” query rewrite rule has an ACTION to rewrite the query shown above to the following query shown below:
[0069] SELECT *
[0070] FROM Employee x, table_function(100000,200000) as y(empno)
[0071] WHERE x.empno=y.empno
[0072] The transformation of the IN to a JOIN gives the query optimizer the ability to order the table_function y and the Employee table x as the outer or the inner of the query plan that is produced. For example, following are two available alternatives for the JOIN: (1) to choose x as outer, y as inner, and for each row of x, use scan to get matching rows from y; and (2) to choose y as outer, x as inner, and for each row of y, use index scan on Employee(empno) to get matching rows from x (because of the unique index, empno, on Employee).
[0073] However, suppose that for remote database system A 208 containing the Employee table, we cannot generate a SQL statement to represent the table_function(100000, 200000). Therefore, the query with the IN predicate that could have been evaluated completely at the remote database can only be partially evaluated at the remote database when it is rewritten. Its pushdownability is now decreased after being rewritten (step 406 and 408). The UNDO operation is now called to change the query back to its original form (step 410). This way, we give the Optimizer phase 324 the original query portion and it will generate alternatives to consider the evaluation of the IN predicate at the remote database or at the local database. Since IN is a filtering predicate, executing this predicate remotely is usually better than executing the predicate locally because it reduces the amount of data that has to be sent over the network
[0074] Transforming Subquery into JOIN. This example illustrates a situation when the pushdownability is unchanged due to rewriting a portion of the query and the rule ACTION is taken in order to receive the benefit of the rule. Given a query that determines employees that belong in departments wherein the department has at least one project, according to SQL query below:
[0075] SELECT x.empno
[0076] FROM Employee x
[0077] WHERE EXISTS(SELECT projno
[0078] FROM Project y
[0079] WHERE x.deptno=y.deptno)
[0080] Note that Employee table and the Project table exist on the same remote database and assume that the remote database system can fully evaluate this operation. The traditional “Transforming Subquery into JOIN” rewrite rule rewrites the above query into the following form:
[0081] SELECT DISTINCT x.empno
[0082] FROM Employee x, Project y
[0083] WHERE x.deptno=y.deptno
[0084] This transformation is good in general because the JOIN between Employee x and Project y gives the optimizer two alternatives instead of one alternative for evaluating the query. The optimizer may evaluate the statement by: (1) choosing x as outer, y as inner, and for each row of x, use scan to get matching rows from y, or (2) choosing y as outer, x as inner, and for each row of y, use scan to get matching rows from x. Now, the rewritten query is still pushdownable to the remote database system, thereby allowing the full benefit of the rule without any decrease in pushdownability.
[0085] Transforming OUTER JOIN to JOIN. Consider the situation where a query first determines employees and associated projects where the employee's deptno does or does not match the project's deptno. Next, the query returns in the results only those matching with project of name ‘Datajoiner’. The SQL statement for such a query is found below:
[0086] SELECT
[0087] FROM Employee x LEFT OUTER JOIN Project y on x.deptno=y.deptno
[0088] WHERE y.projname=‘Datajoiner’
[0089] Assume the above query can not be completely evaluated at remote database system A 208 since it does not support LEFT OUTER JOIN. The traditional “Transforming OUTER JOIN into JOIN” rewrite rule rewrites the above query into the following form:
[0090] SELECT *
[0091] FROM Employee x, Project y
[0092] WHERE x.deptno=y.deptno and y.projname=‘DataJoiner’
[0093] This transformation is good in general because the JOIN between Employee x and Project y gives the optimizer two alternatives instead of one alternative for evaluating the query. The alternatives that the optimizer now has to choose from are: (1) Choose x as outer, y as inner, and for each row of x, use scan to get matching rows from y that satisfies y.projname=‘DataJoiner’; or (2) Choose y as outer, x as inner, and for each row of y that satisfies y.projname=‘DataJoiner’, use scan to get matching rows from x.
[0094] Besides having two choices during optimization, in this case, the rewritten query becomes completely pushdownable to the remote database system A 208 due to the lack of OUTER JOIN. This allows receiving the benefit of the rule (e.g., more choices for the optimizer) and at the same time achieving increased pushdownability to the remote database systems than without the rule.
[0095] By taking into account pushdownability at the Query Rewrite phase 316, new rules can be conceived that will enhance system performance in a heterogeneous database environment. These new rules attempt to minimize the number of remote queries, maximize the portions of the query that can be pushed down remotely, and give the Optimizer phase 324 the maximum number of choices to explore in search of the optimal query plan.
[0096] Grouping UNION operations based on data sources. Given a query which finds out the union set of the deptno from the Employee table, the deptno from Department table, and the deptno from Project table in the following SQL query:
[0097] SELECT x.deptno
[0098] FROM Employee x
[0099] UNION
[0100] SELECT y.deptno
[0101] FROM Department y
[0102] UNION
[0103] SELECT z.deptno
[0104] FROM Project z
[0105] The query as it is written above is neither pushdownable to the remote database system A 208 nor to the remote database system B 212. However, we try to maximize the portions of the query that can be pushed down to the remote databases and limit the search space of the Optimizer phase 324 heuristically. In other words, the Optimizer phase 324 communicates with a remote database system as few times as possible while simultaneously pushing down as much of the query as possible to the remote database systems. The operands of the UNION operator are grouped based on their data sources so that the above query can be rewritten by an appropriate rule as follows:
[0106] (SELECT x.deptno FROM Employee x
[0107] UNION
[0108] SELECT z.deptno FROM Project z)
[0109] UNION
[0110] SELECT y.deptno
[0111] FROM Department y
[0112] To get the final results, we execute two remote SQL statements:
[0113] (1) SELECT x.deptno FROM Employee x
[0114] UNION
[0115] SELECT z.deptno FROM Project z;
[0116] (2) SELECT y.deptno FROM Department y
[0117] Plugging scalar subquery result into a remote SQL statement. Given a query which finds out employees who belong to the department with the highest department number among the departments having a department name of ‘Service’ as described below:
[0118] SELECT x.empno
[0119] FROM Employee x
[0120] WHERE x.deptno=(SELECT MAX(y.deptno)
[0121] FROM Department y
[0122] WHERE y.deptname=‘Service’)
[0123] The above query is not completely pushdownable to either remote database system A 208 or remote database system B 212. If this query were to be optimized in a naive manner, the Employee table would be fetched from the remote database in its entirety and the scalar subquery predicate would be applied locally. However, assuming that remote database system A 208 supports host variables, we can maximize the portions of the query that can be pushed down to the remote database systems and pushdown the scalar subquery predicate to the relevant remote database system. First, determine the results of the scalar subquery by sending a remote SQL statement to the remote database system B 212 as shown below:
[0124] SELECT MAX(y.deptno)
[0125] FROM Department y
[0126] WHERE y.deptname=‘Service’
[0127] Next, the predicate is pushed down to remote database system A 208 by plugging the scalar subquery result into remote SQL statement with a host variable, as shown below:
[0128] SELECT x.empno
[0129] FROM Employee x
[0130] WHERE x.deptno=:hv
[0131] Twinning complex operators. Several complex SQL operators such as INTERSECT, EXCEPT, FULL OUTER JOIN, CUBE, and ROLLUP are usually implemented locally by rewriting the operator into a series of simpler operators for execution. This is due to the lack of direct run time implementation at the local database for these complex operations. Since some remote database systems may support the complex operators directly in their original form, the Optimizer phase 324 should consider both possibilities. Therefore, the original form of the query and the simplified version are kept in the QGM so that the portion of the query is said to be “twinned.”
[0132] For example, assuming that the remote database system A 208 supports the rollup operator a query that determines projects grouped by rollup(projname, projno, deptno), as shown below, can be twinned.
[0133] SELECT *
[0134] FROM Project x
[0135] GROUP BY ROLLUP(x.projname, x.projno, x.deptno)
[0136] Even though the query is completely pushdownable as written to the remote database system A 208, the query rewrite rule will rewrite it due to the lack of direct run time implementation of the ROLLUP operator available locally as shown below:
[0137] WITH (SELECT projname, projno, deptno
[0138] FROM Project
[0139] GROUP BY deptno, projno, projname)
[0140] AS X(C1, C2, C3)
[0141] SELECT X.C1, X.C2, X,C3 FROM X
[0142] UNION All
[0143] WITH (SELECT X.C1, X.C2, NULL
[0144] FROM X
[0145] GROUP BY X.C2, X.C1)
[0146] AS Y(C1, C2, C3)
[0147] SELECT Y.C1, Y.C2, Y.C3 FROM Y
[0148] UNION ALL
[0149] WITH (SELECT Y.C1, NULL, NULL
[0150] FROM Y
[0151] GROUP BY Y.C1)
[0152] AS Z(C1, C2, C3)
[0153] SELECT Z.C1, Z.C2, Z.C3 FROM Z
[0154] UNION ALL
[0155] SELECT NULL, NULL, NULL FROM Z
[0156] In order to let the Optimizer phase 324 have as many opportunities as possible, we keep both the original query as well as its rewritten version. This way the Optimizer phase 324 can select from the following choices:
[0157] 1) Evaluate the original query as it is written at the remote database system because it is originally pushdownable;
[0158] 2) Evaluate the rewritten query as it is rewritten at the remote database system if the rewritten portions are still pushdownable;
[0159] 3) Evaluate some of the rewritten query remotely, some locally. Typically, the more choices or avenues of approach the Optimizer phase 324 can take, the higher quality or more optimal final query plan is selected. The operation of the Optimizer phase 324 is explained in greater detail hereafter.
[0160] Referring back to FIG. 3 and the representation of query processing, a Optimizer Setup phase 320 performs a static pushdown analysis by invoking the code in the Pushdown Analysis module 308 in “static” mode as represented by arrow 322. Essentially, the QGM is traversed once beginning at the bottom of the representation and going up in order to determine which portions of the query can be unambigously (i.e., regardless of plan enumeration) be performed at a remote database system. The processing steps taken by the Optimizer Setup phase 320 is the traditional extent of pushdown analysis taken by many current heterogeneous database systems. It is by nature conservative and will only identify a portion of a query as pushdownable if it will in fact be pushed down regardless of the different query plans that may be generated during the Optimizer phase 324.
[0161] Since a static pushdown analysis was performed previously by the Static Pushdown Analysis phase 306 and maintained by the Query Rewrite phase 316 as individually applied rules changed the pushdownability of the query, the Optimizer Setup phase 320 performs a redundant pushdown analysis to assure that nothing was missed. Note that some implementations may have high enough confidence in the maintenance performed by the Query Rewrite phase 316 that this redundant pushdown analysis may be omitted. The Optimizer Setup phase 320 also prepares the QGM for processing by the Optimizer module 324. Some of this processing includes: (1) splitting non-pushdownable order by or distinct requirements from a pushdownable operation, and (2) assuring that the QGM satisfies the specific implementation requirements of the Optimizer phase 324.
[0162] Next, query processing is handled by the Optimizer phase 324 that will generate a number of different query plans and select the optimal plan for actual execution. This is generally done by generating portions of a query plan and combining the portions in various ways to create “final” plans that will execute the entire query.
[0163] These final plans and the underlying portions of the plan may be built, manipulated, and stored in a variety of ways as will be understood by those skilled in the art. For example, they can be built in a bottom-up tree-like fashion where the plans could be generated, retained for an optimizer to combine and “explore” until all final plans are found. Then the best final plan will be chosen. Despite the wide variety of query optimiztion, it can be generally said that most query optimizers, including the one used in this embodiment or those available for different database systems, will generate plan portions and combine them in some fashion to arrive at final plans on the way to developing the optimal or “best” plan.
[0164] Final query plans or portions of query plans are evaluated on the cost of execution according to a cost model that has a major objective of delivering up the results of the query in the quickest way possible, though other objectives may be used, such as least compute intensive, least network intensive, etc. Things contributing to cost include: CPU speed, I/O speed, network traffic, etc.
[0165] In one embodiment, the details of how this is done is described in Gassner cited previously. The plan is “built” from the bottom up as the optimizer explores, according to internal rules and the state of the different permutations of the plans.
[0166] Referring now to FIG. 5, the processing steps taken to integrate pushdownability into normal query optimization processing is shown. The Optimizer phase 324 will generate a portion of a query plan at step 500. This is according to it's standard processing explained previously.
[0167] The Optimizer phase 324 will continue processing and generate and store the cost parameters for that portion of the query plan based on local execution and store the portion as a candidate to be used for further processing to arrive at final query plans at step 502.
[0168] At step 504, an evaluation of the pushdownability of the query plan portion is made by having the Optimizer phase 324 invoking the Pushdown Analysis module 308 as indicated by arrow 326. The Pushdown Analysis module 308 is adapted to analyzing portions of a query and makes reference to locally stored information (e.g., stored by the local database system 204) regarding the capabilities of the remote database systems. Essentially, the Pushdown Analysis module 308 returns whether or not the portion is pushdownable and if so what those pushdownable entities would be (i.e., host variable support, expressions, predicates, SQL operations, etc. at the remote system).
[0169] After the Pushdown Analysis module 308 determines pushdownability, and if the portion is not pushdownable, the Optimizer phase 324 will continue processing and generate the next portion of a query plan at step 500. Again this is according to general workings of the Optimizer module 324.
[0170] If the Pushdown Analysis module 308 determines that the portion of the plan is pushdownable at step 504, it will further create a duplicate portion of the plan and mark it as pushdownable at step 506. This will be yet another option for the Optimizer phase 324 to evaluate as it determines the optimal plan.
[0171] Finally, at step 508, the Optimizer phase 324 continues its processing and will generate the cost parameters for the duplicate portion of the query plan based on remote execution (“pushdowned”) and store the duplicate portion as a candidate to be used for further processing to arrive at final query plans. Processing then continues to generate the next portion of the query plan at step 500.
[0172] Note that FIG. 5 is a logical diagram and that actual implementation details may dictate variations in actual flow which will be readily understood by those skilled in the art. For example, the local plan generation may occur after the decision regarding pushdownability. Also, remote query plan generation may occur separately from the generation of the local query plan.
[0173] Because unambiguously pushdownable elements of the query plan were handled by the static pushdown analysis in the Optimizer Setup phase 320 previously, there will not be a duplication of pushdown analysis. However, the static approach of checking and indicating various portions of the query that can be executed at the remote database system sometimes cannot accurately capture the portions of the query that are really pushdownable because some of the pushdownabilities are dependent upon how the query is planned for execution. In other words, those situations where pushdownability is independant of plan enumeration will have been caught previously during the Optimizer Setup phase 320. The Optimizer phase 324, according to the steps shown in FIG. 5, is able to explore those situations where the pushdownability is dependant on plan enumeration.
[0174] In order to illustrate the benefits of the latter (where it is uncertain whether the portion of the query is pushdownable or not depending on the plan enumeration), a number of examples are now presented using the same schema as used previously, namely: Employee(empname, empno, deptno) representing an employee having a name, a number, and an associated department number and further having a unique index on empno; Project(prjname, projno, deptno) representing a project having a name, a number, and an associated department number; and Department(deptname, deptno, mgrno) representing a department having a name, a number, and an associated manager number. Again, the Employee table and the Project table will be on remote database system A 208 on server A 206, and the Department table will be on remote database system B 212 on server B 214.
[0175] Simple Example. Now, consider a query that will find all employees who work in some department, as described below in an SQL query:
[0176] SELECT *
[0177] FROM Employee x,Department y
[0178] WHERE x.deptno=y.deptno.
[0179] Referring now to FIGS. 6 and 7, a number of logical partial and final query plans are shown (FIG. 7) and the corresponding portions of plans (FIG. 6) that may be generated by the Optimizer phase 324. However, depending on the capabilities of the underlying remote database systems involved and the context at which the optimization takes place, some of the plans are feasible and others are not.
[0180] For one portion of the original query, all rows of the Employee table x could be retrieved from the remote database system A 208 for use in the JOIN operation on the local database system 204 as represented by box 600. This would likely be a costly way of processing the query if there are other alternatives due to the potentially large amount of data traveling between the database systems. If processing indicated that there was host variable support on remote database system A 208, then, depending on the context of the optimization, the Optimizer component 324 may create a pushdowned version (e.g., at step 506 explained previously) with a predicate using a host variable as represented by box 602.
[0181] Similarly, all rows of the Department table y could be retrieved from the remote database system B 212 for use in the JOIN operation as represented by box 604 and, again depending on the context of the optimization and the availability of host variable support at the remote database system, the Optimizer component 324 may create a pushdowned version (e.g., at step 506 explained previously) with a predicate using a host variable as represented by box 604.
[0182] Referring to the four query plan graphs in FIG. 7, all of the possible permutations conceivable are shown. The query plan graphs representing final query plans shown in FIGS. 7(a)-7(d) could be generated depending on the underlying capabilities of the remote database systems and the context of the compiler generating the query plans.
[0183] Without taking into account the plan dependant pushdownability at all, an optimizer would generate the final query plans illustrated by the query plan graphs of FIGS. 7(a) and 7(b) because static pushdown analysis will indicate the predicate is not pushdownable for safety. Cost parameters would be calculated for each of the plans and the “best” final plan is chosen for actual execution. Cost parameters can be generated for a portion of the plan during final plan generation and accumulated later or a running total of the most optimal or group of most optimal plans can be maintained.
[0184] With a given query, static pushdown analysis will identify portions of query that are unambiguously pushdownable. In other words, regardless of the different plans an optimizer, such as the Optimizer phase 324, may generate, some things, such as a given function will always be pushdownable or not. This is the traditional form of pushdown analysis and is by nature conservative and cannot take advantage of the context known to the optimizer during plan generation. As such, it will miss opportunities for optimal plans.
[0185] Assuming that the query plan illustrated in FIG. 7(b) is executed, its operation would require, first, issuing an SQL statement to remote database system B 212 to retrieve rows of the Department table y, as shown below (step 700 in FIG. 7(b)):
[0186] SELECT *
[0187] FROM Department y
[0188] Next, for each row of y, the local database system 204 would issue an SQL statement to remote database system A 208 to retrieve rows of the Employee table x, as shown below (step 702):
[0189] SELECT *
[0190] FROM Employee x
[0191] Finally, the local database system 204 evaluates the predictate x.deptno=y.deptno locally (step 704) to join the appropriate rows and return the results (step 706).
[0192] The query plan illustrated in FIG. 7(a) would be evaluated in a similar to that of the query plan illustrated in 7(b) and explained previously except the processing order would be reversed as to Employee table x and Department table y in that the Employee table x would be evaluated as “outer” and the Department table y would be evaluated “inner.” In either case, the join predicate and the join operation would be evaluated locally on the local database system 204.
[0193] When using context sensitive pushdown analysis as illustrated previously, whether the predicate x.deptno=y.deptno is pushdownable or not depends upon how we plan to execute the query and the remote database system capability. For example, the two plans that are conceivable are illustrated by plan graphs shown in FIGS. 7(c) and 7(d).
[0194] Assume now, for this example only, that remote database system A 208 supports host variables and remote database system B 212 does not.
[0195] Evaluating the query plan illustrated in FIG. 7(c), its operation would require, first, issuing an SQL statement to remote database system A 208 to retrieve rows of the Employee table x, as shown below (step 710 in FIG. 7(c)):
[0196] SELECT *
[0197] FROM Employee x
[0198] Next, for each row of x, the local database system 204 would issue an SQL statement to remote database system B 212 to make use of host variable capability (step 712). Since remote database B 212 does not support host variables, this plan is not feasible and would not be generated. This will be discovered in one embodiment when the Pushdown Analysis module 308 makes access to the information regarding the remote database system capabilities 312 and would discover that host variables are not supported on remote database system B 212.
[0199] Evaluating now the query plan illustrated in FIG. 7(d), its operation would require, first, issuing an SQL statement to remote database system B 212 to retrieve rows of the Department table y, as shown below (step 720 in FIG. 7(d)):
[0200] SELECT *
[0201] FROM Department y
[0202] Next, for each row of y, the local database system 204 would issue an SQL statement to remote database system A 208 that makes use of host variables so that only relevant rows of the Employee table x are returned (i.e., those that have the same department number), as shown below (step 722):
[0203] SELECT *
[0204] FROM Employee x; x.deptno=:HV
[0205] Finally, the local database system 204 locally joins the appropriate rows (step 724) and returns the results (step 726).
[0206] Since the remote database system A 208 does support host variables, the query plan illustrated in FIG. 7(d) is feasible and is likely to be the best alternative. An optimizer, such as Optimizer phase 324, that follows the processing steps explained above in connection with FIG. 5 and takes into account pushdownability during actual plan generation can generate such alternatives.
[0207] In general, however, there is an exponential number of combinations that an optimizer, such as Optimizer phase 324 will explore for executing the query. This makes it nearly impossible, and at the least, very impractical to capture all the pushdownable information statically for all the combinations prior to actual plan generation. Therefore, many implementations that do not take into account pushdownability dynamically during plan generation by an optimizer may forego opportunities for efficient query plans. This would result in the selection of the best of the query plans shown in FIGS. 7(a) or 7(b) but would not take into account the likely most optimal plan of FIG. 7(d).
[0208] Even assuming that an entire static analysis could be performed on all the different combinations, this would result in a duplication of processing since the actual plan generation would encompass many of the same steps. This, in turn, leads to decreased overall performance in processing the original query.
[0209] In summary, before the query optimization by the Optimizer phase 324, we invoke pushdown analysis for plan independent features and save the results. During the dynamic programming of query plan generation and evaluation by the Optimizer phase 324, the optimizer considers the various permutations of the tables and subquery results to be combined along with the predicates that can be applied on the result. This allows for those pushdownabilities that are plan dependent to be evaluated under the specific context being planned.
[0210] Unnecessary repetitive computing is avoided by evaluating the pushdownability of plan independent feature, while plan dependent pushdown analysis naturally fits with the query optimizer plan generation architecture and enables the Optimizer phase 324 to obtain more accurate information of the portions of the query that are pushdownable.
[0211] Another couple of examples will be helpful to illustrate the benefits of taking into account pushdownability during plan generation by an optimizer, such as Optimizer module 324.
Example 1
[0212] Consider a query that finds employees and their associated projects. Further, the association is such that the project belongs to the department with the highest number managed by the employee, as represented by the SQL statement below:
[0213] SELECT *
[0214] FROM Employee x, Project z
[0215] WHERE z.deptno=(select max(deptno)
[0216] from Department y
[0217] where y.mgrno=x.empno)
[0218] Assume for this example only that all the remote database systems (remote database system A 208 and remote database system B 212) support host variables. Without the context information found during actual plan generation, pure static pushdown analysis would indicate that (1) predicate z.deptno=(select max(deptno) from Department y where y.mgmo=x.empno) is not pushdownable for safety because x, y, and z are from different sources (i.e., remote database systems), and (2) predicate y.mgmo=x.empno is not pushdownable for safety because x and y are from different sources. Then the final best plan based on the static pushdownable information that we might get can is illustrated in FIG. 8(a).
[0219] Referring now to the query plan shown in FIG. 8(a), the query plan will first cause the local database system 204 to issue a SQL statement to remote database system A 208 to retrieve rows of Employee table x (step 800 of FIG. 8(a)):
[0220] SELECT *
[0221] FROM Employee x
[0222] Next, for each row of x, the local database system 204 would issue an SQL statement to remote database system B 212 retrieves rows of the Department table y as shown below(step 802):
[0223] SELECT deptno
[0224] FROM Department y
[0225] The retrieved rows will be filtered using the predicate x.empno=y.mgrno (step 804) and then the highest department number is determined (step 806) locally at the local database system 204.
[0226] These rows will be joined (step 808) locally at local database system 204 as an intermediate processing step. As evaluation continues, the local database system 204 issues a SQL statement to remote database system A 208 to retrieve rows of the Project table z (step 810):
[0227] SELECT *
[0228] FROM Project z
[0229] Finally, the local database system 204 locally joins the appropriate rows from Project table z and the intermediate results (step 812) with the join predicate z.deptno=max(deptno) and returns the results (step 814).
[0230] With context information available during actual plan generation, dynamic pushdown analysis would indicates that (1) predicate x.empno=y.mgrno is pushdownable and (2) predicate z.deptno=(select max(deptno) from Department y where y.mgrno=x.empno) is pushdownable under the plan context illustrated in FIG. 8(b).
[0231] Referring now to the query plan shown in FIG. 8(b), the query plan will first cause the local database system 204 to issue a SQL statement to remote database system A 208 to retrieve rows of Employee table x (step 820 of FIG. 8(b)):
[0232] SELECT *
[0233] FROM Employee x
[0234] Next, for each row of x, the local database system 204 would issue an SQL statement to remote database system B 212 that makes use of host variables to retrieve maximum department number (max(y.deptno)) of the Department table y where the employee number taken from the row of x and plugged into the host variable equals the manager number of a relevant row in the Department table y as shown below(step 822):
[0235] SELECT MAX(deptno)
[0236] FROM Department y
[0237] WHERE y.mgrno=:hv1
[0238] Essentially, the x.empno is bound into remote database system B 212 through a host variable, :hv1. These rows will be joined (step 824) locally at local database system 204 as an intermediate processing step. As evaluation continues, the local database system 204 issues a SQL statement to remote database system A 208 to retrieve rows of the Project table z with a filter predicate (i.e., the intermediate results) as shown below (step 826):
[0239] SELECT *
[0240] FROM Project z
[0241] WHERE z.deptno=:hv2
[0242] Essentially, the result of max(deptno) is bound into remote database system A 208 through a host variable, :hv2. Finally, the local database system 204 locally joins the appropriate rows from Project table z and the intermediate results (step 828) and returns the results (step 830).
[0243] The query plan shown in FIG. 8(b) is usually better than the query plan shown in FIG. 8(a) because more pushdownability of the predicate is identified dynamically under the plan context. A static pushdown analysis would be unable to catch the use of the first and the second host variable because there would be no context as to how the query would be executed.
Example 2
[0244] Consider another query that finds employees and their associated projects. Their association is such that the project belongs to the department with the highest number among those departments that include both the employee and the project, as represented by the SQL statement below:
[0245] SELECT *
[0246] FROM Employee x, Project y,
[0247] (SELECT MAX(deptno), projno
[0248] FROM Project y
[0249] GROUP BY projno) AS z(deptno,projno)
[0250] WHERE x.deptno=y.deptno and y.deptno=z.deptno
[0251] Assume for this example only that the SQL dialect on remote database system A 208 does not support nested table expression. Because of this, the entire query cannot be pushed down to remote database system A 208 even though all the tables are on the same remote database system. With static pushdown analysis, it is cost prohibitive to analyze and store the pushdownabilities for all combinations of input tables. Whereas with dynamic pushdown analysis at plan enumeration level, we can try different combinations to avoid using nested table expression, and hence always get more, and potential better plans.
[0252] The query plan shown in FIG. 9(a) is a plan for the above query that would be generated based on the static pushdown analysis. FIG. 9(b) is a plan for the above query that can only be generated based on the dynamic pushdown analysis during plan generation as explained previously in connection with the processing steps shown in FIG. 5. It beneficially allows one more plan alternative for the Optimizer phase 324 to consider in determining the optimal final query plan.
[0253] Referring now to the query plan shown in FIG. 9(b), the query plan will first cause the local database system 204 to issue a SQL statement to remote database system A 208 to retrieve rows of Employee table x joined with Project table z having common department numbers (step 900 of FIG. 9(b)):
[0254] SELECT *
[0255] FROM Employee x, Project y
[0256] WHERE x.deptno=y.deptno
[0257] Next, an intermediate result containing the single highest department number for each project number will be retrieved by the local database system 204 according to the remote query issued to the remote database system A 208 shown below (step 902):
[0258] SELECT MAX(deptno), projno
[0259] FROM Project
[0260] GROUP BY projno
[0261] Finally, the local database system 204 locally joins the appropriate rows from the intermediate results (step 904) and returns the results (step 906).
[0262] Referring back to FIG. 3, once an optimal query plan is selecte by the Optimizer phase 324, processing is picked by a Remote SQL Generator phase 328 that generates all the remote SQL statements that will be sent to the various remote database systems. Finally, query processing proceeds to a Threaded Code Generator phase 330 that generates the threaded code that intertwines the remote SQL statements, their results, and all necessary local processing by the local database system 204 so that the processed query that is “compiled” can now be executed and give the desired results back to the requesting client.
[0263] To summarize the processing of a heterogeneous query involving tables from multiple remote data sources, an execution plan is selected by a query optimizer, such as Optimizer phase 324, based on a cost model. This execution plan includes two portions: the local portion that is executed locally, and the remote portion that is conveyed by SQL statements that are to be sent to the remote database systems for execution.
[0264] The query optimizer should select the remote portion of plan as it does for the local portion of plan through dynamic programming and global cost model which takes cpu, I/O and network communication costs all into account to achieve global optimal plan. The pushdown analysis is first performed statically to avoid repetitive analysis of plan independent pushdownabilities, and then is performed dynamically for each plan context to achieve accurate and context sensitive results allowing the optimizer to explore better plans.
[0265] While the preferred embodiment of the present invention has been described in detail, it will be understood that modification and adaptations to the embodiment(s) shown may occur to one of skill in the art without departing from the scope of the present invention as set forth in the following claims. Thus, the scope of this invention is to be construed according to the appended claims and not just to the specifics disclosed in the exemplary embodiment or embodiments.
[0266] References in the claims to an element in the singular are not intended to mean “one and only” unless explicitly so stated, but rather “one or more.” All structural and functional equivalents to the elements of the above-described exemplary embodiment that are currently known or later come to be known to those of ordinary skill in the art are expressly incorporated herein by reference and are intended to be encompassed by the present claims. No element, component, or method step in the present disclosure is intended to be dedicated to the public regardless of whether the element, component, or method step is explicitly recited in the claims. No claim element herein is to be construed under the provisions of 35 U.S.C. §112, sixth paragraph, unless the element is expressly recited using the phrase “means for” or “step for.”
Claims
What is claimed is:
1. A method for performing query rewrite in a heterogeneous database environment that preserves optimal pushdownability comprising:
storing the results of a pushdown analysis on a database query, said results including the pushdownability of portions of the database query;
rewriting a portion of the database query;
performing a pushdown analysis on the rewritten portion of the database query; and
if the pushdownability of the rewritten portion is greater than the pushdownability of the original portion, updating the stored results of the pushdown analysis to reflect the pushdownability of the rewritten portion of the database query.
2. A method as recited in claim 1 wherein the stored results of the pushdown analysis is updated if the pushdownability is greater than or equal to the pushdownability of the original portion.
3. A method as recited in claim 1 further comprising undoing the rewriting of the rewritten portion of the database so that it is the same as the original portion if the pushdownability is decreased in the rewritten portion.
4. A method as recited in claim 1 wherein the pushdown analysis comprises determining the capabilities of a remote database system by accessing information locally available.
5. A method as recited in claim 1 wherein the pushdown analysis comprises determining the capabilities of a remote database system by communicating with the remote database system.
6. A computer program product comprising:
a computer usable medium; and
computer readable instructions embodied on said computer useable medium for performing query rewrite in a heterogeneous database environment that preserves optimal pushdownability, the instructions directing a computer to perform the steps of:
storing the results of a pushdown analysis on a database query, said results including the pushdownability of portions of the database query;
rewriting a portion of the database query;
performing a pushdown analysis on the rewritten portion of the database query; and
if the pushdownability of the rewritten portion is greater than the pushdownability of the original portion, updating the stored results of the pushdown analysis to reflect the pushdownability of the rewritten portion of the database query.
7. A computer program product as recited in claim 6 wherein the stored results of the pushdown analysis is updated if the pushdownability is greater than or equal to the pushdownability of the original portion.
8. A computer program product as recited in claim 6 further comprising undoing the rewriting of the rewritten portion of the database so that it is the same as the original portion if the pushdownability is decreased in the rewritten portion.
9. A computer program product as recited in claim 6 wherein the pushdown analysis comprises determining the capabilities of a remote database system by accessing information locally available.
10. A computer program product as recited in claim 6 wherein the pushdown analysis comprises determining the capabilities of a remote database system by communicating with the remote database system.
11. A system for performing query rewrite in a heterogeneous database environment that preserves optimal pushdownability comprising:
means for storing the results of a pushdown analysis on a database query, said results including the pushdownability of portions of the database query;
means for rewriting a portion of the database query;
means for performing a pushdown analysis on the rewritten portion of the database query; and
if the pushdownability of the rewritten portion is greater than the pushdownability of the original portion, means for updating the stored results of the pushdown analysis to reflect the pushdownability of the rewritten portion of the database query.
12. A system as recited in claim 11 wherein the stored results of the pushdown analysis is updated by the means for updating if the pushdownability is greater than or equal to the pushdownability of the original portion.
13. A system as recited in claim 11 further comprising means for undoing the rewriting of the rewritten portion of the database so that it is the same as the original portion if the pushdownability is decreased in the rewritten portion.
14. A system as recited in claim 11 wherein the pushdown analysis comprises means for determining the capabilities of a remote database system by a means for accessing information locally available.
15. A system as recited in claim 11 wherein the pushdown analysis comprises a means for determining the capabilities of a remote database system by a means for communicating with the remote database system.
16. A method for performing query rewrite in a heterogeneous database environment that preserves optimal pushdownability comprising:
rewriting a portion of the database query;
performing a pushdown analysis on the rewritten portion of the database query; and
if the pushdownability of the rewritten portion is less than the pushdownability of the original portion, undoing the rewrite of the portion of the database query.
17. A computer program product comprising a computer usable medium having computer-executable instructions embodied therein to perform the method as recited in claim 16.
Patent Citations (13)
| Patent | Date | Inventor | Cited By |
|---|---|---|---|
| US5544355(A) | 1996-08-01 | Chaudhuri et al. | Examiner |
| US5548755(A) | 1996-08-01 | Leung et al. | Examiner |
| US5590324(A) | 1996-12-01 | Leung et al. | Examiner |
| US5797136(A) | 1998-08-01 | Boyer et al. | Examiner |
| US5826077(A) | 1998-10-01 | Blakeley et al. | Examiner |
| US6032143(A) | 2000-02-01 | Leung et al. | Examiner |
| US6088524(A) | 2000-07-01 | Levy et al. | Examiner |
| US6122627(A) | 2000-09-01 | Carey et al. | Examiner |
| US6134540(A) | 2000-10-01 | Carey et al. | Examiner |
| US6199063(B1) | 2001-03-01 | Colby et al. | Examiner |
| US6275818(B1) | 2001-08-01 | Subramanian et al. | Examiner |
| US6285997(B1) | 2001-09-01 | Carey et al. | Examiner |
| US6334128(B1) | 2001-12-01 | Norcott et al. | Examiner |
Non-Patent Literature (10)
- Hamid Pirahesh et al., “Extensible/Rule Base Query Rewrite Optimization in Starburst”, Conference Proceedings for SIGMOD 1992, pp. 39-48.Applicant
- Peter Gassner et al., “Query Optimization in the IBM DB2 Family” Data Engineering Bulletin 16 (4): 0-13 (1994).Applicant
- Oracle 8i™ Distributed Data Access and Oracle Transparent Gateway®, Feb. 1999 pp. 1-4.Applicant
- EDA Server, http://www.ibi.com/products/eda/server.htm.Applicant
- Oracle 8i Distributed Database Systems Release 8.1.5, A67784-01, http://technet.oracle.com/doc/server.815/a67784/ds_chl.htm.Applicant
- Optimizing Distributed Queries, SQL Server 7.0 Query Processor White Paper, Microsoft®, mk:@MSITStore:C:/TEMP/SQLBOL. CHM: : /html/ tun 1 103 . htm.Applicant
- Laura M. Haas, et al., “Optimizing Queries across Diverse Data Sources,” IBM Almaden Research Center, VLDB 1997 Conference, Athens, Greece, Feb. 21, 1997.Applicant
- Weimin Du, et al., “Query Optimization in Heterogenous DBMS,” Proceedings of the 18th VLDB Conference, Vancouver, British Columbia, Canada 1992, pp. 277-291.Applicant
- Fernando de Ferreira Rezende, “The Heterogeneity Problem and Middleware Technology: Experiences with and Performance of Database Gateways,” Proceedings of the 24th VLDB Conference, New York, 1998.Applicant
- Oracle7 Server Concepts Manual, http://teku.pspt.fi/˜jkajala/oradoc/DOC/server/doc/ / SCN73/ch13.htm.Applicant