DB2 9 provides pureXML storage and XML-indexes, XQuery and SQL / XML as a query language, XML support and XML model, which in turn improve the Import / Export and Runstats and other utilities. As the same query in the relationship, the index for high-performance XQuery and SQL / XML is essential. DB2 XML columns defined to allow the path of the XML-specific index. This means that you can use them to index the predicate and connections frequently use the selected elements and attributes. For example, using sample data in Figure 1, according to author ID using the following index id×1 on table books of the XML column bookinfo all the documents to find and connect.
create table books (bookinfo XML);
create index id×1 on books (bookinfo)
generate keys using xmlpattern''/ book / authors / author / @ id''
as sql double;
Figure 1. To the text (continuous) format and resolution (stratified) sample XML document format
Since DB2 does not enforce a single XML model and XML column associated with all documents, so a specific element and attribute data type is unknown in advance. Therefore, the requirement for each XML index to specify a target type. Later, you will find out why this type is important. You can use the XML index data type as follows:
VARCHAR (n): string value for the node with a known string value of the maximum length of n.
VARCHAR HASHED: with a string value for the node, the string value of the length of any length. The index contains the actual string of the hash value, can only be used for equality predicates can not be used for range predicates.
DOUBLE: used with any numeric type of the node.
DATE and TIMESTAMP: for with a date or timestamp value node.
VARCHAR (n) the length of the index is a mandatory constraint. If you insert a document in which the element or attribute value of the index exceeds the maximum length n, the insert operation will fail. Similarly, if the index is greater than the value of the element or attribute n, while VARCHAR (n) index create index statement will fail.
By default, DOUBLE, DATE, or TIMESTAMP is not a compulsory constraint index. For example, the author ID attribute on the index id×1 is defined as DOUBLE, the hope is that these ID values. If you insert a document in which author ID of the value “MN127″, it is non-logarithmic values, although it will still be inserted in the document, but will not “MN127″ value added to the index. This is because the index can only be calculated DOUBLE numerical predicate, but “MN127″ will never match the value of a numerical search criteria. Therefore, the index is not the value is correct.
Backward from the DB2 9.5, you can add to the definition of XML indexes optional REJECT INVALID VALUES clause. This clause is mandatory to DOUBLE, DATE, or TIMESTAMP type of index as a mandatory constraint. If you define an index, but contains the author ID value “MN127″ can not insert a document, you create the index in the document can not be included in the XML column.
create index id×1 on books (bookinfo)
generate keys using xmlpattern''/ book / authors / author / @ id''
as sql double REJECT INVALID VALUES;
You can “DB2 pureXML Cookbook” to find the definition of XML indexes more details. In the discussion following the use of XML indexes, they assume you are familiar with query DB2 XML data in the basic concepts. For more information, please refer to previous article: “Query DB2 XML data with SQL” (developerWorks, 2006 ??3 months) and the “Query DB2 XML data using XQuery” (developerWorks, 2006 ??4 months) gives the introduction, ” DB2 9's pureXML: how to query your XML data? “(developerWorks, 2006 ??6 months) gives more examples and details.
For XQuery and SQL / XML statement XML index
As the same query in the relationship, the index for high-performance XQuery and SQL / XML statement is essential. When the application is submitted to the DB2 relational query or XML query, the query compiler will compare the query predicate and the existing index definition, and then determine whether there can be an index for the implementation of the query. The process is called “index matching”, and for a given query generate a suitable index (possibly empty set). Index of the group entered into the cost-based optimizer, the optimizer decide whether to use any suitable index. This article focuses on the index matching, rather than the optimizer's index selection. Decision-making in the optimizer, in addition to running “runstats” for the optimizer to provide accurate statistics on the data, in addition, can do things not many. But you can take a number of other measures to ensure that the index matching.
In relation query, the index matching is often negligible. DB2 can use a single definition of the relevant series of the index to respond to this column on any equality or range predicate verb. However, for XML column, it will be more complicated. The index of the relevant series contains all the values in this column, and XML index contains only those that match the patterns and index XML data type definition of the node value. Therefore, only when the XML indexes have the “right” data type and at least satisfy the predicate of all XML nodes, the XML index can be used to calculate the XML query predicate. For XML index eligibility, there are two main requirements:
Limit the definition of XML index equal to or lower than the query predicate restrictions (”hold”).
Index of data types and query the data type of predicate match.
This article explains how to design XML indexing and query in order to ensure compliance with these requirements and how to avoid common mistakes. Start to understand query execution plans to begin. DB2 in the interpretation of existing tools (such as Visual Explain and db2exfmt) can be used to view the XQuery and SQL / XML query execution plan, just as their traditional role of SQL in the same
XML query computing: implementation of the plan and the new operator
In order to implement XML Query, DB2 9 introduces three new internal query operators, called XSCAN, XISCAN and XANDOR. These new operators and existing query operators (such as TBSCAN, FETCH and SORT) allows DB2 generate SQL / XML, and XQueries the implementation plan. Now look at the three new operators, as well as their implementation plan with the XML index is how it works.
XSCAN (XML document scanning)
DB2 use XSCAN operator to traverse the XML document tree, if necessary, will calculate and extract document fragments, and predicate values. XSCAN not “XML table scan”, but after scanning the table, it can appear in the implementation of the plan, to deal with each document.
XISCAN (XML index scan)
Similar to the existing relationship between the index for the relationship between the index scan operator (IXSCAN), XISCAN operator to perform in the XML index search or scan. XISCAN value predicate to use as input, such as similar / book [price = 29] or where $ i / book / price = 29 the path value pairs. It returns a row ID and node ID. Bank ID used to identify qualified document contains the line, and node ID used to identify these documents qualified nodes.
XANDOR (link XML index)
XANDOR operator by operating multiple XISCAN, to simultaneously calculate the equation of two or more predicate. It will return documents that satisfy all predicates the row ID.
See below a sample query (with the XQuery and SQL / XML equivalent), respectively, with no understanding of the index, with an index and an implementation plan with a number of indexes:
- XQuery:
xquery for $ i in db2-fn: xmlcolumn (”BOOKS.BOOKINFO”)
where $ i / book / title = “Database systems” and $ i / book / price = 29
return $ i / book / authors;
- SQL / XML:
select XMLQUERY (''$ i / book / authors''passing bookinfo as “i”)
from books
where XMLEXISTS (''$ i / book [title = “Database systems” and price = 29]''
passing bookinfo as “i”);
- Two Indexes:
create index id×1 on books (bookinfo) generate keys
using xmlpattern''/ book / title''as sql varchar (50);
create index id×2 on books (bookinfo) generate keys
using xmlpattern''/ book / price''as sql double;
In Figure 2, can see the different query execution plan (simplified db2exfmt output). Because the implementation of the program logic flow is top to bottom, left to right, so to see such a scheme, preferably from the tree in the bottom left of the operator started.
If the query is not a suitable index for the predicate, use the most on the left of the plan (a). Table scan operator (TBSCAN) will read the table “BOOKS” all the lines. For each row, the nested loop join (NLJOIN) operator to point to a pointer to the corresponding XML document passed to the XSCAN operator. Similarly, NLJOIN not serve as a standard with two input connections, but to assist XSCAN operator to access the XML data. XSCAN operator to traverse each document, computing the predicate, if the predicate, then the extraction of “authors” element. RETURN operator to complete the query execution, and search-based results to return to the API.
Figure 2. 3 implementation plan: (a) no index, (b) an index, (c) the two indexes
If there is an index for one or two predicates, such as / book / price on the index id×1, will see a similar scheme in Figure 2 (b) the implementation of the plan. XISCAN use the path-value pairs (/ book / price, 29) to check the index and return the document in which the price for the 29 line ID. ID on the classification of these lines in order to remove the same item (if any), and follow-up to optimize the table I / O. Then line ID scan (RIDSCN) operator will scan these lines, trigger lines prefetch and line ID is passed to the FETCH operator. For each row ID, FETCH operator will read the corresponding rows in the table. The scheme has the advantage of only a small portion of the table to retrieve rows that the only “price” to search for the 29 line. This is far below the full table scan (that is, read each line) of the cost. Obtained for each line, XSCAN operator will process the corresponding XML documents. It will “title” to calculate the predicate, if the predicate, then the extraction of “authors” element. Possible that some documents, including the second predicate is not true, then the XSCAN will perform some action to exclude these documents. Therefore, if the second predicate is also used to replace the index will get better query performance.
If there are two predicates for the index, you can see the plan in Figure 2 (c). The program uses two XISCAN, one for each predicate and the index. XANDOR operator in turn use these XISCAN to check the two indexes, in order to effectively match the two predicates to find the document at the same time the line ID. FETCH operator to search only these lines, it will form the I / O minimum. Then for each document, XSCAN will extract the “authors” element. If the predicate in the path includes the / / or *, or use a range of comparison (such as ), then use the index AND''ing (IXAND) operator instead of XANDOR. Logically, these two operators performed the same operation, only for different types of predicates and the use of different optimization methods.
Optimizer can decide not to use the index, even if the index can be used. For example, if the second index table is not effective in reducing the number of rows to retrieve, for example, the overhead required to access the index table than saving I / O is more important, the optimizer might choose plan (b) instead of the planned (c) . However, the need to ensure that the optimizer considered all the appropriate index to the lowest cost and shortest implementation time to select the program. In other words, XML index eligibility to comply with two requirements:
XML index includes at least satisfy the predicate of all XML nodes.
Query predicate in the definition of data types are compatible with the index
XML indexes and query predicates in the wildcard
Wildcard / / and backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp can influence the index and query containment relationship between the predicate. This is because the path expression is different, for example, / book / price, and / / price. Path / book / price all the price elements were identified, they are the element “book” of the direct child elements. The path / / price of the XML document identifying all levels of the price element. Therefore, / book / price elements identified / / price elements specified subset. Ie / / price “includes” / book / price, but otherwise can not.
Now, look at the wild card is how it affects the eligibility index. With the following query example. Table 1 shows the changes in where the four statements.
XQUERY
for $ i in db2-fn: xmlcolumn (”BOOKS.BOOKINFO”)
where $ i / book / price = 29
return $ i / book / authors
Table 1 the most right two columns that the index of the two alternative definitions of the table each line shows the verb can (+) or not (-) calculated by the two indexes. Look at Table 1 below the line, to study the index eligibility of each predicate.
For the first predicate, because it contains only the “book” of the direct sub-element “price”, so / book / price on the index is unqualified. The index does not contain a deeper “price” element, the table of the elements may exist and may be related to the predicate path $ i / / price match. Therefore, if the DB2 in the / book / price using the price index may return incomplete results. The second index / / price is qualified, because it contains all the price levels of the document elements, as required by the predicate.
The third predicate uses the asterisk (*) as a wildcard, so it will look for “book” value of 29 under any sub-elements. Not only is the “price” elements satisfy the predicate. For example, the element / book / title of the document the value of 29 is a valid match. However, the two indexes in Table 1 do not include the title element. DB2 may return as the predicate for the incomplete results, so the two indexes are not used.
Table 1. Index eligibility and XML indexes and predicates in the wildcard
# Predicate / index defined … using xmlpattern''/ book / price''as sql double; … using xmlpattern''/ / price''as sql double;
1 when $ i / / price = 29 hours - +
2 When $ i / book / price = 29 hours + +
3where $ i / book / * = 29 - -
4where $ i / * / price = 29 - +
The fourth predicate $ i / * / price = 29 will find the price of any element under the root element, not just in the “book” element under. If there is a path / journal / price of document, you may meet the predicate $ i / * / price = 29, but will not be included in the / book / price on the index. Since DB2 may return to face the risk of incomplete query results, so can not use the index. However, / / price on the index contains any price element has nothing to do with the root element.
In short, DB2 query compiler must always be able to test the restrictions index equal to or less than the predicate constraints to include the predicate is to find all content.
Should be aware that the definition in the index may be inadvertently used the wildcard index more nodes (more than the required nodes). Whenever possible, it is recommended to use the index definitions and query elements or attributes required in the exact path instead of using wildcards. Such as / / bin / / boot / / dev / / etc / / home / / lib / / lost + found / / media / / misc / / mnt / / net / / opt / / proc / / root / / sbin / / selinux / / srv / / sys / / tmp / / u01 / / usr / / var / / vmware or / / text () of these very common XML index is acceptable, but should be used cautiously. / / * The index of the index or even non-terminal elements, non-terminal element is usually of no use and can easily exceed the Varchar (n) index length limit.
XML indexes and query predicates in the name of the space
XML index eligibility to note whether it contains the name of the space. First, if the table contains the name of the XML document space, then the index definition must consider the name of the space. This again relates to the index / predicate hold of. To the following definition of XML documents and indexing example:
Database Systems
29
CREATE INDEX id×3 ON books (bookinfo)
GENERATE KEYS USING XMLPATTERN''/ book / price''AS SQL DOUBLE;
Defined as the index id×3 / book / price index of the element with empty namespace, so the index of this sample document does not contain any index entry. However, the definition of any of the following index can be properly used for the index price elements:
CREATE INDEX id×4 ON books (bookinfo) GENERATE KEYS USING XMLPATTERN
''Declare namespace bk = “http://mybooks.org”; / bk: book / bk: price''AS SQL DOUBLE
CREATE INDEX id×5 ON books (bookinfo) GENERATE KEYS USING XMLPATTERN
''Declare default element namespace “http://mybooks.org”; / book / price''AS SQL DOUBLE
CREATE INDEX id×6 ON books (bookinfo) GENERATE KEYS USING XMLPATTERN
''/*: Book / *: price''AS SQL DOUBLE
Index id×4 explicitly declare the namespace and prefix to match the document. Index id×5 the namespace declaration for the default name space, as the name implies a prefix of space, so XML model / book / price does not use prefixes. Index id×6 only use a wildcard to match any name space. Using XQuery or SQL / XML statements that predicate, you can use the same options:
Query 4: - XQuery:
XQUERY declare namespace bk = “http://mybooks.org”;
for $ b in db2-fn: xmlcolumn (”BOOKS.BOOKINFO”) / bk: book
where $ b / bk: price <10
return $ b
- SQL / XML:
select bookinfo
from books
where XMLEXISTS (''declare namespace bk = “http://mybooks.org”;
$ B / bk: book [bk: price <10]''
passing bookinfo as “b”)
Query 5: - XQuery:
XQUERY declare default element namespace “http://mybooks.org”;
for $ b in db2-fn: xmlcolumn (”BOOKS.BOOKINFO”) / book
where $ b / price <10
return $ b
- SQL / XML:
select bookinfo
from books
where XMLEXISTS (''declare default element namespace “http://mybooks.org”;
$ B / book [price <10]''
passing bookinfo as “b”)
Query 6: - XQuery:
XQUERY for $ b in db2-fn: xmlcolumn (”BOOKS.BOOKINFO “)/*: book
where $ b / *: price <10
return $ b
- SQL / XML:
select bookinfo
from books
where XMLEXISTS (''$ b / *: book [*: price <10]''
passing bookinfo as “b”)
Table 2 for a query for each row, each column of the index were defined for the previous id×3 to id×6. Can first look at Table 2. First of all, the name space id×3 without any consideration of the name can not be used in the query space. Second, you can find queries 4 and 5 corresponding queries have the same entry line, and the corresponding index id×4 and id×5 columns also have the same entry. This is because the explicit namespace default namespace definition and the definition of logic is the same, just with a different view of things. You can use either one without affecting the index matching. Name space with the index id×6 wildcard queries are qualified for all examples, it can even be used without a predicate name space, such as $ b / price <10. Index id×6 or 6 in the predicate with the query only matches the index. Index id×4 and id×5 name contains space for a specific index entry, but because the inquiry is to find any name space 6 in the book prices, so the two can not be used to query the index 6. Therefore, the violation tolerance requirements.
Table 2. Index eligibility and XML indexes and predicates in the name of the space
# Query / index defined id×3 (no namespace) id×4 (explicit namespace) id×5 (default namespace) id×6 (namespace wildcard)
1 query 4 (explicit namespace) - + + +
2 Query 5 (default namespace) - + + +
3 Query 6 (namespace wildcard) - - - +
Predicate in the index and query XML data type
In addition to the index or the predicate name space with wildcards and inclusion, the index eligibility requirements for the second predicate, and an index of data types must match. In all these examples, / book / price element is always indexed as a DOUBLE. However, the price of books can be indexed as a VARCHAR, as shown in Table 3. However, please note that the value of the predicate also has a text value type defined by the data type. The value of double quotes is always a string, not numeric values with quotation marks are considered digital. As you can see in Table 3, the string predicate VARCHAR types of XML only by the index calculation, whereas a predicate can only be calculated DOUBLE types of indexes.
Relations indexed by the index of the column data type is always the type to determine. However, DB2 does not impose a requirement for XML pattern associated with the XML column, so the element or attribute data type is not predetermined. Therefore, each type of XML index requires a target. And type is very important. Assumed price element has value 9. String predicate “9″ < "29" is false, and value of 9 <29 is true. If you would like to emphasize here that the semantic values more correctly, should use DOUBLE index. Best to "price" element indexed as DOUBLE.
Table 3. XML indexes and predicates in the data type
# Predicates or indexes defined … using xmlpattern''/ book / price''as sql double; … using xmlpattern''/ book / price''as sql varchar (10);
1 when $ i / book / price < "29" when - +
2 When $ i / book / price <29 hours + -
Use XML to connect predicates suitable for the index
In the example above, see the text value, including the value of the predicate. Determine the comparative value of these text data type. Usually such a decision does not apply to join predicate. Assume that there is a table “authors”, in XML format of the detailed information, including data appears in the book's author ID. Now want to use to connect to retrieve details of the author data, and the only books in the table to retrieve the book authors. Defined in the author ID index seem useful:
create table books (bookinfo xml);
create table authors (authorinfo xml);
create index authorId×1 on books (bookinfo) generate key using
xmlpattern''/ book / authors / author / @ id''as sql double;
create index authorId×2 on authors (authorinfo) generate key using
xmlpattern''/ author / @ id''as sql double;
XQUERY
for $ i in db2-fn: xmlcolumn (”BOOKS.BOOKINFO”)
for $ j in db2-fn: xmlcolumn (”AUTHORS.AUTHORINFO”)
where $ i / book / authors / author / @ id = $ j / author / @ id
return $ j;
The query retrieval of the required information, but not the index used to connect handle. Please note, author ID on the join predicate does not contain text values, compare the text value will indicate the type of data. Therefore, DB2 must be considered match any data type author ID. For example, consider Table 4 in the book and author information. Author John Doe has a numeric ID value (47), and author Tom Noodle has a non-numeric ID value (TN28). In the two other tables are valid match. Therefore, both must be included in the link results. However, if you use the numeric index authorId×1 DB2 or authorId×2, you will not find the author ID “TN28″, and will return an incomplete result. Therefore, DB2 can not use those indexes, but the table scan will be taken to ensure correct query results.
Table 4. Sample book and author data
BookAuthor
John Doe
29
John Doe
555 Bailey Av
San Jose
US
4084511234
Tom Noodle
19.95
Tom Noodle
213 Rigatoni Road
Toronto
Canada
4162050745
However, in many cases specific to your document elements and attributes can not contain both digital and non-numeric values. If you know all the author ID is the number, you can specify in the query, which allows DB2 to use DOUBLE index. The following query explicitly connect the two ends of the predicate is converted to DOUBLE. This requires the numerical comparison, and apparently does not accept non-numeric connection match. Therefore, DB2 can use index for quick connection DOUBLE treatment.
XQUERY
for $ i in db2-fn: xmlcolumn (”BOOKS.BOOKINFO”)
for $ j in db2-fn: xmlcolumn (”AUTHORS.AUTHORINFO”)
where $ i / book / authors / author / @ id / xs: double (.) = $ j / author / @ id / xs: double (.)
return $ j;
Since the query does not include books or authors to limit the value of the predicate table, two tables, so DB2 must perform a table scan to read all the author ID. For each author ID, use the index to check whether the ID appears in another table. It does not use any index than the two table scans of the nested loop join is much faster. DB2's cost-based optimizer determines the table to be scanned through the index to access content. Table 5 shows the two implementation plan. If you are one of the following ways to use SQL / XML tags written the same connection, then the two implementation plan is feasible:
Query 1: select authorinfo
from books, authors
where xmlexists (''$ b / book / authors [author / @ id / xs: double (.) =
$ A / author / @ id / xs: double (.)]''
passing bookinfo as “b”, authorinfo as “a”);
Query 2: select authorinfo
from books, authors
where xmlexists (''$ a / author [@ id / xs: double (.) =
$ B / book / authors / author / @ id / xs: double (.)]''
passing bookinfo as “b”, authorinfo as “a”);
Query 1 and Query 2, the difference is XMLEXISTS predicate “direction.” In both the query, expressed in square brackets to connect the predicate. Query 1, the square brackets in the predicate expression is a $ b start on the predicate, so it is a predicate on the books table. DB2 9.7 ignored the difference between this syntax, and select the overhead table 5 smaller implementation plan.
However, prior to DB2 9.7, query 1 and 2 in the XMLEXISTS query determines the direction in which to use Table 5 implementation plan. As the query 1 in the books table in the join predicate, so DB2 9.1 and 9.5 authors perform a scan table, and then use the index to find books AUTHORIDX1 table. As shown in Table 5 on the left.
Query 2 on the authors table apply connection predicate. Thus 9.1 and 9.5 in the books on the implementation of the table scan, then use the index to check the authors table AUTHORIDX2 (as shown in Table 5, right). Therefore, the preparation can affect the way the predicate XMLEXISTS previous versions of DB2 connection order. If you can not avoid the use of table scans, will be its use in the smallest form.
Table 5. XML join queries of the execution plan, generated by the db2exfmt
Query 1 Query 2
Rows
RETURN
(1)
Cost
I / O
|
3.59881e-005
NLJOIN
(2)
5410.62
743
/——-+——-
1.29454e-007 278
NLJOIN NLJOIN
(3) (6)
4311.96 1098.66
570 173
/—+– / - +
556 2.32831e-010 139 2
TBSCAN XSCAN FETCH XSCAN
(4) (5) (7) (11)
106.211 7.5643 47.237 7.56421
141 341
| /—+—
556 139 556
TABLE: MATTHIAS RIDSCN TABLE: MATTHIAS
AUTHORS (8) BOOKS
15.2133
2
|
139
SORT
(9)
15.2129
2
|
139
XISCAN
(10)
15.1542
2
|
556
XMLIN: MATTHIAS
AUTHORIDX1
Rows
RETURN
(1)
Cost
I / O
|
8.37914e-015
NLJOIN
(2)
5410.63
743
/——–+——–
1.29454e-007 6.47269e-008
NLJOIN NLJOIN
(3) (6)
4311.96 1098.67
570 173
/—+– /—+–
556 2.32831e-010 139 4.65661e-010
TBSCAN XSCAN FETCH XSCAN
(4) (5) (7) (11)
106.211 7.56429 47.2365 7.5643
141 341
| /—+—
556 139 556
TABLE: MATTHIAS RIDSCN TABLE: MATTHIAS
BOOKS (8) AUTHORS
15.2128
2
|
139
SORT
(9)
15.2124
2
|
139
XISCAN
(10)
15.1537
2
|
556
XMLIN: MATTHIAS
AUTHORIDX2
Summarize the recommendations on the XML join queries, usually connected predicate into the XML index type should be used. Otherwise, the query semantics are not allowed to use the index. If the XML index is defined as DOUBLE, then use the xs: double conversion connected predicates. If the XML index is defined as VARCHAR, the use of fn: string conversion link predicate, as shown in Table 6. (Strictly speaking, DB2 9.7 do not have to use the fn: string to enable the connection predicate VARCHAR index. However, in DB2 9.5 are still to use the fn: string, and use it in the predicate is not adversely affected).
Table 6. Conversion join predicate to allow the use of XML indexes
SQL type conversion index used to connect the predicate: Note
DOUBLExs: double for any numerical comparison
VARCHAR (n), VARCHAR HASHEDfn: string for any string comparison
DATExs: date for date comparison
TIMESTAMPxs: dateTime time stamp than for
“Between” predicate index support
XQuery query is not similar to the relationship “between” predicate-specific function or operator. In addition, when the expression “between” condition, you need to pay attention to the existence of XQuery is generally more essential predicate.
Assume that the price you want to find between 20 and 30 books. Intuitively, you may use the predicate / book [price> 20 and price <30], but it did not constitute “between the predicate.” This means that if you have a / book / price on the index, DB2 can not be carried out between 20 and 30 in the index range scan to find the price range of books. This is because the document may have multiple price book sub-elements, such as the following example:
40
10
As is generally more (>,< ,= and <=, etc.) exist semantics, if the value is greater than 20 sub-elements of "price", while there is less than 30 sub-element "price", the predicate / book [price> 20 and price <30] will select the book elements. These child elements may be the same “price” element or two different “price” element.??????????¤??????????????????20 ?????????????????30 ???????????????????????????? 20 ??30 ?????br />
?????? DB2 ??20 ??30 ?????????????????????????????????????????????????????DB2 ?????????????????????????????????????????????????????? 3 ???????????????????????AND''ing ?????????DB2 ???????????????????????????????????XISCAN ?????????????????ID???????????IXAND ??????????????ID?????????????????$20?????????????????$30 ?????????????????? XISCAN ?????? ID ??????????????? IXAND ???????????????????????????????????br />
?????????????????????????etween?????????????????????????????????????????????????????????????ID???????????????????????? AND''ing??????????????????????????? ??? ????????????
?????3. ?????? AND''ing ????????????????????????
???RETURN
???|
???NLJOIN
???|
???/-+-
???/
???FETCH XSCAN
???|
???/—+—
???/
???RIDSCN TABLE:
???| BOOKS
???SORT
???|
???IXAND
???|
???/—+—
???XISCAN XISCAN
???price > 20 price < 30
???RETURN
???|
???NLJOIN
???|
???/-+-
???/
???FETCH XSCAN
???|
???/—+—
???/
???RIDSCN TABLE:
???| BOOKS
???SORT
???|
???XISCAN
???20 < price < 30
?????? DB2 ???????????????????????????????????????????????????????????? DB2 ????????? ??etween???????????????????????????????????????????????between(> ??<)????????????????????????????????>??amp;lt; ??= ????elf ????????????????????br />
????????br />
???????????? book ????????price ???????????? XQuery ?????????????????????????????????????????????????????/book[price gt 20 and price lt 30] ???????etween????????price ?????????????????????????????? book ?????? price ??????????????????????????????
???Self ??br />
????????????????????self ????? ???????)???????etween??????????? /book/price[. > 20 and . < 30] ??? self ????????????????????? price ????????self ????????????????????????????????? ??etween?????????????? book ?????? price???????????price ?????? 20 ??30 ???????????????????????????????????????????????br />
Property
?????? book price ?????????????????book ????????????????????/book[@price>20 and @price<30] ??????????????????????????DB2 ?????????????????????????etween???
??????????????? XPath ??text()??br />
??????????????????????????4 ??????????????? XML ????????????????????????????????????????????????????????? XML ?????????????????????????????????????????????????????? ??ook?????????atabase Systems29????????????????????????????????? ??rice???????29??br />
?????4. ????????XML ??????
???29
???XPath ?????/book/price ??/book/price/text() ????????????????????? ??rice????????????????9??????????????????????????????????????
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")/book
???return $b/price
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")/book
???return $b/price/text()
?????????????????????????????29???????????????????????? 29????????????????????? /text() ??XPath ?????????????????? $b/book??? DB2 ????????????????????????????ook????????????atabase Systems29????????????????????????????????br />
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")
???where $b/book = "Database Systems29"
???return $b
????????????????????? /text() ?????where ????????????????????????????????????????????????????ook?????
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")
???where $b/book/text()= "Database Systems29"
???return $b
???????????????????????????????????? /text() ?????????????????????????????????????????? /text()???????????????????????????????????????????????????????????? ??????????????????????rice????????????????????????????????
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")
???where $b/book/price < 10
???return $b
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")
???where $b/book/price/text() < 10
???return $b
?????? /text() ??????????????????????????????????????7 ???????????? /text() ???????????? XML ???????????/text() ???????????????????????/text()??????????????/text()??br />
?????7. ????????? /text() ?????????
??????????????..using xmlpattern ''/book/title/text()'' as sql varchar(128);…using xmlpattern ''/book/title'' as sql varchar(128);
?????$i/book/title = "Database Systems" ?? +
?????$i/book/title/text() = "Database Systems" ?? -
?????????????????? XML ?????????????????? /text()????????? /text() ??????????????????????????? XML ??? //text() ???????????????????????XML ?????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????br />
??????????????br />
???????????????????????/book ?????????????????????(??????????????????????????????????????????????????????????????????????????????????? XML ?????ML ??? /book ???????????????????????????????????? ??ohn DoePeter PanDatabase Systems29SQLrelational????????????????????????????????????????????????????????????????????????
???John Doe
???Peter Pan
???29
???SQL
???relationa
???????????????????????????????????????????????????????????????????????????????????????????????phone ???????????????
???John Doe
???408
???451123
??????????????????????hone?????????reacode?????????????XML ?????br />
???create index phoneidx on authors(authorinfo) generate key using
???xmlpattern ''/author/phone'' as sql double;
???create index areaidx on authors(authorinfo) generate key using
???xmlpattern ''/author/phone/areacode'' as sql double;
?????????????????????????????????????????br />
???select authorinfo from authors
???where xmlexists(''$a/author[phone=4084511234]'' passing authorinfo as "a");
???select authorinfo from authors
???where xmlexists(''$a/author[phone/areacode=408]'' passing authorinfo as "a");
???XML ????????????????????????????????????????????? XML ????????????????????????????????????????????????????????????????????phoneidx ?????/phone/areacode ??/phone/number ?????????
????????? XML ???????????br />
???XMLQUERY ??XMLEXISTS ????????br />
???????????? XML ????????????????????? XQuery ??SQL/XML ???????????? SQL/XML ??? XMLQUERY ??XMLEXISTS????????????????????br />
????????SQL ?????select ?????XMLQUERY ????????XML ???????????????????????????????????????????????????????????????????????????(??????)???????????????????????????????????SQL/XML ?????where ?????XMLEXISTS ??????
?????XMLEXISTS ?????????????????????????????$a/author[phone=4084511234]?????? $a/author/phone=4084511234?????? Boolean ????????phone ?????????????????? ??alse?????? XMLEXISTS ??????????????????????alse????????????¤? XMLEXISTS??????????????????????????????????????? XPath ??????????????????????????????????????????(??????????????????????br />
????????? XMLQUERY ??XMLEXISTS ??????????????????????B2 9 ??15 ??pureXML ???????????developerWorks??006 ??10 ????br />
???Let ??return ???
????????XQuery let ??return ????????????????????????????????????????????????????????????????????????????????author ?????? ??hone408???????????408 ?????author ????????br />
???XQUERY for $a in db2-fn:xmlcolumn("AUTHORS.AUTHORINFO")/author
???let $p := $a/phone[areacode="408"]//text()
???return {$p}
???XQUERY for $a in db2-fn:xmlcolumn("AUTHORS.AUTHORINFO")/author
???return {$a/phone[areacode="408"]//text()}
??????
???DB2 9 ??????????????????????.??????????????????????? ??rice??????????br />
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")/book/title[../price < 10]
???return $b
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")/book/title
???where $b/../price < 10
???return $b
????????????????????????????????? parent ??????????????br />
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")/book[price < 10]/title
???return $b
???XQUERY for $b in db2-fn:xmlcolumn("BOOKS.BOOKINFO")/book
???where $b/price < 10
???return $b/title
???????????(//) ????????br />
?????????????????????????????? self ??????????//)??????????????? ID ??129 ??????????????author ID ???????????????????????? ID ??????????????????????????????????????????????????br />
??????!
???select bookinfo
???from books
???where XMLEXISTS(''$b/book/authors[//@id = 129]''
???passing bookinfo as "b")
???????????????????uthors???????? ??uthors?????????????? ID ????????????????????????????????/ ??//)????????????????????????????????????????????????????????????????????br />
???John Doe
???Peter Pan
???29
?????????????????????????????self ??????????????????????? ??uthors?????????????? self ??(//)??br />
??????!
???select bookinfo
???from books
???where XMLEXISTS(''$b/book/authors[.//@id = 129]''
???passing bookinfo as "b")
????????? DB2 ????????/book//@id ??//@id ????????????????????????????????br />
????????? ??n the Path to Efficient XML Queries?????????????XQuery ??SQL/XML ??????????????????????????br />
Conclusion
???XML ?????? XML ???????????????????????XML ??????????????????????????????????????????????????????????????????????????????????????? XML ????????????????????????????????????????????????????????XML ????????????????????????
????:
MP4 to FLV
send anonymous text message express your
Flash Effect 2 Production Mysterious Word
MOV To IPod
New BUSINESS Databases And Tools
HR HR in the eyes of men and women
SCO Unix copyrights 6 years to reverse the verdict entered a new phase of World War II
TOD to WMV
Semiconductor India vied with New Deal
Fun Chinese version of Firefox mode conversion work great transfiguration Entertainment
ARTICLES about Site Administration
With JSF + Spring + IBatis build a simple framework
Clipboard Tools Reviews
To prevent the most effective way to Trojan
Tang Jun: To learn to create opportunities
Premier Wen visited the British company Behind Huawei
