Query optimization by specifying path-based predicate evaluation in a path-based query operator

• US 8,161,035 B2
• Filed: 06/04/2009
• Issued: 04/17/2012
• Est. Priority Date: 06/04/2009
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Zhang, Ning, Idicula, Sam, Sthanikam, Balasubramanyam, Agarwal, Nipun
• Primary Examiner(s): Timblin, Robert
• Assistant Examiner(s): BOWEN, RICHARD L
• Application Number:
  US12/478,147
• Publication Number:
  US 20100312756A1
• Time in Patent Office: 1,048 Days
• Field of Search: 707/713, 707/736
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/8373 Query execution

Abstract

The approaches described herein provide an efficient way for a database server to process certain kinds of queries over XML data stored in an object-relational database that require the evaluation of a predicate expression with one or more path-based operands. A predicate expression part of a XQuery or SQL WHERE clause that returns a boolean value. A database server first determines whether the query qualifies for this particular kind of optimization, then rewrites the query using an enhanced query operator syntax for specifying the predicate expression to be evaluated. The enhanced query operator subsumes the work of a second path-based query operator, resulting in the suppression of the WHERE EXISTS subquery. The rewritten query operator is used to generate a query execution plan that provides for several query execution optimizations.

Claims

1. A method comprising steps of:

receiving a query against a collection of XML documents stored in one or more tables of a database, wherein the query contains a row filter condition;

determining that the query contains a set of path-based query operators that satisfy a set of criteria, wherein the set of path-based query operators comprises a first path-based query operator and a second path-based query operator;

wherein satisfying said set of criteria requires that;

a) the row output from the first path-based query operator is the row input to the second path-based operator;

b) the row filter condition contains a path-based operand that references the row output from the second path-based query operator; and

c) the output from the second path-based query operator is not returned in the results for the query;

in response to determining that the query contains a set of path-based query operators that satisfy the criteria, rewriting the query by eliminating the second path-based query operator and re-writing the first path-based query operator to only return rows that satisfy the row filter condition;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, wherein satisfying said set of criteria further requires that:

the output from the second path-based query operator is only referenced by the row filter condition.

3. The method of claim 1, further comprising:

rewriting the first path-based query operator with a plurality of parameters, wherein one or more parameters of the plurality of parameters specify the row filter condition.

4. The method of claim 1, wherein the value of the path-based operand of the row filter condition is computed by the first path-based query operator and placed in a column of the output from the first path-based query operator.

5. The method of claim 1, wherein the row filter condition is evaluated within the first path-based query operator, and only rows that satisfy the row filter condition are returned as output from the first path-based query operator.

6. The method of claim 5, wherein the query is rewritten without an SQL WHERE clause.

7. The method of claim 1, wherein the query is expressed as an XQuery FLOWR expression and rewritten using path-based query operators.

8. The method of claim 1, wherein the query is expressed as an SQL expression including an XMLTABLE expression and rewritten using path-based query operators.

9. The method of claim 1, wherein satisfying said set criteria further requires that the driving operand of the second path-based query operator is based on the driving operand of the first path-based query operator.

10. A machine-readable storage medium storing instructions, wherein the instructions, when executed by one or more processors, cause execution of steps comprising:

receiving a query against a collection of XML documents stored in one or more tables of a database, wherein the query contains a row filter condition;

determining that the query contains a set of path-based query operators that satisfy a set of criteria, wherein the set of path-based query operators comprises a first path-based query operator and a second path-based query operator;

wherein satisfying said set of criteria requires that;

a) the row output from the first path-based query operator is the row input to the second path-based operator;

b) the row filter condition contains a path-based operand that references the row output from the second path-based query operator; and

c) the output from the second path-based query operator is not returned in the results for the query;

in response to determining that the query contains a set of path-based query operators that satisfy the set of criteria, rewriting the query by eliminating the second path-based query operator and re-writing the first path-based query operator to only return rows that satisfy the row filter condition;

wherein the method is performed by one or more computing devices.

11. The machine-readable storage medium of claim 10, wherein satisfying said set of criteria further requires that:

the output from the second path-based query operator is only referenced by the row filter condition.

12. The machine-readable storage medium of claim 10, the steps further comprising:

rewriting the first path-based query operator with a plurality of parameters, wherein one or more parameters of the plurality of parameters specify the row filter condition.

13. The machine-readable storage medium of claim 10, wherein the value of the path-based operand of the row filter condition is computed by the first path-based query operator and placed in a column of the output from the first path-based query operator.

14. The machine-readable storage medium of claim 10, wherein the row filter condition is evaluated within the first path-based query operator, and only rows that satisfy the row filter condition are returned as output from the first path-based query operator.

15. The machine-readable storage medium of claim 10, wherein the query is rewritten without an SQL WHERE clause.

16. The machine-readable storage medium of claim 10, wherein the query is expressed as an XQuery FLOWR expression and rewritten using path-based query operators.

17. The machine-readable storage medium of claim 10, wherein the query is expressed as an SQL expression including an XMLTABLE expression and rewritten using path-based query operators.

18. The machine-readable storage medium of claim 10, wherein satisfying said set of criteria further requires that the driving operand of the second path-based query operator is based on the driving operand of the first path-based query operator.