XML DB Database environment: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
I am using the following PurchaseOrder Schema (same as the one provided in Oracle Documentation).
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true">
<xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/>
<xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T">
<xs:sequence>
<xs:element name="Reference" type="ReferenceType" minOccurs="1" xdb:SQLName="REFERENCE"/>
<xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/>
<xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/>
<xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/>
<xs:element name="User" type="UserType" minOccurs="1" xdb:SQLName="USERID"/>
<xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/>
<xs:element name="ShippingInstructions" type="ShippingInstructionsType" xdb:SQLName="SHIPPING_INSTRUCTIONS"/>
<xs:element name="SpecialInstructions" type="SpecialInstructionsType" xdb:SQLName="SPECIAL_INSTRUCTIONS"/>
<xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
<xs:sequence>
<xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
<xs:sequence>
<xs:element name="Description" type="DescriptionType" xdb:SQLName="DESCRIPTION"/>
<xs:element name="Part" type="PartType" xdb:SQLName="PART"/>
</xs:sequence>
<xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER"/>
</xs:complexType>
<xs:complexType name="PartType" xdb:SQLType="PART_T">
<xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:minLength value="10"/>
<xs:maxLength value="14"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/>
<xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/>
</xs:complexType>
<xs:simpleType name="ReferenceType">
<xs:restriction base="xs:string">
<xs:minLength value="18"/>
<xs:maxLength value="30"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T">
<xs:sequence>
<xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V">
<xs:complexType xdb:SQLType="ACTION_T">
<xs:sequence>
<xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/>
<xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T">
<xs:all>
<xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/>
<xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/>
<xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/>
</xs:all>
</xs:complexType>
<xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T">
<xs:sequence>
<xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/>
<xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/>
<xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/>
</xs:sequence>
</xs:complexType>
<xs:simpleType name="moneyType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="2"/>
<xs:totalDigits value="12"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="quantityType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="4"/>
<xs:totalDigits value="8"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="UserType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="10"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="RequestorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="128"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="CostCenterType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="4"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="VendorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="PurchaseOrderNumberType">
<xs:restriction base="xs:integer"/>
</xs:simpleType>
<xs:simpleType name="SpecialInstructionsType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="NameType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="AddressType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="TelephoneType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="24"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DateType">
<xs:restriction base="xs:date"/>
</xs:simpleType>
<xs:simpleType name="CommentsType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DescriptionType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>
Tables are created with the following Script
CREATE TABLE purchaseorder OF XMLType XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY "XMLDATA"."ACTIONS"."ACTION"
STORE AS TABLE action_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
ORGANIZATION INDEX OVERFLOW)
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM"
STORE AS TABLE lineitem_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
ORGANIZATION INDEX OVERFLOW);
I have then populated PurchaseOrder table to contain 10 PurchasesOrders each of which is 1.7MB in size. the Bulk of this size in each PurchaseOrder is
contributed by LineItems.Each PurchaseOrder has 12000 LineItems.
I am aiming to perform an update to a spcific LineItem like the following
UPDATE purchaseorder
SET OBJECT_VALUE = updateXML(OBJECT_VALUE,'/PurchaseOrder/LineItems/LineItem/Description[text()="abcd"]/text()','The Lady Vanishes')
WHERE
EXTRACTVALUE (OBJECT_VALUE, '/PurchaseOrder/Reference') = 'ORD1000000000000010';
This is taking a lot of time. Explain Plan and Oracle Trace Files reveal the following.
Can you please let me know if there is any way to speed up the updateXML query?*
Oracle Trace:
********************************************************************************
UPDATE purchaseorder
SET OBJECT_VALUE = updateXML(OBJECT_VALUE,'/PurchaseOrder/LineItems/LineItem/Description[text()="abcd"]/text()','The Lady Vanishes')
WHERE
EXTRACTVALUE (OBJECT_VALUE, '/PurchaseOrder/Reference') = 'ORD1000000000000010'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.54 0.72 179 333 12 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.54 0.72 179 333 12 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 73
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE PURCHASEORDER (cr=1463 pr=521 pw=0 time=5183214 us)
1 TABLE ACCESS FULL PURCHASEORDER (cr=8 pr=6 pw=0 time=24130 us)
1 INDEX RANGE SCAN SYS_IOT_TOP_74253 (cr=1 pr=0 pw=0 time=44 us)(object id 74257)
12000 INDEX RANGE SCAN SYS_IOT_TOP_74258 (cr=315 pr=169 pw=0 time=1539628 us)(object id 74264)
********************************************************************************
INSERT /*+ NO_PARTIAL_COMMIT REF_CASCADE_CURSOR */ INTO
"IPCUSER1"."LINEITEM_TABLE"("NESTED_TABLE_ID","SYS_NC_ARRAY_INDEX$",
"SYS_NC_ROWINFO$")
VALUES
(:1,:2,:3)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.89 2.57 193 892 79177 12000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.89 2.57 193 892 79177 12000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 73 (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.54 0.72 179 333 12 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.54 0.72 179 333 12 1
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 11 0.00 0.00 0 0 0 0
Execute 11 1.23 4.41 339 1105 128303 24002
Fetch 12 0.00 0.04 3 25 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 34 1.23 4.46 342 1130 128303 24009
Misses in library cache during parse: 0
4 user SQL statements in session.
9 internal SQL statements in session.
13 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Explain Plan:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 720167670
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 105 | 3 (0)| 00:00:01 |
| 1 | UPDATE | PURCHASEORDER | | | | |
|* 2 | TABLE ACCESS FULL | PURCHASEORDER | 1 | 105 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_IOT_TOP_74253 | 1 | 28 | 1 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| SYS_IOT_TOP_74258 | 12000 | 632K| 36 (3)| 00:00:01 |
--------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PURCHASEORDER"."SYS_NC00009$"='ORD1000000000000010')
3 - access("NESTED_TABLE_ID"=:B1)
4 - filter("NESTED_TABLE_ID"=:B1)
18 rows selected.
Doesn't the following experiment prove that XPath Rewrite is not happening in this quey? Is it expected?
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> ALTER SESSION SET EVENTS '19021 trace name context forever, Level 0x20';
Session altered.
SQL> UPDATE purchaseorder
2 SET OBJECT_VALUE = updateXML(OBJECT_VALUE,'/PurchaseOrder/LineItems/LineItem/Description[text()="abcd"]/text()','The Lady Vanishes
')
3 WHERE
4 EXTRACTVALUE (OBJECT_VALUE, '/PurchaseOrder/Reference') = 'ORD1000000000000010';
UPDATE purchaseorder
*
ERROR at line 1:
ORA-19022: XML XPath functions are disabled
Edited by: user12835109 on 22-Mar-2010 05:14
Updated the contents after running exec dbms_stats.gather_schema_stats
Edited by: user12835109 on 22-Mar-2010 23:54
Added results on an experiment to check if XPath Rewrite is happening