System and method for optimizing queries

• US 8,554,760 B2
• Filed: 10/07/2009
• Issued: 10/08/2013
• Est. Priority Date: 10/07/2009
• Status: Active Grant

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: Subbiah, Suresh, Fredriksen, Lars, Krishna, Murali Mallela, Zeller, Hansjorg
• Primary Examiner(s): PYO, MONICA M
• Application Number:
  US12/574,911
• Publication Number:
  US 20110082856A1
• Time in Patent Office: 1,462 Days
• Field of Search: 707/713, 707/714, 707/999.002
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

There is provided a computer-implemented method of optimizing a query. An exemplary method comprises receiving a first query configured to generate a result set. The query may comprise an operation for an outer table, a first predicate, and a second predicate. The second predicate may comprise a subquery correlated to the operation. Additionally, the first predicate and the second predicate may be disjunctive. The exemplary method also comprises generating a first query tree representing the first query, and generating a second query tree representing a second query. The second query may be configured to generate the result set, and may not comprise the subquery. The exemplary method additionally comprises generating a query plan based on the second query tree.

Claims

1. A computer-implemented method of optimizing a query, comprising:

receiving a first query configured to generate a result set, wherein the first query comprises an operation for an outer table, a first predicate, and a second predicate comprising a subquery correlating an inner table to the outer table, wherein the first predicate and the second predicate are disjunctive, wherein the subquery comprises an operation for the inner table and a correlation predicate correlating the inner table and the outer table, and wherein the second query comprises a left outer join operation between the inner table and the outer table using the correlation predicate;

generating a first query tree representing the first query;

generating a second query tree representing a second query configured to generate the result set, wherein the second query does not comprise the subquery, and wherein the second query comprises a group by operation configured to generate one row for each row in the outer table, and wherein the group by operation comprises a having clause comprising the first predicate and the second predicate; and

generating a query plan based on the second query tree.

2. The method recited in claim 1 wherein generating the second query tree comprises removing the subquery from the second predicate, and wherein the second query tree comprises the operation, the first predicate, and the second predicate.

3. The method recited in claim 1, wherein the one row comprises a null value for the inner table, and wherein the second predicate is not null-rejecting.

4. The method recited in claim 3, wherein the second predicate comprises an aggregate function that is not null-rejecting.

5. The method recited in claim 4, wherein the aggregate function is a count operation comprising a column wildcard.

6. The method recited in claim 3, wherein the second predicate comprises a constraint that is not null-rejecting.

7. The method recited in claim 1, wherein the query plan comprises an operation with a processing cost at a factor of a number of rows in the outer table plus a number of rows in the inner table.

8. The method recited in claim 3, wherein the having clause comprises a third predicate that is conjunctive with the second predicate, wherein the third predicate comprises a FALSE value when the null value results from a non-correlated row between the inner table and the outer table.

9. A computer system for optimizing a query in a database management system (DBMS), the computer system comprising:

a processor that is adapted to execute stored instructions; and

a memory device that stores instructions, the memory device comprising;

computer-implemented code adapted to receive a first query configured to generate a result set, wherein the query comprises an operation for an outer table, a first predicate, and a second predicate, wherein the first predicate comprises a first subquery correlating an inner table to the outer table, wherein the first predicate and the second predicate are disjunctive, wherein the first subquery comprises an operation for the inner table and a correlation predicate correlating the inner table and the outer table, and wherein the second query comprises a left outer join operation between the inner table and the outer table using the correlation predicate;

computer-implemented code adapted to generate a first query tree representing the first query;

computer-implemented code adapted to generate a second query tree representing a second query configured to generate the result set, wherein the second query does not comprise the first subquery, and wherein the second query comprises a group by operation configured to generate one row for each row in the outer table, and wherein the group by operation comprises a having clause comprising the first predicate and the second predicate; and

computer-implemented code adapted to generate a query plan based on the second query tree.

10. The computer system recited in claim 9, wherein the query plan comprises an operation with a processing cost at a factor of a number of rows in the outer table plus a number of rows in the inner table.

11. The computer system recited in claim 9, wherein the computer-implemented code adapted to generate the second query tree comprises computer-implemented code adapted to remove the first subquery from the second predicate, and wherein the second query tree comprises the operation, the first predicate, and the second predicate.

12. The computer system recited in claim 9, wherein the one row comprises a null value for the inner table, and wherein the second predicate is not null-rejecting.

13. The computer system recited in claim 12, wherein the having clause comprises a third predicate that is conjunctive with the second predicate, wherein the third predicate comprises a FALSE value when the null value results from a non-correlated row between the inner table and the outer table.

14. A non-transitory machine-readable medium that stores machine-readable instructions executable by a processor to optimize a query in a database management system (DBMS), the tangible, machine-readable medium comprising:

machine-readable instructions that, when executed by the processor, receive a first query configured to generate a result set, wherein the first query comprises an operation for an outer table, a first predicate, and a second predicate comprising a subquery correlated to the operation, wherein the first predicate and the second predicate are disjunctive, wherein the subquery comprises an operation for the inner table and a correlation predicate correlating the inner table and the outer table, and wherein the second query comprises a left outer join operation between the inner table and the outer table using the correlation predicate;

machine-readable instructions that, when executed by the processor, generate a first query tree representing the first query;

machine-readable instructions that, when executed by the processor, generate a second query tree representing a second query configured to generate the result set, wherein the second query does not comprise the subquery, and wherein the second query comprises a group by operation configured to generate one row for each row in the outer table, and wherein the group by operation comprises a having clause comprising the first predicate and the second predicate; and

machine-readable instructions that, when executed by the processor, generate a query plan based on the second query tree.

15. The non-transitory machine-readable medium recited in claim 14, wherein the one row comprises a null value for an inner table of the first query, and wherein the second predicate is not null-rejecting.

16. The non-transitory machine-readable medium recited in claim 15, wherein the having clause comprises a third predicate that is conjunctive with the second predicate, wherein the third predicate comprises a FALSE value when the null value results from a non-correlated row between an inner table of the first query and an outer table of the first query.

17. The non-transitory machine-readable medium recited in claim 14, wherein the machine-readable instructions that generate the second query tree comprise machine-readable instructions that, when executed by the processor, remove the subquery from the second predicate, wherein the second query tree comprises the operation, the first predicate, and the second predicate.

18. The non-transitory machine-readable medium recited in claim 14, wherein the query plan comprises an operation with a processing cost at a factor of a number of rows of the outer table of the first query plus a number of rows of an inner table of the first query.