Using sibling-count in XML indexes to optimize single-path queries

• US 7,840,609 B2
• Filed: 07/31/2007
• Issued: 11/23/2010
• Est. Priority Date: 07/31/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Agarwal, Nipun, Baby, Thomas, Chandrasekar, Sivasankaran, Murthy, Ravi
• Primary Examiner(s): Jalil; Neveen Abel
• Assistant Examiner(s): BUCKINGHAM, KELLYE DEE
• Application Number:
  US11/888,415
• Publication Number:
  US 20090037369A1
• Time in Patent Office: 1,211 Days
• Field of Search: None
• US Class Current: 707/804
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/8365 Query optimisation

Abstract

A method and apparatus are provided for using sibling-counts in XML indices to optimize single-path queries. Using a b-tree XML index with a SQL query logarithmically reduces the number of disk accesses by passing over index entries where it is determined that a match will not be found. However, because certain index entries are passed over, it is impossible to ascertain if a path expression occurs more than once in the XML index, as certain queries sometimes require. This hurdle can be overcome by maintaining a sibling count with each node entry in the XML index. Because the sibling count is stored with the index entry, the index will reveal whether the matching node is single or has other siblings. In additional to re-writing the original query for optimization by use of an XML index, it will be re-written to check for a single-path condition in the index.

Claims

1. A method for optimizing a query, comprising performing a machine-executed operation involving instructions, wherein said instructions are instructions which, when executed by one or more processors, cause the one or more processors to perform certain steps comprising:

for each node in a set of nodes, creating an entry in an index;

including, in the entry for each node, stored information that indicates whether there is either one node or more than one node at a path of the each node;

receiving a first query against the set of nodes, wherein the first query specifies a function that receives a specified path as an argument;

wherein the function is configured to;

return a certain value when there is only one node at the specified path; and

not return the certain value when there is more than one node at the specified path;

transforming the first query into a second query, wherein the second query;

does not include the function;

includes a condition that is based on the stored information that indicates whether there is either one node or more than one node at the specified path; and

includes operators operating on the index; and

wherein the second query is configured to return an equivalent result as the first query.

2. The method of claim 1, wherein the function is an SQL/XML operator.

3. The method of claim 2, wherein the SQL/XML operator is extractValue( ), and wherein extractValue( ) is used within the WHERE clause of a SQL query.

4. The method of claim 2, wherein the SQL/XML function in the first query is transformed into relational database operations to create the second query.

5. The method of claim 1, wherein the index is a first index, further comprising:

including, in each entry in the first index, a node value;

creating a second index on the node values;

wherein the second query includes operators operating on the second index.

16. The method of claim 1, wherein the first query is a SQL query, wherein the function is specified by an extractValue( ) operator, wherein the specified path is an XPath, and wherein transforming the first query into the second query comprises:

re-writing the SQL query such that a subquery replaces the extractValue( ) operator; and

including the condition in the SQL query as re-written.

6. A volatile or non-volatile computer-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:

for each node in a set of nodes, creating an entry in an index;

including, in the entry for each node, stored information that indicates whether there is either one node or more than one node at a path of the each node;

receiving a first query against the set of nodes, wherein the first query specifies a function that receives a specified path as an argument;

wherein the function is configured to;

return a certain value when there is only one node at the specified path; and

not return the certain value when there is more than one node at the specified path;

transforming the first query into a second query, wherein the second query;

does not include the function;

includes a condition that is based on the stored information that indicates whether there is either one node or more than one node at the specified path; and

includes operators operating on the index; and

wherein the second query is configured to return an equivalent result as the first query.

7. The volatile or non-volatile computer-readable storage medium of claim 6, wherein the function is an SQL/XML operator.

8. The volatile or non-volatile computer-readable storage medium of claim 7, wherein the SQL/XML operator is extractValue( ), and wherein extractValue( ) is used within the WHERE clause of a SQL query.

9. The volatile or non-volatile computer-readable storage medium of claim 7, wherein the SQL/XML function in the first query is transformed into relational database operations to create the second query.

10. The volatile or non-volatile computer-readable storage medium of claim 6, wherein the index is a first index, and wherein the one or more sequences of instructions, when executed, further cause the one or more processors to perform:

including, in each entry in the first index, a node value;

creating a second index on the node values;

wherein the second query includes operators operating on the second index.

17. The volatile or non-volatile computer-readable storage medium of claim 6, wherein the first query is a SQL query, wherein the function is specified by an extractValue( ) operator, wherein the specified path is an XPath, and wherein the one or more sequences of instructions, when executed, cause the one or more processors to perform transforming the first query into the second query by:

re-writing the SQL query such that a subquery replaces the extractValue( ) operator; and

including the condition in the SQL query as re-written.

11. An apparatus for optimizing a query, comprising:

one or more computing devices configured to perform;

for each node in a set of nodes, creating an entry in an index;

including, in the entry for each node, stored information that indicates whether there is either one node or more than one node at a path of the each node;

receiving a first query against the set of nodes, wherein the query specifies a function that receives a specified path as an argument;

wherein the function is configured to;

return a certain value when there is only one node at the specified path; and

not return the certain value when there is more than one node at the specified path;

transforming the first query into a second query, wherein the second query;

does not include the function;

includes a condition that is based on the stored information that indicates whether there is either one node or more than one node at the specified path; and

includes operators operating on the index; and

wherein the second query is configured to return an equivalent result as the first query.

12. The apparatus of claim 11, wherein the function is an SQL/XML operator.

13. The apparatus of claim 12, wherein the SQL/XML operator is extractValue( ), and wherein extractValue( ) is used within the WHERE clause of a SQL query.

14. The apparatus of claim 12, wherein the SQL/XML function in the first query is transformed into relational database operations to create the second query.

15. The apparatus of claim 11, wherein the index is a first index, wherein the one or more computing devices are further configured to perform:

including, in each entry in the first index, a node value;

creating a second index on the node values;

wherein the second query includes operators operating the second index.

18. The apparatus of claim 11, wherein the first query is a SQL query, wherein the function is specified by an extractValue( ) operator, wherein the specified path is an XPath, and wherein the one or more computing devices are further configured to perform transforming the first query into the second query by:

re-writing the SQL query such that a subquery replaces the extractValue( ) operator; and

including the condition in the SQL query as re-written.