Optimization of queries over XML views that are based on union all operators

• US 7,685,150 B2
• Filed: 09/27/2005
• Issued: 03/23/2010
• Est. Priority Date: 04/19/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Zhang, Hui, Manikutty, Anand, Arora, Vikas, Angrish, Rohan, Warner, James W., Krishnaprasad, Muralidhar, Liu, Zhen Hua
• Primary Examiner(s): MIZRAHI, DIANE D
• Application Number:
  US11/237,286
• Publication Number:
  US 20060235840A1
• Time in Patent Office: 1,638 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-206
• US Class Current: 1/1
• CPC Class Codes: G06F 16/8365 Query optimisation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Optimization is provided for database statements involving XML data, e.g., XPath and XQuery, which operate over views that use aggregate set operators, e.g., UNION ALL. As part of a “view merge” stage of query transformation, the query that operates over the view is merged with a query that defines the view. One or more expressions (e.g., operators, predicates, virtual table constructs) from the query that operates over the view are then “pushed down” to operate on the individual underlying XML data constructs. “Branch elimination” is performed based on the structure of the view and the query acting over the view, based on which it is possible to determine whether any of the sub-queries, if executed, would result in the return of zero rows. If so, then such branches are eliminated from the transformed query, leaving a reduced set of data containers on which to execute the query.

Claims

1. A method comprising:

a database server running on one or more computers;

said database server receiving a first database statement;

wherein the first database statement requires access to a view defined as combined results of a set of database statements;

wherein the first database statement includes an expression that operates on an XML construct; and

said database server generating a second database statement, based on the first database statement and the view, that includes a modified version of the set of database statements in the distributive form and rewritten to include the expression that operates on the XML construct.

2. The method of claim 1, further comprising:

determining whether any statement, from the set of database statements included in the second database statement, if executed, would return no data; and

for any statement that would return no data, eliminating the statement from the second database statement, generating a reduced database statement corresponding to the first database statement.

3. The method of claim 2, further comprising:

rewriting each expression, included in the set of database statements from the reduced database statement, as a particular relational database expression that does not include the corresponding expression that operates on the XML construct.

4. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

5. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

6. The method of claim 1, wherein the view is an XML data view that is based on one or more UNION ALL operations on database statements from the set of database statements.

7. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

8. The method of claim 1, wherein the expression that operates on the XML construct is an XML-specific operator.

9. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

10. The method of claim 1, wherein the expression that operates on the XML construct is an XML-specific predicate.

11. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 10.

12. The method of claim 1, wherein the expression that operates on the XML construct is an XML aggregation operator.

13. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 12.

14. The method of claim 1, wherein the expression that operates on the XML construct is an XMLTable virtual table construct.

15. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 14.

16. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

17. The method of claim 1, wherein the XML construct includes one or more XML elements, XML attributes, XML documents, or XML document fragments that are indicated by a path that is provided as an argument to the expression.

18. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 17.

19. The method of claim 1, wherein the expression is an XML component operation.

20. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 19.

21. The method of claim 1, further comprising:

determining whether any statement, from the set of database statements included in the second database statement, is not in the XML construct; and

for any statement that is not in the XML construct, eliminating the statement from the second database statement, generating a reduced database statement corresponding to the first database statement.

22. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 21.

23. A method comprising:

a database server running on one or more computers;

said database server creating a first database statement that defines a view based on first and second XML data, wherein the first XML data is based on a first version of an XML schema and the second XML data is based on a second version of the XML schema, and wherein the second version of the XML schema describes a different data structure than the first version of the XML schema;

wherein the first database statement includes a union, based on a UNION ALL operator, of a statement involving the first XML data and a statement involving the second XML data; and

in response to receiving a second database statement that involves the view, executing the first database statement by said database server.

24. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 23.

26. The method of claim 23, wherein the first data storage model and the second data storage model are each from a group consisting of a CLOB (Character Large Object), an XML view based on relationally-stored data, and a native XML data type.

27. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 26.

25. A method comprising:

a database server running on one or more computers;

said database server creating a first database statement that defines a view based on first and second XML data, wherein the first XML data is stored in a database based on a first data storage model and the second XML data is stored in the database based on a second data storage model, and wherein the second data storage model is different than the first data storage model;

wherein the first database statement includes a union, based on a UNION ALL operator, of a statement involving the first XML data and a statement involving the second XML data; and

in response to receiving a second database statement that involves the view, executing the first database statement by said database server.

28. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 25.

29. A method comprising:

a first database server running on one or more computers;

said first database server creating a first database statement that defines a view based on first and second XML data, wherein the first XML data is managed by said first database server and the second XML data is managed by a second database server, and wherein the second database server is a different server than the first database server;

wherein the first database statement includes a union, based on a UNION ALL operator, of a statement involving the first XML data and a statement involving the second XML data; and

in response to receiving a second database statement that involves the view, executing the first database statement by said first database server.

30. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 29.

31. A method comprising:

a first database server running on one or more computers;

said first database server creating a first database statement that defines a view based on first and second XML data;

wherein the first XML data and the second XML data are based on a combination of any two from a group consisting of;

(a) the first XML data is managed by said first database server and the second XML data is managed by a second database server, wherein the second database server is a different server than the first database server,

(b) the first XML data is stored in a database based on a first data storage model and the second XML data is stored in the database based on a second data storage model, wherein the second data storage model is different than the first data storage model,

(c) the first XML data conforms to a first version of an XML schema and the second XML data conforms to a second version of the XML schema, wherein the second version of the XML schema describes a different data structure than the first version of the XML schema;

wherein the first database statement includes a union, based on a UNION ALL operator, of a statement involving the first XML data and a statement involving the second XML data; and

in response to receiving a second database statement that involves the view, executing the first database statement by said first database server.

32. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 31.

33. A method comprising:

a database server running on one or more computers;

said database server creating a first database statement that defines a view based on first and second XML data, wherein the first XML data is based on a first XML schema and the second XML data is based on a second XML schema, and wherein the second XML schema describes a different data structure than the first XML schema;

wherein the first database statement includes a union, based on a UNION ALL operator, of a statement involving the first XML data and a statement involving the second XML data; and

in response to receiving a second database statement that involves the view, executing the first database statement by said database server.

34. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 33.

35. A method comprising:

a database server running on one or more computers;

said database server receiving a first database statement;

wherein the first database statement requires access to a view defined as combined results of a set of database statements;

wherein the first database statement includes an expression that operates on an XML construct that is based on a relational database construct; and

said database server generating a second database statement, based on the first database statement and the view, that includes a modified version of the set of database statements in the distributive form and rewritten to operate on the relational database construct.

36. The method of claim 35, wherein the relational database construct includes one or more tables, rows, columns, objects, and views that are indicated by a reference to the relational database construct in the second database statement.

37. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 36.

38. A volatile or non-volatile machine-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 35.