Query Optimization by Specifying Path-Based Predicate Evaluation in a Path-Based Query Operator

• US 20100312756A1
• Filed: 06/04/2009
• Published: 12/09/2010
• 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, Agarwal, Nipun, Sthanikam, Balasubramanyam
• Granted Patent: US 8,161,035 B2
• Time in Patent Office: Days
• US Class Current: 707/693
• 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:

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 one or more criteria, wherein the set of path-based query operators comprises a first path-based query operator and a second path-based query operator;

wherein the one or more criteria includes;

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

b) the row filter condition contains a path-based operand that references the 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 set of path-based query operators meet the one or more 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 the one or more criteria further comprises:

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 the one or more criteria further includes the driving operand of the second path-based query operator is based on the driving operand of the first path-based query operator.