Cache-based predicate handling for queries on XML data using uncorrelated path-based row sources
The approaches described herein provide an efficient way to process certain kinds of queries that retrieve XML data stored in an object-relational database. An XML query may include a predicate condition that joins two row sources. One or both of the row sources required for predicate evaluation may be path-based; that is, consume and produce rows of XMLType data. Such a path-based row source may be modified to use the predicate condition to filter the rows it generates so that only rows satisfying the predicate condition need to be further processed. A small cache structure introduced into the path-based row source may be used to optimize the evaluation of the predicate row. Once populated with row data, the cache structure may be reorganized to optimize the performance of predicate evaluation.
1. A computer-implemented method comprising:
computing a query against a collection of XML documents stored in one or more tables of a database by, at least in part, executing a plurality of row sources of a query execution plan, each row source of said plurality of row sources generating one or more output rows;
wherein said plurality of row sources includes a path-based row source based on a path expression;
wherein said query comprises a row predicate condition that specifies one or more conditions that output rows of said path-based row source must satisfy;
wherein executing the path-based row source causes:
(a) identifying, based on said path expression, XML data from one or more input rows to said path-based row source;
(b) filtering, based on said row predicate condition, said XML data from said one or more input rows to said path-based row source;
(c) generating output rows that contain XML data based on said (a) identifying, based on said path expression, XML data from one or more input rows to said path-based row source and said (b) filtering, based on said row predicate condition, said XML data from said one or more input rows to said path-based row source.
2. The method of claim 1 , wherein the query is an XQuery expression, and the path-based row source implements an XQuery FLOWR expression.
3. The method of claim 1 , wherein the query is an SQL expression including XMLTABLE, and the path-based row source is an XPathTable.
4. The method of claim 1 , wherein the query execution plan includes:
a first path-based row source generating a first set of output rows;
a second path-based row source generating a second set of output rows; and
computing the query further comprises:
determining that the second path-based row source does not operate on said first set of output rows;
determining that the second path-based row source generates all rows of the second set of output rows for each row in said first set of output rows; and
enabling a cache within the second path-based row source.
5. The method of claim 4 , further comprising:
the first path-based row source generating a first row of said first set of output rows;
in response to the first path-based row source generating the first row of said first set of output rows, the second path-based row source generating data for all rows of said second set of output rows, and storing the data in the cache.
6. The method of claim 5 , further comprising:
after the first path-based row source generating the first row, the first path-based row source generating a next row of said first set of output rows;
in response to the first path-based row source generating the next row, the second path-based row source creating a reorganized cache.
7. The method of claim 6 , wherein the reorganized cache is a sorted buffer.
8. The method of claim 7 , wherein the reorganized cache includes a hash table index, where in a key into the hash table index is based on a filter column.
9. The method of claim 8 , wherein a value of the filter column is computed and stored in the cache as a scalar value.
10. The method of claim 4 , wherein one or more tuning parameters define a maximum size of the cache.
11. The method of claim 10 , further comprising:
determining whether a size of the cache has exceeded a size limit set in the one or more tuning parameters;
in response to determining that the size of the cache has exceeded the size limit, moving parts of the cache to temporary database tables.
12. A non-transitory computer-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform a method, comprising:
computing a query against a collection of XML documents stored in one or more tables of a database by, at least in part, executing a plurality of row sources of a query execution plan, each row source of said plurality of row sources generating one or more output rows;
wherein said plurality of row sources includes a path-based row source based on a path expression;
wherein said query comprises a row predicate condition that specifies one or more conditions that output rows of said path-based row source must satisfy;
wherein executing the path-based row source causes:
(a) identifying, based on said path expression, XML data from one or more input rows to said path-based row source;
(b) filtering, based on said row predicate condition, said XML data from said one or more input rows to said path-based row source;
(c) generating output rows that contain XML data based on said (a) identifying, based on said path expression, XML data from one or more input rows to said path-based row source and said (b) filtering, based on said row predicate condition, said XML data from said one or more input rows to said path-based row source.
13. The non-transitory computer-readable storage medium of claim 12 , wherein the query is an XQuery expression, and the path-based row source implements an XQuery FLOWR expression.
14. The non-transitory computer-readable storage medium of claim 12 , wherein the query is an SQL expression including XMLTABLE, and the path-based row source is an XPathTable.
15. The non-transitory computer-readable storage medium of claim 12 , wherein the query execution plan includes:
a first path-based row source generating a first set of output rows;
a second path-based row source generating a second set of output rows; and
computing the query further comprises:
determining that the second path-based row source does not operate on said first set of output rows;
determining that the second path-based row source generates all rows of the second set of output rows for each row in said first set of output rows; and
enabling a cache within the second path-based row source.
16. The non-transitory computer-readable storage medium of claim 15 , further comprising:
the first path-based row source generating a first row of said first set of output rows;
in response to the first path-based row source generating the first row of said first set of output rows, the second path-based row source generating data for all rows of said second set of output rows, and storing the data in the cache.
17. The non-transitory computer-readable storage medium of claim 16 , further comprising:
after the first path-based row source generating the first row, the first path-based row source generating a next row of said first set of output rows;
in response to the first path-based row source generating the next row, the second path- based row source creating a reorganized cache.
18. The non-transitory computer-readable storage medium of claim 17 , wherein the reorganized cache is a sorted buffer.
19. The non-transitory computer-readable storage medium of claim 18 , wherein the reorganized cache includes a hash table index, where in a key into the hash table index is based on a filter column.
20. The non-transitory computer-readable storage medium of claim 19 , wherein a value of the filter column is computed and stored in the cache as a scalar value.
21. The non-transitory computer-readable storage medium of claim 15 , wherein one or more tuning parameters define a maximum size of the cache.
22. The non-transitory computer-readable storage medium of claim 21 , further comprising:
determining whether a size of the cache has exceeded a size limit set in the one or more tuning parameters;
in response to determining that the size of the cache has exceeded the size limit, moving parts of the cache to temporary database tables.