Memory structure and method for tuning a database statement using a join-tree data structure representation, including selectivity factors, of a master table and detail table

• US 5,761,654 A
• Filed: 06/05/1996
• Issued: 06/02/1998
• Est. Priority Date: 06/05/1996
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Tow, Daniel S.
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US08/659,158
• Time in Patent Office: 727 Days
• Field of Search: 395/602, 395/603, 395/604, 395/611, 707/2, 707/3, 707/4, 707/101
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/24545 Selectivity estimation or determination
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A method of constructing an aid to tuning of database statements comprises a data structure (stored in a memory on a computer system) which compactly represents that information which is needed about a database statement to determine the optimal series of operations (e.g., table data fetches) needed to execute the statement. The information includes the relationships between tables joined in the statement and the selectivities of logical conditions applied to rows in those tables.

Claims

1. A machine-executed method of creating a data structure in a memory device, for use in selecting an execution plan for a data access statement that specifies (i) a plurality of tables and (ii) a plurality of join conditions, each of said join conditions specifying a relationship between (x) a table that uses a key as a primary key, referred to as a master table, and (y) a table that uses a corresponding key as a foreign key, referred to as a detail table, said method comprising:

(a) defining a set of nodes respectively representing the tables;

(b) defining a set of directional links between pairs of nodes each of which represents a master-detail relationship between a detail table and a corresponding master table;

(c) defining, in a memory device, a data structure, referred to as a join tree, comprising a representation of the nodes and their directional links;

(d) associating with the join tree a representation of a set of properties of the nodes and links, comprising;

(1) for each node, a set of zero or more selectivity factors, each selectivity factor indicating the expected fraction of rows in the table represented by the node that satisfy one or more logical conditions set forth in the data access statement;

(2) for each directional link associated with a detail table and a master table, (A) the ratio of (i) the number of distinct rows satisfying the join statement in the detail table A to (ii) the number of distinct rows satisfying the join statement in the master table, and (B) the probability that a row in the detail table will have a corresponding row in the master table.

2. The method of claim 1, wherein the data access statement is an SQL statement.

3. The method of claim 1, wherein the data structure comprises one or more selectivity factors, and at least one of said one or more selectivity factors is a filter.

5. A memory device containing a data structure created in accordance with a specified one of claims 1, 2, 3, and 4.

6. A program storage device readable by the machine of a specified one of claims 1-4 and 11-15 and encoding a program of instructions that, when executed by the machine, results in the performance of the operations said forth in said specified one claim.

4. A machine-executed method of creating a data structure in a memory device for use in selecting an execution plan for an SQL statement that specifies (i) a plurality of tables and (ii) a plurality of join conditions each of which specifies a relationship between (x) a table that uses a key as a primary key, referred to as a master table, and (y) a table that uses a corresponding key as a foreign key, referred to as a detail table, said method comprising:

(a) defining a set of nodes respectively representing the tables;

(b) defining a set of directional links between pairs of nodes each of which represents a master-detail relationship between a detail table and a corresponding master table;

(c) defining, in said memory device, a data structure, referred to as a join tree, comprising a representation of the nodes and their directional links;

(d) associating with the join tree a representation of a set of properties of the nodes and links, comprising;

(1) for each node, a set of zero or more filters, each filter indicating the expected fraction of rows in the table represented by the node that satisfy one or more logical conditions set forth in the SQL statement;

(2) for each directional link associated with a detail table and a master table, (A) the ratio of (i) the number of distinct rows satisfying the join statement in the detail table A to (ii) the number of distinct rows satisfying the join statement in the master table, and (B) the probability that a row in the detail table will have a corresponding row in the master table.

7. A machine-readable memory device encoding a map of a data access statement, said data access statement specifying (i) a plurality of tables and (ii) a plurality of join conditions, each of said join conditions specifying a relationship between (x) a table that uses a key as a primary key, referred to as a master table, and (y) a table that uses a corresponding key as a foreign key, referred to as a detail table, said map comprising respective representations of:

(a) a set of nodes respectively representing the tables;

(b) a set of directional links between pairs of nodes, each directional link representing a master-detail relationship between a detail table and a corresponding master table;

(c) a set of properties of the nodes and links, comprising;

(1) for each node, a set of zero or more selectivity factors, each selectivity factor indicating the expected fraction of rows in the table represented by the node that satisfy one or more logical conditions set forth in the data access statement;

(2) for each directional link associated with a detail table and a master table, (A) the ratio of (i) the number of distinct rows satisfying the join statement in the detail table A to (ii) the number of distinct rows satisfying the join statement in the master table, and (B) the probability that a row in the detail table will have a corresponding row in the master table.

8. The memory device of claim 7, wherein the data access statement is an SQL statement.

9. The memory device of claim 7, wherein the data structure comprises one or more selectivity factors, and at least one of said one or more selectivity factors is a filter.

11. A computer-executed method of generating a human-readable graphic diagram representing an input data access statement said input data access statement being encoded in a data structure in a memory device in accordance with claim 7.

12. A computer-executed method of constructing a tuned data access statement as a replacement for an input data access statement, said input data access statement being encoded in a data structure in a memory device in accordance with claim 7, said data structure comprising (i) a map representing a plurality of tables and one or more links between pairs of tables, each said link having a direction referred to as a downhill direction and an opposite direction referred to as an uphill direction, and (ii) for each of at least two of said plurality of tables, a selectivity factor respectively associated with said two tables, said method comprising:

(a) defining one selectivity factor as the best selectivity factor;

(b) inserting into the tuned data access statement a reference to the table associated with the best selectivity factor;

(c) searching downhill in the map for a best-remaining selectivity factor;

(d) if the best-remaining selectivity factor is found, then;

(1) inserting into the tuned data access statement a reference to the table associated with the best-remaining selectivity factor, and

(2) repeating the operations described in paragraphs (c) and (d);

(e) else searching uphill in the map for a best-remaining selectivity factor and repeating the operations described in paragraphs (c) and (d).

13. The method of claim 12, wherein the data access statement is an SQL statement.

14. The method of claim 12, wherein at least one of said one or more selectivity factors is a filter.

10. A machine-readable memory device encoding a map of an SQL statement, said SQL statement specifying (i) a plurality of tables and (ii) a plurality of join conditions, each of said join conditions specifying a relationship between (x) a table that uses a key as a primary key, referred to as a master table, and (y) a table that uses a corresponding key as a foreign key, referred to as a detail table, said map comprising respective representations of:

(a) a set of nodes respectively representing the tables;

(b) a set of directional links between pairs of nodes, each directional link representing a master-detail relationship between a detail table and a corresponding master table;

(d) a set of properties of the nodes and links, comprising;

(1) for each node, a set of zero or more filters, each filter indicating the expected fraction of rows in the table represented by the node that satisfy one or more logical conditions set forth in the SQL statement;

(2) for each directional link associated with a detail table and a master table, (A) the ratio of (i) the number of distinct rows satisfying the join statement in the detail table A to (ii) the number of distinct rows satisfying the join statement in the master table, and (B) the probability that a row in the detail table will have a corresponding row in the master table.

15. A computer-executed method of constructing a tuned SQL statement as a replacement for an input SQL statement, said input SQL statement being encoded in a data structure in a memory device in accordance with claim 10, said data structure comprising (i) a map representing a plurality of tables and one or more links between pairs of tables, each said link having a direction referred to as a downhill direction and an opposite direction referred to as an uphill direction, and (ii) for at least two of said plurality of tables, a filter respectively associated with said two tables, said method comprising:

(a) defining one filter as the best filter;

(b) inserting into the tuned SQL statement a reference to the table associated with the best filter;

(c) searching downhill in the map for a best-remaining filter;

(d) if the best-remaining filter is found, then;

(1) inserting into the tuned SQL statement a reference to the table associated with the best-remaining filter, and

(2) repeating the operations described in paragraphs (c) and (d);

(e) else searching uphill in the map for a best-remaining filter and repeating the operations described in paragraphs (c) and (d).