Reducing query response time using tree balancing

• US 5,694,591 A
• Filed: 05/02/1995
• Issued: 12/02/1997
• Est. Priority Date: 05/02/1995
• Status: Expired due to Term

• Current Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Original Assignee: Hewlett-Packard Company (HP Inc.)
• Inventors: Dayal, Umeshwar, Shan, Ming-Chien, Du, Weimin
• Primary Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US08/434,101
• Time in Patent Office: 945 Days
• Field of Search: 395/602, 395/610, 395/604
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method for optimizing data retrieval from a multidatabase system by restructuring a database query tree to optimize query response time in a two step optimization process. First, the query tree is transformed into a left deep join tree having a root query, a plurality of subordinate (descendant) query nodes and a plurality of table nodes, each subordinate query node having a left child subtree and a right child subtree. This transformation is usually the result of a first optimization scheme such as System-R. A response time for the root query and for each of the plurality of subordinate query nodes is estimated and access response times to each table node and subtree are estimated. Then, this data is utilized in the balancing of the left deep join query tree so that the cost for access to each left child subtree is substantially equal to the cost for the right child subtree. This balancing step encompasses the second phase of the query tree optimization process and includes using transformation processes such top-down, bottom-up, and a hybrid of the first two. Finally, the query is executed in a relational database to retrieve data responsive to the query in accordance with an execution plan operating according to the balanced query tree.

Claims

1. In a multidatabase system (MDBS) for accessing data in a plurality of relational computer databases on a distributed network of database machines, a method of structuring a database query to reduce query response time using a two phase optimization, comprising the steps of:

inputting the database query;

as a first phase, optimization,

transforming the database query into a left deep join query tree having a root query, a plurality of subordinate (descendant) query nodes and a plurality of table nodes, each subordinate query node having a left child subtree and a right child subtree;

as a second phase optimization.

estimating a response time for the root query and for each of the plurality of subordinate query nodes;

measuring a response time to access each of the plurality of table nodes and a cost to access each subtree;

balancing the left deep join query tree into a bushy query tree, wherein the cost for each left child subtree is substantially equal to the cost for the right child subtree; and

executing the database query in one or more of said relational databases in accordance with an execution plan operating according to the balanced bushy query tree.

2. The method of claim 1, wherein the left deep join query tree includes a plurality of join nodes, at least one of which links two data tables within the query tree, and child subtrees depending from the join nodes, each node being defined by a data structure stored in memory in the MDBS and comprising data defining the response time of each corresponding table and cost for each corresponding join node, and a pointer identified with the data for linking it to its corresponding child subtrees, wherein the step of balancing further comprises the step of changing the pointer link within the corresponding child subtree.

3. The method of claim 1, wherein the step of balancing further comprises the steps of

selecting a root node of the left deep join query tree having a corresponding right child subtree and a left child subtree, each with a corresponding cost;

characterizing the root node as an upper anchor node (UAN);

reading a response time of the corresponding right child subtree and a response time of the corresponding left child subtree of the root node;

comparing the corresponding right child tree response time with the corresponding left child tree response time; and

transforming a left child node into a new root node of the transformed tree if the cost of the left child subtree is greater than that of the right child subtree.

4. The method of claim 1, wherein the step of balancing further comprises the steps of

identifying a lowest leftmost node on the left deep join query tree, wherein the node'"'"'s left child subtree and right child subtree are not both single table nodes and the cost of the node'"'"'s left child subtree is greater than the cost of the node'"'"'s right child subtree;

characterizing the identified lowest leftmost node as an upper anchor node (UAN);

comparing the right child tree response time with the left child tree response time; and

transforming the identified lowest leftmost node into a new child node of the left child node of the identified lowest leftmost node.

5. The method of claim 1, wherein the step of balancing further comprises the steps of

selecting a first node having first left and right child subtrees, said first node being initially defined as the grandparent of the leftmost leaf node;

selecting a second node having second left and right child subtrees, said second node being initially defined as the root of the left deep join query tree;

comparing the cost for said first and second left and right subtrees;

defining an upper anchor node (UAN), said UAN being set to said first node only if the cost of the first left child subtree is smaller than the response time of the second right child subtree, otherwise said UAN is set to said second node; and

transforming the UAN.

6. An apparatus, comprising:

(A) a storage medium;

(B) a computer executable program stored in the storage medium that structures a database query using a two phase optimization to reduce query response time for accessing data in a plurality of relational databases on a distributed network, comprising

(a) a first set of instructions that input the database query;

as a first phase optimization,

(b) a second set of instructions that transform the database query into a left deep join query tree having a root query, a plurality of subordinate (descendant) query nodes and a plurality of table nodes, each subordinate query node having a left child subtree and a right child subtree;

as a second phase optimization,

(c) a third set of instructions that estimate a response time for the root query and for each of the plurality of subordinate query nodes;

(d) a fourth set of instructions that measure a response time to access each of the plurality of table nodes and a cost to access each subtree;

(e) a fifth set of instructions that balance the left deep join query tree into a bushy query tree, wherein the cost for each left child subtree is substantially equal to the cost for the right child subtree; and

(f) a sixth set of instructions that execute the database query in one or more of said relational databases in accordance with an execution plan operating according to the balanced bushy query tree.

7. The apparatus of claim 6, wherein the left deep join query tree includes (1) a plurality of join nodes, at least one of which links two data tables, (2) child subtrees depending from the join nodes, each node being defined by a data structure stored in a memory and comprising data defining the response time of each corresponding table and cost for each corresponding join node, and (3) a pointer identified with the data for linking it to its corresponding child subtrees.

8. The apparatus of claim 7, wherein the fifth set of instructions further comprises instructions that change the pointer link within the corresponding child subtree.

9. The apparatus of claim 6, wherein the fifth set of instructions further comprises

instructions that select a root node of the left deep join query tree having a corresponding right child subtree and a left child subtree, each with a corresponding cost;

instructions that characterize the root node as an upper anchor node (UAN);

instructions that read a response time of the corresponding right child subtree and a response time of the corresponding left child subtree of the root node;

instructions that compare the corresponding right child tree response time with the corresponding left child tree response time; and

instructions that transform a left child node into a new root node of the transformed tree if the cost of the left child subtree is greater than that of the right child subtree.

10. The apparatus of claim 6, wherein the fifth set of instructions further comprises

instructions that identify a lowest leftmost node on the left deep join query tree, wherein the node'"'"'s left child subtree and right child subtree are not both single table nodes and the cost of the node'"'"'s left child subtree is greater than the cost of the node'"'"'s right child subtree;

instructions that characterize the identified lowest leftmost node as an upper anchor node (UAN);

instructions that compare the right child tree response time with the left child tree response time; and

instructions that transform the identified lowest leftmost node into a new child node of the left child node of the identified lowest leftmost node.

11. The apparatus of claim 6, wherein the fifth set of instructions further comprises

instructions that select a first node having first left and right child subtrees, said first node being initially defined as the grandparent of the leftmost leaf node;

instructions that select a second node having second left and right child subtrees, said second node being initially defined as the root of the left deep join query tree;

instructions that compare the cost for said first and second left and right subtrees;

instructions that define an upper anchor node (UAN), said UAN being set to said first node only if the cost of the first left child subtree is smaller than the response time of the second right child subtree, otherwise said UAN is set to said second node; and

instructions that transform the UAN.