| Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
|
|
View PDF |
This chapter explains the fundamentals of XPath rewrite in Oracle XML DB and how to use it for XML schema-based structured storage. It details the rewriting of XPath-expression arguments to these important SQL functions: existsNode, extract, extractValue, XMLSequence, updateXML, insertChildXMl, and deleteXML.
This chapter contains these topics:
When XMLType data is stored in structured storage (object-relationally) using an XML schema and queries using XPath are used, they can potentially be rewritten directly to the underlying object-relational columns. This rewrite of queries can also potentially happen when queries using XPath are issued on certain non-schema-based XMLType views. The optimization process of rewriting XPath expressions is called XPath rewrite.
This enables the use of B*Tree or other indexes, if present on the column, to be used in query evaluation by the Optimizer. This XPath rewrite mechanism is used for XPath-expression arguments to SQL functions such as existsNode, extract, extractValue, and updateXML. This enables the XPath expression to be evaluated against the XML document without constructing the XML document in memory.
The XPath expressions that are rewritten by Oracle XML DB are a proper subset of those that are supported by Oracle XML DB. Whenever you can do so without losing functionality, use XPath expressions that can be rewritten.
Example 6-1 XPath Rewrite
For example, a query such as the following tries to obtain the Company element and compare it with the literal 'Oracle':
SELECT OBJECT_VALUE FROM mypurchaseorders p WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Company') = 'Oracle';
Because table mypurchaseorders was created with XML schema-based structured storage, extractValue is rewritten to the underlying relational column that stores the company information for the purchaseOrder. The query is rewritten to the following:
SELECT VALUE(p) FROM mypurchaseorders p WHERE p.xmldata.Company = 'Oracle';
|
Note: XMLDATA is a pseudo-attribute of datatype XMLType that enables direct access to the underlying object column. See Chapter 4, "XMLType Operations". |
If there is a regular index created on the Company column, such as the following, then the preceding query uses the index for its evaluation.
CREATE INDEX company_index ON mypurchaseorders e (extractValue(OBJECT_VALUE, '/PurchaseOrder/Company'));
XPath rewrite happens for XML schema-based tables and both schema-based and non-schema-based views. In this chapter, we consider only examples related to schema-based tables.
Example 6-2 XPath Rewrite with UPDATEXML
The XPath argument to SQL function updateXML in this example is rewritten to the equivalent object relational SQL statement given in Example 6-3.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
EXTRACTVAL
----------
SBELL
1 row selected.
UPDATE purchaseorder
SET OBJECT_VALUE = updateXML(OBJECT_VALUE, '/PurchaseOrder/User/text()', 'SVOLLMAN')
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
1 row updated.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
EXTRACTVAL
----------
SVOLLMAN
1 row selected.
Example 6-3 Rewritten Object Relational Equivalent of XPath Rewrite with UPDATEXML
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User') FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACTVAL ---------- SBELL 1 row selected. UPDATE purchaseorder p SET p."XMLDATA"."userid" = 'SVOLLMAN' WHERE p."XMLDATA"."reference" = 'SBELL-2002100912333601PDT'; 1 row updated. SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User') FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACTVAL ---------- SVOLLMAN 1 row selected.
|
See Also: Chapter 3, "Using Oracle XML DB", "Understanding and Optimizing XPath Rewrite", for additional examples of rewrite over schema-based and non-schema-based views |
XPath rewrite happens for the following SQL functions:
extract
existsNode
extractValue
updateXML
insertChildXML
deleteXML
XMLSequence
XPath rewrite can happen when these SQL functions are present in any expression in a query, DML, or DDL statement. For example, you can use function extractValue to create indexes on the underlying relational columns.
Example 6-4 SELECT Statement and XPath Rewrites
This example gets the existing purchase orders:
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Company') FROM mypurchaseorders x WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/Item[1]/Part') = 1;
Here are some examples of statements that get rewritten to use underlying columns:
Example 6-5 DML Statement and XPath Rewrites
This example deletes all PurchaseOrders where the Company is not Oracle:
DELETE FROM mypurchaseorders x WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Company') = 'Oracle Corp';
Example 6-6 CREATE INDEX Statement and XPath Rewrites
This example creates an index on the Company column, because this is stored object relationally and the XPath rewrite happens, a regular index on the underlying relational column will be created:
CREATE INDEX company_index ON mypurchaseorders e (extractValue(OBJECT_VALUE,'/PurchaseOrder/Company'));
In this case, if the rewrite of the SQL functions results in a simple relational column, then the index is turned into a B*Tree or a domain index on the column, rather than a function-based index.
An XPath expression can generally be rewritten if all of the following are true:
The XML function or method is rewritable.
SQL functions extract, existsNode, extractValue, updateXML, insertChildXML, deleteXML, and XMLSequence are rewritten. Except method existsNode(), none of the corresponding XMLType methods are rewritten.
The XPath expression uses only the descendent axis.
Expressions involving axes (such as parent and sibling) other than descendent are not rewritten. Expressions that select attributes, elements, or text nodes can be rewritten. XPath predicates are rewritten to SQL predicates.
The XML Schema constructs for the XPath expression are rewritable.
XML Schema constructs such as complex types, enumerated values, lists, inherited (derived) types, and substitution groups are rewritten. Constructs such as recursive type definitions are not rewritten.
The storage structure chosen during XML-schema registration is rewritable.
Storage using the object-relational mechanism is rewritten. Storage of complex types using CLOBs are not rewritten
Table 6-1 lists the kinds of XPath expressions that can be translated into underlying SQL queries.
Table 6-1 Sample List of XPath Expressions for Translation to Underlying SQL constructs
| XPath Expression for Translation | Description |
|---|---|
|
Simple XPath expressions:
|
Involves traversals over object type attributes only, where the attributes are simple scalar or object types themselves. The only axes supported are the child and the attribute axes. |
|
Collection traversal expressions:
|
Involves traversal of collection expressions. The only axes supported are child and attribute axes. Collection traversal is not supported if the SQL function is used during a |
|
Predicates:
|
Predicates in the XPath are rewritten into SQL predicates. |
|
List index (positional predicate):
|
Indexes are rewritten to access the nth item in a collection. These are not rewritten for |
|
Wildcard traversals:
|
If the wildcard can be translated to a unique XPath (for example, |
|
Descendent axis:
|
Similar to a wildcard expression. The descendent axis gets rewritten, if it can be mapped to a unique XPath expression and the subsequent element is not involved in a recursive type definition. |
|
Oracle-provided extension functions and some XPath functions
|
Any function from the Oracle XML DB namespace ( |
|
String bind variables inside predicates
|
XPath expressions using SQL bind variables are rewritten if they occur between the concatenation ( |
|
Unnest operations using XMLSequence
|
When used in a |
The following are some of the XPath constructs that get rewritten. This is not an exhaustive list and only illustrates some of the common forms of XPath expressions that get rewritten.
Simple XPath traversals
Predicates and index accesses
Oracle-provided extension functions on scalar values
SQL Bind variables
Descendant axis (XML schema-based data only): Rewrites over the descendant axis (//) are supported if:
There is at least one XPath child or attribute access following the //
Only one descendant of the children can potentially match the XPath child or attribute name following the //. If the XML schema indicates that multiple descendants of the children potentially match, and there is no unique path that the // can be expanded to, then no rewrite is done.
None of the descendants have an element of type xsi:anyType
There is no substitution group that has the same element name at any descendant.
Wildcards (XML schema-based only). Rewrites over wildcard axis (/*) are supported if:
There is at least one XPath child or attribute access following the /*
Only one of the grandchildren can potentially match the XPath child or attribute name following the /*. If the XML schema indicates that multiple grandchildren potentially match, and there is no unique path that the /* can be expanded to, then no rewrite is done.
None of the children or grandchildren of the node before the /* have an element of type xsi:anyType
There is no substitution group that has the same element name for any child of the node before the /*.
The following XPath constructs are not rewritten:
XPath functions other than those listed earlier. The listed functions are rewritten only if the input is an element with scalar content.
XPath variable references.
All axes other than the child and attribute axes.
Recursive type definitions with descendent axes.
UNION operations.
In addition to standard XML Schema constructs such as complexTypes and sequences, the following additional XML Schema constructs are also supported. This is not an exhaustive list and seeks to illustrate the common schema constructs that get rewritten.
Collections of scalar values where the scalar values are used in predicates.
Simple type extensions containing attributes.
Enumerated simple types.
Boolean simple type.
Inheritance of complex types.
Substitution groups.
The following XML Schema constructs are not supported. This means that if the XPath expression includes nodes with the following XML Schema construct then the entire expression will not get rewritten:
XPath expressions accessing children of elements containing open content, namely any content. When nodes contain any content, then the expression cannot be rewritten, except when the any targets a namespace other than the namespace specified in the XPath. The any attributes are handled in a similar way.
Datatype operations that cannot be coerced, such as addition of a Boolean value and a number.
All rewritable XPath expressions over object-relational storage get rewritten. In addition to that, the following storage constructs are also supported for rewrite.
Simple numeric types mapped to SQL RAW datatype.
Various date and time types mapped to the SQL TIMESTAMP_WITH_TZ datatype.
Collections stored inline, out-of-line, as OCTs, and as nested tables.
XML functions over schema-based and non-schema-based XMLType views and SQL/XML views also get rewritten.
The following XML Schema storage constructs are not supported. This means that if the XPath expression includes nodes with the following storage construct then the entire expression will not get rewritten:
CLOB storage: If the XML schema maps part of the element definitions to a SQL CLOB value, then XPath expressions traversing such elements are not supported
For the most part, there is no difference between rewritten XPath queries and functionally evaluated ones. However, since XPath rewrite uses XML Schema information to turn XPath predicates into SQL predicates, comparison of nonnumeric entities is different.
In XPath 1.0, the comparison operators, >, <, >=, and <=, use only numeric comparison. The two operands are converted to numeric values before comparison. If either of them fails to be converted to a numeric value, the comparison returns false.
For instance, if I have an XML-schema element definition such as the following, then an XPath predicate such as [ShipDate < '2003-02-01'] will always evaluate to false with functional evaluation.
<element name="ShipDate" type="xs:date" xdb:SQLType="DATE"/>
This is because the string value '2003-02-01' cannot be converted to a numeric quantity. With XPath rewrite, however, this predicate gets translated to a SQL date comparison, and will evaluate to true or false, depending on the value of ShipDate.
Similarly if a collection value is compared with another collection value, the XPath 1.0 semantics dictate that the values must be converted to strings and then compared. With XPath rewrite, the comparison uses the rules for comparing SQL values.
To suppress this behavior, you can turn off rewrite either using query hints or session level events.
This section uses the same purchase-order XML schema introduced earlier in this chapter.
Example 6-7 Creating XML Schema-Based Purchase-Order Data
DECLARE
doc VARCHAR2(2000) :=
'<schema
targetNamespace="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
xmlns="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<complexType name="PurchaseOrderType">
<sequence>
<element name="PONum" type="decimal"/>
<element name="Company">
<simpleType>
<restriction base="string">
<maxLength value="100"/>
</restriction>
</simpleType>
</element>
<element name="Item" maxOccurs="1000">
<complexType>
<sequence>
<element name="Part">
<simpleType>
<restriction base="string">
<maxLength value="20"/>
</restriction>
</simpleType>
</element>
<element name="Price" type="float"/>
</sequence>
</complexType>
</element>
</sequence>
</complexType>
<element name="PurchaseOrder" type="po:PurchaseOrderType"/>
</schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd', doc);
END;
/
The registration creates the internal types. We can now create a table to store the XML values and also create a nested table to store the items.
CREATE TABLE mypurchaseorders OF XMLType XMLSchema "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" ELEMENT "PurchaseOrder" VARRAY xmldata."Item" STORE AS TABLE item_nested; Table created
Now, we insert a purchase order into this table.
INSERT INTO mypurchaseorders
VALUES(
XMLType(
'<PurchaseOrder
xmlns="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation
= "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd
http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd">
<PONum>1001</PONum>
<Company>Oracle Corp</Company>
<Item>
<Part>9i Doc Set</Part>
<Price>2550</Price>
</Item>
<Item>
<Part>8i Doc Set</Part>
<Price>350</Price>
</Item>
</PurchaseOrder>'));
Because the XML schema did not specify anything about maintaining the ordering, the default is to maintain the ordering and DOM fidelity. Hence the types have the SYS_XDBPD$ (PD) attribute to store the extra information needed to maintain the ordering of nodes and to capture extra items such as comments, processing instructions and so on.
The SYS_XDBPD$ attribute also maintains the existential information for the elements (that is, whether or not the element was present in the input document). This is needed for simpleType elements, because they map to simple relational columns. In this case, both empty and missing simpleType elements map to NULL values in the column, and the SYS_XDBPD$ attribute can be used to distinguish the two cases. The XPath rewrite mechanism takes into account the presence or absence of the SYS_XDBPD$ attribute, and rewrites queries appropriately.
This table has a hidden XMLDATA column of type purchaseorder_t that stores the actual data.
XPath-expression mapping of types and path expressions is described in the following sections.
A rewrite for a simple XPath involves accessing the attribute corresponding to the XPath expression – see Table 6-2.
Table 6-2 Simple XPath Mapping for purchaseOrder XML Schema
| XPath Expression | Maps to |
|---|---|
|
|
column |
|
|
column |
|
|
column |
|
|
elements of the collection |
|
|
attribute " |
An XPath expression can contain a text() node test, which targets the text node (content) of an element. When rewriting, this maps directly to the underlying relational columns. For example, the XPath expression "/PurchaseOrder/PONum/text()" maps directly to the SQL column XMLDATA."PONum".
A NULL in the PONum column implies that the text value is not available: either the text() node test is not present in the input document or the element itself is missing. If the column is NULL, there is no need to check for the existence of the element in the SYS_XBDPD$ attribute.
The XPath "/PurchaseOrder/PONum" also maps to the SQL attribute XMLDATA."PONum". However, in this case, XPath rewrite must check for the existence of the element itself, using attribute SYS_XDBPD$ in column XMLDATA.
Predicates are mapped to SQL predicate expressions. Since the predicates are rewritten to SQL, the comparison rules of SQL are used instead of the XPath 1.0 semantics.
Example 6-8 Mapping Predicates
The predicate in the XPath expression:
/PurchaseOrder[PONum=1001 and Company = "Oracle Corp"]
maps to the SQL predicate:
(XMLDATA."PONum" = 20 AND XMLDATA."Company" = "Oracle Corp")
The following query is rewritten to the structured (object-relational) equivalent, and will not require functional evaluation of the XPath.
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Item').getClobval()
FROM mypurchaseorders p
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[PONum=1001 AND Company = "Oracle Corp"]') = 1;
Schema-Based: Mapping of Collection Predicates XPath expressions can involve relational collection expressions. In Xpath 1.0, these are treated as existential checks: if at least one member of the collection satisfies the expression, then the expression is true.
Example 6-9 Mapping Collection Predicates
The collection predicate in this XPath expression involves the relational greater-than operator (>):
/PurchaseOrder[Items/Price > 200]
This maps to the following SQL collection expression:
exists(SELECT NULL FROM table(XMLDATA."Item") x WHERE x."Price" > 200)
In this example, a collection is related to a scalar value. More complicated rewrites occur with a relation between two collections. For example, in the following XPath expression, both LineItems and ShippedItems are collections.
/PurchaseOrder[LineItems = ShippedItems]
In this case, if any combination of nodes from these two collections satisfies the equality, then the predicate is considered satisfied.
Example 6-10 Mapping Collection Predicates, Using EXISTSNODE
Consider a fictitious XPath that checks if a Purchaseorder has Items whose Price and Part number are the same:
/PurchaseOrder[Items/Price = Items/Part]
-- maps to a SQL collection expression:
exists(SELECT NULL
FROM table(XMLDATA."Item") x
WHERE exists(SELECT NULL
FROM table(XMLDATA."Item") y
WHERE y."Part" = x."Price"))
The following query is rewritten to the structured equivalent:
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Item').getClobval() FROM mypurchaseorders p WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Item/Price = Item/Part]') = 1;
Most of the rewrite preserves the original document ordering. However, because SQL does not guarantee ordering on the results of subqueries when selecting elements from a collection using SQL function extract, the resultant nodes may not be in document order.
Example 6-11 Document Ordering with Collection Traversals
For example:
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Item[Price>2100]/Part') FROM mypurchaseorders p;
This query is rewritten to use a subquery:
SELECT (SELECT XMLAgg(XMLForest(x."Part" AS "Part"))
FROM table(XMLDATA."Item") x WHERE x."Price" > 2100)
FROM mypurchaseorders p;
In most cases, the result of the aggregation is in the same order as the collection elements, but this is not guaranteed. So, the results may not be in document order.
An XPath expression can also access an element at a particular position of a collection. For example, "/PurchaseOrder/Item[1]/Part" is rewritten to extract out the first Item element of the collection, and access the Part attribute within that.
If the collection is stored as a varray, then this operation retrieves the nodes in the same order as in the original document. If the collection is stored as a nested table, then the order is indeterminate.
An XPath expression can contain references to nodes that cannot be present in the input document. Such parts of the expression map to SQL NULL values during rewrite. For example, the XPath expression /PurchaseOrder/ShipAddress cannot be satisfied by any instance document conforming to the purchaseorder.xsd XML schema, because the schema does not allow for ShipAddress elements under PurchaseOrder. Hence this expression would map to a SQL NULL literal.
Namespaces are handled in the same way as function-based evaluation. For schema-based documents, if the function (such as existsNode or extract) does not specify any namespace parameter, then the target namespace of the schema is used as the default namespace for the XPath expression.
Example 6-12 Handling Namespaces
For example, the XPath expression /PurchaseOrder/PONum is treated as /a:PurchaseOrder/a:PONum with xmlns:a= "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" if the SQL function does not explicitly specify the namespace prefix and mapping. In other words:
SELECT * FROM mypurchaseorders p WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/PONum') = 1;
is equivalent to the query:
SELECT *
FROM mypurchaseorders p
WHERE existsNode(
OBJECT_VALUE,
'/PurchaseOrder/PONum',
'xmlns="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd')
= 1;
When performing XPath rewrite, the namespace for a particular element is matched with that of the XML schema definition. If the XML schema contains elementFormDefault="qualified" then each node in the XPath expression must target a namespace (this can be done using a default namespace specification or by prefixing each node with a namespace prefix).
If the elementFormDefault is unqualified (which is the default), then only the node that defines the namespace should contain a prefix. For instance if the purchaseorder.xsd had the element form to be unqualified, then existsNode expression should be rewritten as follows:
existsNode( OBJECT_VALUE, '/a:PurchaseOrder/PONum', 'xmlns:a="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd") = 1;
|
Note: For the case whereelementFormDefault is unqualified, omitting the namespace parameter in the existsNode expression in the preceding example would cause each node to default to the target namespace. This would not match the XML schema definition and consequently would not return any result. This is true whether or not the function is rewritten. |
Date datatypes such as DATE, gMONTH, and gDATE have different format in XML Schema and SQL. If an expression has a string value for columns of such datatypes, then the rewrite automatically provides the XML format string to convert the string value correctly. Thus, the string value specified for a DATE column must match the XML date format, not the SQL DATE format.
Example 6-13 Date Format Conversions
For example, the expression [@PurchaseDate="2002-02-01"] cannot be simply rewritten as XMLDATA."PurchaseDate"="2002-02-01", because the default date format for SQL is not YYYY-MM-DD. Hence during XPath rewrite, the XML format string is added to convert text values into date datatypes correctly. Thus the preceding predicate would be rewritten as:
XMLDATA."PurchaseDate" = TO_DATE("2002-02-01","SYYYY-MM-DD");
Similarly when converting these columns to text values (needed for functions such as extract), XML format strings are added to convert them to the same date format as XML.
SQL function existsNode checks for the existence of a node addressed by an XPath; function extract returns a node addressed by an XPath. Oracle XML DB needs to perform special checks for simpleType elements and for attributes used in existsNode expressions. This is because the SQL column value alone cannot distinguish whether an attribute or a simpleType element is missing or is empty; a NULL SQL column can represent either. These special checks are not required for intermediate elements, because the value of the user-defined SQL datatype indicates the absence or emptiness of the element.
Consider, for example, this expression:
existsNode(OBJECT_VALUE, '/PurchaseOrder/PONum/text()') = 1;
Because the query is only interested in the text value of the node, this is rewritten to:
(p.XMLDATA."PONum" IS NOT NULL)
Consider this expression, without the text() node test:
existsNode(OBJECT_VALUE, '/PurchaseOrder/PONum') = 1;
In this case, Oracle XML DB must check the SYS_XDBPD$ attribute in the parent node to determine whether the element is empty or is missing. This check is done internally. It can be represented in pseudocode as follows:
node_exists(p.XMLDATA."SYS_XDBPD$", "PONum")
The pseudofunction node_exists is used for illustration only. It represents an Oracle XML DB implementation that uses its first argument, the positional-descriptor (PD) column (SYS_XDBPD$), to determine whether or not its second argument (element or attribute) node exists. It returns true if so, and false if not.
In the case of extract expressions, this check needs to be done for both attributes and elements. An expression of the form extract(OBJECT_VALUE, '/PurchaseOrder/PONum') maps to pseudocode such as the following:
CASE WHEN node_exists(p.XMLDATA.SYS_XDBPD$", "PONum")
THEN XMLElement("PONum", p.XMLDATA."PONum")
ELSE NULL END;
|
Note: Be aware of this overhead when writingexistsNode and extract expressions. You can avoid this overhead by using a text() node test in the XPath expression; using extractValue to obtain only the node value; or by turning off DOM fidelity for the parent node. DOM fidelity can be turned off by setting the value of the attribute maintainDOM in the element definition to be false. When turned off, empty elements and attributes are treated as missing. |
To determine if your XPath expressions are getting rewritten, you can use one of the following techniques:
This section shows how you can use EXPLAIN PLAN to examine the query plans after rewrite. See "Understanding and Optimizing XPath Rewrite" for examples on how to use EXPLAIN PLAN to optimize XPath rewrite.
With the explained plan, if the plan does not pick applicable indexes and shows the presence of the SQL function (such as existsNode or extract), then you know that the rewrite has not occurred. You can then use the events described later to understand why the rewrite did not happen.
For example, using table mypurchaseorders we can see the use of EXPLAIN PLAN. We create an index on the Company element of PurchaseOrder to show how the plans differ.
CREATE INDEX company_index ON mypurchaseorders
(extractValue(OBJECT_VALUE,'/PurchaseOrder/Company'));
Index created.
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum')
FROM mypurchaseorders
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Company="Oracle"]') = 1;
Explained.
SELECT PLAN_TABLE_OUTPUT
FROM table(DBMS_XPLAN.display('plan_table', NULL, 'serial'))
/
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | MYPURCHASEORDERS | | | |
|* 2 | INDEX RANGE SCAN | COMPANY_INDEX | | | |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MYPURCHASEORDERS"."SYS_NC00010$"='Oracle')
In this explained plan, you can see that the predicate uses internal columns and picks up the index on the Company element. This shows clearly that the query has been rewritten to the underlying relational columns.
In the following query, we are trying to perform an arithmetic operation on the Company element which is a string type. This is not rewritten and hence the EXPLAIN PLAN shows that the predicate contains the original existsNode expression. Also, since the predicate is not rewritten, a full table scan instead of an index range scan is used.
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum')
FROM mypurchaseorders
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Company+PONum="Oracle"]') = 1;
Explained.
SELECT PLAN_TABLE_OUTPUT
FROM table(DBMS_XPLAN.display('plan_table', NULL, 'serial'))/
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
| Id | Operation | Name
-----------------------------------------------------------
| 0 | SELECT STATEMENT |
|* 1 | FILTER |
| 2 | TABLE ACCESS FULL| MYPURCHASEORDERS
|* 3 | TABLE ACCESS FULL| ITEM_NESTED
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(EXISTSNODE(SYS_MAKEXML('C6DB2B4A1A3B0
6CDE034080020E5CF39',2300,"MYPURCHASEORDERS"."XMLEXTRA",
"MYPURCHASEORDERS"."XMLDATA"),
'/PurchaseOrder[Company+PONum="Oracle"]')=1)
3 - filter("NESTED_TABLE_ID"=:B1)
Events can be set in the initialization file or can be set for each session using the ALTER SESSION statement. The XML events can be used to turn off functional evaluation, turn off the XPath rewrite mechanism and to print diagnostic traces.
By turning on this event, you can raise an error whenever any of the XML functions is not rewritten and is instead evaluated functionally. The error ORA-19022 - XML XPath functions are disabled will be raised when such functions execute. This event can also be used to selectively turn off functional evaluation of functions. Table 6-3 lists the various levels and the corresponding behavior.
Table 6-3 Event Levels and Behaviors
| Event | Turn off functional evaluation of . . . |
|---|---|
|
Level 0x1 |
all XML functions |
|
Level 0x2 |
|
|
Level 0x4 |
|
|
Level 0x8 |
|
|
Level 0x10 |
|
|
Level 0x20 |
|
|
Level 0x40 |
|
|
Level 0x80 |
|
|
Level 0x100 |
|
|
Level 0x200 |
|
|
Level 0x4000 |
|
|
Level 0x8000 |
|
For example,
ALTER SESSION SET EVENTS '19021 trace name context forever, level 1';
would turn off the functional evaluation of all the XML operators listed earlier. Hence when you perform the query shown earlier that does not get rewritten, you will get an error during the execution of the query.
SELECT OBJECT_VALUE FROM mypurchaseorders WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Company+PONum="Oracle"]')=1 ; ERROR: ORA-19022: XML XPath functions are disabled
Event 19027 with level 8192 (0x2000) can be used to dump traces that indicate the reason that a particular XML function is not rewritten. For example, to check why the query described earlier, did not rewrite, we can set the event and run an EXPLAIN PLAN:
ALTER SESSION SET EVENTS '19027 TRACE NAME CONTEXT FOREVER, LEVEL 8192';
Session altered.
EXPLAIN PLAN FOR
SELECT OBJECT_VALUE FROM mypurchaseorders
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Company+100="Oracle"]') = 1;
Explained.
This writes the following the Oracle trace file explaining that the rewrite for the XPath did not occur since there are inputs to an arithmetic function that are not numeric.
NO REWRITE
XPath ==> /PurchaseOrder[Company+PONum = "Oracle"] Reason ==> non numeric inputs to arith{2}{4}
This section details XPath rewrite for SQL functions existsNode, extractValue, extract, XMLSequence, updateXML, insertChildXML, and deleteXML. It explains the overhead involved in certain types of operations using existsNode or extract, and how to avoid it.
An update using one of these SQL functions normally involves updating a copy of the XML document and then replacing the entire document with the newly modified document.
When XMLType data is stored in an object-relational manner using XML-schema mapping, updates are optimized to directly modify pieces of the document in place. For example, an update of the PONum element can be rewritten to directly update the XMLDATA.PONum column, instead of materializing the whole document in memory and then performing the update.
Each of the functions updateXML, insertChildXML, and deleteXML must satisfy different conditions for it to use such rewrite optimization during update. If all of the conditions are satisfied, then the functional expression is rewritten into a simple relational update. For example:
UPDATE purchaseorder_table
SET OBJECT_VALUE =
updateXML(OBJECT_VALUE,
'/PurchaseOrder/@PurchaseDate', '2002-01-02',
'/PurchaseOrder/PONum/text()', 2200);
This update operation is rewritten as something like the following:
UPDATE purchaseorder_table p
SET p.XMLDATA."PurchaseDate" = TO_DATE('2002-01-02', 'SYYYY-MM-DD'),
p.XMLDATA."PONum" = 2100;
SQL function existsNode returns one (1) if the XPath argument targets a nonempty sequence of nodes (text, element, or attribute); otherwise, it returns zero (0). The value is determined differently, depending on the kind of node targeted by the XPath argument:
If the XPath argument targets a text node (using node test text()) or a complexType element node, Oracle XML DB simply checks whether the database representation of the element content is NULL.
Otherwise, the XPath argument targets a simpleType element node or an attribute node. Oracle XML DB checks for the existence of the node using the positional-descriptor attribute SYS_XDBPD$. If SYS_XDBPD$ is absent, then the existence of the node is determined by checking whether or not the column is NULL.
Table 6-4 shows the mapping of various XPaths in the case of SQL function existsNode when document ordering is preserved; that is, when SYS_XDBPD$ exists and maintainDOM="true" is present in the schema document.
Table 6-4 XPath Mapping for EXISTSNODE with Document Ordering Preserved
| XPath Expression | Maps to |
|---|---|
/PurchaseOrder |
CASE WHEN XMLDATA IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder/@PurchaseDate |
CASE WHEN node_existsFoot 1 (XMLDATA.SYS_XDBPD$, 'PurchaseDate') THEN 1 ELSE 0 END |
/PurchaseOrder/PONum |
CASE WHEN node_existsFootref 1(XMLDATA.SYS_XDBPD$, 'PONum') THEN 1 ELSE 0 END |
/PurchaseOrder[PONum = 2100] |
CASE WHEN XMLDATA."PONum"=2100 THEN 1 ELSE 0 |
/PurchaseOrder[PONum = 2100]/@PurchaseDate |
CASE WHEN XMLDATA."PONum"=2100
AND node_existsFootref 1(XMLDATA.SYS_XDBPD$, 'PurchaseDate')
THEN 1 ELSE 0 END
|
/PurchaseOrder/PONum/text() |
CASE WHEN XMLDATA."PONum" IS NOT NULL THEN 1 ELSE 0 |
/PurchaseOrder/Item |
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
WHERE value(x) IS NOT NULL)
THEN 1 ELSE 0 END
|
/PurchaseOrder/Item/Part |
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
WHERE node_existsFootref 1(x.SYS_XDBPD$, 'Part'))
THEN 1 ELSE 0 END
|
/PurchaseOrder/Item/Part/text() |
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
WHERE x."Part" IS NOT NULL)
THEN 1 ELSE 0 END
|
node_exists is used for illustration only. It represents an Oracle XML DB implementation that uses its first argument, the PD column, to determine whether or not its second argument node exists. It returns true if so, and false if not.Example 6-14 EXISTSNODE Mapping with Document Order Preserved
Using the preceding mapping, this query checks whether purchase order 1001 contains a part with price greater than 2000:
SELECT count(*)
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[PONum=1001 and Item/Price > 2000]') = 1;
This is rewritten as something like the following:
SELECT count(*)
FROM purchaseorder p
WHERE CASE WHEN p.XMLDATA."PONum" = 1001
AND exists(SELECT NULL FROM table(XMLDATA."Item") p
WHERE p."Price" > 2000 ))
THEN 1
ELSE 0
END = 1;
This CASE expression is further optimized due to the constant relational equality expressions. The query becomes:
SELECT count(*)
FROM purchaseorder p
WHERE p.XMLDATA."PONum"=1001
AND exists(SELECT NULL FROM table(p.XMLDATA."Item") x
WHERE x."Price" > 2000);
This uses relational indexes for its evaluation, if present on the Part and PONum columns.
If the positional-descriptor attribute SYS_XDBPD$ does not exist (that is, if the XML schema specifies maintainDOM="false") then NULL scalar columns map to simpleType elements that do not exist. In that case, you do not need to check for node existence using attribute SYS_XDBPD$. Table 6-5 shows the mapping of existsNode in the absence of the SYS_XDBPD$ attribute.
Table 6-5 XPath Mapping for EXISTSNODE Without Document Ordering
| XPath Expression | Maps to |
|---|---|
/PurchaseOrder |
CASE WHEN XMLDATA IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder/@PurchaseDate |
CASE WHEN XMLDATA.'PurchaseDate' IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder/PONum |
CASE WHEN XMLDATA."PONum" IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder[PONum = 2100] |
CASE WHEN XMLDATA."PONum" = 2100 THEN 1 ELSE 0 END |
/PurchaseOrder[PONum = 2100]/@PurchaseOrderDate |
CASE WHEN XMLDATA."PONum" = 2100
AND XMLDATA."PurchaseDate" NOT NULL
THEN 1 ELSE 0 END
|
/PurchaseOrder/PONum/text() |
CASE WHEN XMLDATA."PONum" IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder/Item |
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
WHERE value(x) IS NOT NULL)
THEN 1 ELSE 0 END
|
/PurchaseOrder/Item/Part |
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
WHERE x."Part" IS NOT NULL)
THEN 1 ELSE 0 END
|
/PurchaseOrder/Item/Part/text() |
CASE WHEN exists(SELECT NULL FROM table(XMLDATA."Item") x
WHERE x."Part" IS NOT NULL)
THEN 1 ELSE 0 END
|
SQL function extractValue is a shortcut for extracting text nodes and attributes using function extract and then using method getStringVal() or getNumberVal() to obtain the scalar content. Function extractValue returns the values of attribute nodes or the text nodes of elements with scalar values. Function extractValue cannot handle XPath expressions that return multiple values or complexType elements.
Table 6-6 shows the mappings of various XPath expressions for function extractValue. If an XPath expression targets an element, then extractValue retrieves the text node of the element. For example, /PurchaseOrder/PONum and /PurchaseOrder/PONum/text() are handled identically by extractValue: both retrieve the scalar content of PONum.
Table 6-6 XPath Mapping for EXTRACTVALUE
| XPath Expression | Maps to |
|---|---|
/PurchaseOrder |
Not supported. Function |
/PurchaseOrder/@PurchaseDate |
XMLDATA."PurchaseDate" |
/PurchaseOrder/PONum |
XMLDATA."PONum" |
/PurchaseOrder[PONum = 2100] |
(SELECT TO_XML(x.XMLDATA) FROM DUAL WHERE x."PONum" = 2100) |
/PurchaseOrder[PONum =
2100]/@PurchaseDate
|
(SELECT x.XMLDATA."PurchaseDate") FROM DUAL WHERE x."PONum" = 2100) |
/PurchaseOrder/PONum/text() |
XMLDATA."PONum" |
/PurchaseOrder/Item |
Not supported. Function |
/PurchaseOrder/Item/Part |
Not supported. Function |
/PurchaseOrder/Item/Part/text() |
Not supported. Function |
Example 6-15 Rewriting EXTRACTVALUE
Consider this SQL query:
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') FROM purchaseorder WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') = 1001;
This query would be rewritten as something like the following:
SELECT p.XMLDATA."PONum" FROM purchaseorder p WHERE p.XMLDATA."PONum" = 1001;
Because it gets rewritten to simple scalar columns, any indexes on attribute PONum can be used to satisfy the query.
Creating Indexes with EXTRACTVALUE
Function extractValue can be used in index expressions. If the expression gets rewritten into scalar columns, then the index is turned into a B*Tree index instead of a function-based index.
Example 6-16 Creating Indexes with EXTRACTVALUE
CREATE INDEX my_po_index ON purchaseorder (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference);
This would would get rewritten into something like the following:
CREATE INDEX my_po_index ON purchaseorder x (x.XMLDATA."Reference");
This produces a regular B*Tree index. Unlike a function-based index, the same index can now satisfy queries that target the column, such as the following:
existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
SQL function extract retrieves XPath results as XML. For Xpath expressions involving text nodes, extract is rewritten similarly to extractValue.
Table 6-7 shows the mapping of various XPath expressions inside extract expressions when document order is preserved (that is, when SYS_XDBPD$ exists and maintainDOM="true" in the XML schema document).
Table 6-7 XPath Mapping for EXTRACT with Document Ordering Preserved
| XPath | Maps to |
|---|---|
/PurchaseOrder |
XMLForest(XMLDATA AS "PurchaseOrder") |
/PurchaseOrder/@PurchaseDate |
CASE WHEN node_existsFoot 1 (XMLDATA.SYS_XDBPD$, 'PurchaseDate') THEN XMLElement("", XMLDATA."PurchaseDate") ELSE NULL END; |
/PurchaseOrder/PONum |
CASE WHEN node_existsFootref 1(XMLDATA.SYS_XDBPD$, 'PONum') THEN XMLElement("PONum", XMLDATA."PONum") ELSE NULL END |
/PurchaseOrder[PONum = 2100] |
SELECT XMLForest(XMLDATA as "PurchaseOrder") FROM DUAL WHERE XMLDATA."PONum" = 2100 |
/PurchaseOrder [PONum = 2100]/@PurchaseDate |
SELECT CASE WHEN node_existsFootref 1(XMLDATA.SYS_XDBPD$, 'PurchaseDate') THEN XMLElement("", XMLDATA."PurchaseDate") ELSE NULL END FROM DUAL WHERE XMLDATA."PONum" = 2100 |
/PurchaseOrder/PONum/text() |
XMLElement("", XMLDATA."PONum")
|
/PurchaseOrder/Item |
SELECT XMLAgg(XMLForest(value(it) AS "Item")) FROM table(XMLDATA."Item") it |
/PurchaseOrder/Item/Part |
SELECT XMLAgg(CASE WHEN node_existsFootref 1(p.SYS_XDBPD$, 'Part') THEN XMLForest(p."Part" AS "Part") ELSE NULL END) FROM table(XMLDATA."Item") p |
/PurchaseOrder/Item/Part/text() |
SELECT XMLAgg(XMLElement("", p."Part"))
FROM table(XMLDATA."Item") p
|
node_exists is used for illustration only. It represents an Oracle XML DB implementation that uses its first argument, the PD column, to determine whether or not its second argument node exists. It returns true if so, and false if not.Example 6-17 XPath Mapping for EXTRACT with Document Ordering Preserved
Using the mapping in Table 6-7, consider this query that extracts the PONum element, where the purchase order contains a part with price greater than 2000:
SELECT extract(OBJECT_VALUE, '/PurchaseOrder[Item/Part > 2000]/PONum') FROM purchaseorder_table;
This query would become something like the following:
SELECT (SELECT CASE WHEN node_exists(p.XMLDATA.SYS_XDBPD$, 'PONum')
THEN XMLElement("PONum", p.XMLDATA."PONum")
ELSE NULL END
FROM DUAL
WHERE exists(SELECT NULL FROM table(XMLDATA."Item") p
WHERE p."Part" > 2000))
FROM purchaseorder_table p;
If attribute SYS_XDBPD$ does not exist (that is, if the XML schema specifies maintainDOM="false"), then NULL scalar columns map to simpleType elements that do not exist. Hence you do not need to check for the node existence using attribute SYS_XDBPD$. Table 6-8 shows the mapping for function existsNode in the absence of SYS_XDBPD$.
Table 6-8 XPath Mapping for EXTRACT Without Document Ordering Preserved
| XPath | Equivalent to |
|---|---|
/PurchaseOrder |
XMLForest(XMLDATA AS "PurchaseOrder") |
/PurchaseOrder/@PurchaseDate |
XMLForest(XMLDATA."PurchaseDate" AS "PurchaseDate") |
/PurchaseOrder/PONum |
XMLForest(XMLDATA."PONum" AS "PONum") |
/PurchaseOrder[PONum = 2100] |
SELECT XMLForest(XMLDATA AS "PurchaseOrder") FROM DUAL WHERE XMLDATA."PONum" = 2100 |
/PurchaseOrder [PONum = 2100]/@PurchaseDate |
SELECT XMLForest(XMLDATA."PurchaseDate" AS "PurchaseDate "") FROM DUAL WHERE XMLDATA."PONum" = 2100 |
/PurchaseOrder/PONum/text() |
XMLForest(XMLDATA.PONum AS "") |
/PurchaseOrder/Item |
SELECT XMLAgg(XMLForest(value(p) AS "Item") FROM table(XMLDATA."Item") p |
/PurchaseOrder/Item/Part |
SELECT XMLAgg(XMLForest(p."Part" AS "Part") FROM table(XMLDATA."Item") p |
/PurchaseOrder/Item/Part/text() |
SELECT XMLAgg(XMLForest(p. "Part" AS "Part")) FROM table(XMLDATA."Item") p |
You can use SQL function XMLSequence in conjunction with SQL functions extract and table to unnest XML collection values. When used with schema-based storage, these functions also get rewritten to access the underlying relational collection storage.
For example, this query obtains the price and part numbers of all items in a relational form:
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') AS ponum,
extractValue(value(it), '/Item/Part') AS part,
extractValue(value(it), '/Item/Price') AS price
FROM purchaseorder,
table(XMLSequence(extract(OBJECT_VALUE, '/PurchaseOrder/Item'))) it;
PONUM PART PRICE
----- -------------------- ---------
1001 9i Doc Set 2550
1001 8i Doc Set 350
In this example, SQL function extract returns a fragment containing the list of Item elements. Function XMLSequence converts the fragment into a collection of XMLType values one for each Item element. Function table converts the elements of the collection into rows of XMLType. The XML data returned from table is used to extract the Part and the Price elements.
The applications of functions extract and XMLSequence are rewritten to a simple SELECT operation from the item_nested nested table.
EXPLAIN PLAN
FOR SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') AS ponum,
extractValue(value(it) , '/Item/Part') AS part,
extractValue(value(it), '/Item/Price') AS price
FROM purchaseorder,
table(XMLSequence(extract(OBJECT_VALUE, '/PurchaseOrder/Item'))) it;
Explained
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | ITEM_NESTED |
| 3 | TABLE ACCESS BY INDEX ROWID | PURCHASEORDER |
|* 4 | INDEX UNIQUE SCAN | SYS_C002973 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id)
--------------------------------------------------
4 - access("NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0001100012$")
The EXPLAIN PLAN output shows that the optimizer is able to use a simple nested-loops join between nested table item_nested and table purchaseorder. You can also query the Item values further and create appropriate indexes on the nested table to speed up such queries.
For example, to search on the price to get all the expensive items, we could create an index on the Price column of the nested table. The following EXPLAIN PLAN uses a price index to obtain the list of items and then joins with table purchaseorder to obtain the PONum value.
CREATE INDEX price_index ON item_nested ("Price");
Index created.
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/PONum') AS ponum,
extractValue(value(it), '/Item/Part') AS part,
extractValue(value(it), '/Item/Price') AS price
FROM purchaseorder,
table(XMLSequence(extract(OBJECT_VALUE, '/PurchaseOrder/Item'))) it
WHERE extractValue(value(it), '/Item/Price') > 2000;
Explained.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS BY INDEX ROWID | ITEM_NESTED |
|* 3 | INDEX RANGE SCAN | PRICE_INDEX |
| 4 | TABLE ACCESS BY INDEX ROWID | PURCHASEORDER |
|* 5 | INDEX UNIQUE SCAN | SYS_C002973 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_NESTED"."Price">2000)
5 - access("NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0001100012$")
SQL function updateXML must satisfy the following conditions for it to use rewrite optimization:
The XMLType argument must be based on a registered XML schema.
The XMLType argument must also be the target of the UPDATE operation. For example:
UPDATE purchaseorder_table SET OBJECT_VALUE = updateXML(OBJECT_VALUE,...);
XPath arguments must all be different (no duplicates).
XPath arguments must otherwise be rewritable, as described in "Which XPath Expressions Are Rewritten?".
XPath arguments must target only text nodes or attribute nodes.
XPath arguments cannot target nodes that have default values (as defined in the XML schema).
XPath arguments must not have a positional predicate (for example, foo[2]).
If an XPath argument has a predicate, the predicate must not come before a collection.
For example, /PurchaseOrder/LineItems[@MyAtt="3"]/LineItem will not be rewritten, because the predicate occurs before the LineItem collection. (This assumes an XML schema where LineItems has an attribute MyAtt.)
If an XPath argument references a collection, the collection must be stored as a separate table (varray or nested table), not out of line (REF storage) or in line.
If an XPath argument references a collection, the collection must not be scalar (simpleType with maxOccurs > 1).
|
See Also: Example 6-2, Example 6-3, Example 3-34, and Example 3-34 for examples of rewritingupdateXML expressions |
SQL function deleteXML must satisfy the following conditions for it to use rewrite optimization:
The XMLType argument must be based on a registered XML schema.
The XMLType argument must also be the target of the UPDATE operation. For example:
UPDATE purchaseorder_table SET OBJECT_VALUE = updateXML(OBJECT_VALUE,...);
XPath arguments must otherwise be rewritable, as described in "Which XPath Expressions Are Rewritten?".
The XPath argument must not have a positional predicate (for example, foo[2]).
If the XPath argument has a predicate, the predicate must not come before a collection.
For example, /PurchaseOrder/LineItems[@MyAtt="3"]/LineItem will not be rewritten, because the predicate occurs before the LineItem collection. (This assumes an XML schema where LineItems has an attribute MyAtt.)
The XPath argument must target an unbounded collection (element with maxOccurs = "unbounded").
The XPath argument must not target a choice of collections, as defined in the XML schema.
The parent of the targeted collection must be defined in the XML schema with annotation maintainDOM = "false".
If an XPath argument references a collection, the collection must be stored as a separate table (varray or nested table), not out of line (REF storage) or in line.
If an XPath argument references a collection, the collection must not be scalar (simpleType with maxOccurs > 1).
