40183 : Oracle XML DB Performance and Tuning

2. Session id: 40183 :Oracle XML DB Performance and Tuning Mark D. Drake Senior Product Manager

3. Introduction

4. Managing XML Content • XML Stored as XMLType • SQL functions allow XPath expressions to operate on XML content • extract(), extractValue(), updateXML(), existsNode() • Two modes of operation • Functional Evaluation • Query-Rewrite

5. Functional Evaluation • Works with all XMLType columns and Tables. • Parse the XML document and construct a DOM • Evaluate XPath expressions using DOM API • Based on proven XDK ‘C’ XML Parser and DOM • Most appropriate to operations on small numbers of small documents

6. XPath Re-write • Query and Update operations • Requires Schema based XMLType • Operates directly on the underlying Object Store • Relational database engine used to evaluate XPath expression • XPath Expression translated into equivalent SQL • Eliminates need to parse XML and construct DOM • Allows efficient operations on large collections of XML Documents

7. Collections

8. What is a collection • A Collection is a set of elements with the same name and similar structure <LineItem ItemNumber="1"> <Description>Duel at Ichijoji Temple</Description> <Part Id="37429125526" UnitPrice="29.95" Quantity="3"/ </LineItem> <LineItem ItemNumber="2"> <Description>The Red Shoes</Description> <Part Id="37429128220" UnitPrice="39.95" Quantity="4"/> </LineItem>

9. Defining a Collection • In XML Schema, Collections are defined using maxOccurs attribute <xs:complexType name="LineItemsType“ <xs:sequence> <xs:element name="LineItem“ type="LineItemType“ maxOccurs="unbounded"/> </xs:sequence> </xs:complexType>

10. Working with Collections • A Typical XML document contains one or more collections • Collections may be Nested • A collection may contain a collections • Query and Updating collections present the biggest challenge

11. Querying Collections • Sample Query on a Collection Get the Reference and Item Number of each LineItem that contains a reference to Part “717951002372“ XPATH := '/PurchaseOrder/LineItems/LineItem[Part/@Id="717951002372"]'; select extractValue(p.object_value,'/PurchaseOrder/Reference') extractValue(value(l),'/LineItem/@ItemNumber') from PURCHASEORDER p, table (xmlsequence(extract(p.object_value,XPATH))) l

12. Querying Collections • Use the extract()function to get the set of nodes that match an XPath expression XPATH := '/PurchaseOrder/LineItems/LineItem[Part/@Id="717951002372"]'; select extractValue(object_value,'/PurchaseOrder/Reference') extractValue(value(l),'/LineItem/@ItemNumber') from PURCHASEORDER p, table (xmlsequence(extract(p.object_value,XPATH))) l

13. Querying Collections • The extract function will be evaluated for each document in the table. • An XMLType will be generated from each document that contains a node matching the XPath expression. • The XML Type will be contain an XML Fragment • The Fragment will consist of the set of nodes that match the specified XPATH expression.

14. Querying Collections • Use the xmlSequencefunction to generate a set of XMLType Objects from each fragment XPATH := '/PurchaseOrder/LineItems/LineItem[Part/@Id="717951002372"]'; select extractValue(object_value,'/PurchaseOrder/Reference') extractValue(value(l),'/LineItem/@ItemNumber') from PURCHASEORDER p, table (xmlsequence(extract(p.object_value,XPATH))) l

15. Querying Collections • xmlSequence will create a set of XMLType objects from the result of the extract. • Each set will contain one XMLType object for each top level node in the XML fragment. • Each XMLType will consist of a well-formed XML document.

16. Querying Collections • Use the TABLEfunction toconvert the set of XMLType objects into a virtual table XPATH := '/PurchaseOrder/LineItems/LineItem[Part/@Id="717951002372"]'; select extractValue(p.object_value,'/PurchaseOrder/Reference') extractValue(value(l),'/LineItem/@ItemNumber') from PURCHASEORDER p,table (xmlsequence(extract(p.object_value,XPATH)))l • Join the virtual table with the base table to get required result set

17. Managing Collections

18. Collection Management • Four options for storing collections • BLOB Storage • SQL objects stored in a single BLOB Column • Nested Table Storage • SQL objects stored as rows in a Nested Table • CLOB Storage • XML text • XMLType Table Storage • XMLType objects stored in an XMLType Table

19. Why different storage models ? • Applications have different requirements • Querying within in a collection • Indexing within a collection • Updating within a collection • Choosing the correct storage model is vital

20. Collections and VARRAYS • VARRAY: SQL concept for managing collections • VARRAY can be collection of Scalars or collection of SQL Type • AUTHOR_V VARRAY(n) of VARCHAR2(40) • LINE_ITEM_V VARRAY(n) of LINE_ITEM_T • In XML DB the members of a collection are mapped to a SQL VARRAY.

21. LOB

22. LOB model • Default : No Annotation Required • Collection stored as VARRAY of SQL Objects • SQL Objects serialized into a single BLOB • Indexing using CtxXPath index • No partial updates • update requires re-writing entire BLOB • Query re-write for XPath expressions

23. LOB : Objects <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:comlexType> SQL> desc LINEITEMS_T LINEITEMS_T is NOT FINAL Name Null? Type ------------------- -------- ------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T LINEITEM LINEITEM_V

24. LOB : Storage SQL> describe LINEITEM_V LINEITEM_V VARRAY(2147483647) OF LINEITEM_T LINEITEM_T is NOT FINAL Name Null? Type ------------------- -------- ------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T ITEMNUMBER NUMBER(38) DESCRIPTION VARCHAR2(256 CHAR) PART PART_T

25. ABANDA … LOB Storage Reference User Id … LineItem ABANDA-20.. LINEITEM_V ( LINEITEM_T(...), LINEITEM_T(...), LINEITEM_T(...), LINEITEM_T(...) )

26. LOB : Query Re-Write SELECT P.XMLDATA."Reference", L."ItemNumber" FROM PURCHASEORDER P, TABLE (P.XMLDATA."LineItems"."LineItem") L WHERE L."Part"."PartNumber" = '717951002372' • Evaluated using SQL to process VARRAY of LINEITEM objects • Entire VARRAY has to be loaded into Memory

27. NestedTables

28. Nested Table model • Annotation: xdb:storeVarrayAsTable=“true” • Collection converted into a set of SQL objects • Each SQL Object stored as a separate row in a Nested Table. • Nesting of Nested Tables for collections within collections • SQL Based Fragment access and update • Index with B-Tree, Functional and CtxXPath indexes

29. Nested Table : Objects <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:comlexType> SQL> desc LINEITEMS_T LINEITEMS_T is NOT FINAL Name Null? Type ------------------- -------- ------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T LINEITEM LINEITEM_V

30. Nested Table : Objects SQL> describe LINEITEM_V LINEITEM_V VARRAY(2147483647) OF LINEITEM_T LINEITEM_T is NOT FINAL Name Null? Type ------------------- -------- ------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T ITEMNUMBER NUMBER(38) DESCRIPTION VARCHAR2(256 CHAR) PART PART_T SQL> describe LINEITEM_TABLE Name Null? Type ------------------- -------- ------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T ITEMNUMBER NUMBER(38) DESCRIPTION VARCHAR2(256 CHAR) PART PART_T

31. ABANDA … Nested Table : Storage Reference User Id … LineItem 1 ABANDA-20.. ID ItemNumber Description Part Good Morn… … 1 1 Uriah Hee… … 1 2 Sisters … 1 3 The Prince… … 1 4

32. Nested Table : Query Re-Write SELECT P.XMLDATA."Reference", L.ITEMNUMBER FROM PURCHASEORDER P, LINEITEM_TABLE l WHERE L."Part."PartNumber"='717951002372' AND L.NESTED_TABLE_ID = SETID(P.XMLDATA."LineItems"."LineItem“) • Evaluated using SQL to query contents of the Nested Table

33. Working with Nested Tables • xdb:storeVarrayAsTable=“true” is a “shotgun” approach • Every collection stored as a Nested Table • Nested Tables have ‘unfriendly’ names • Simple cases • Rename tables created by Schema Registration • Complex cases • Create tables manually using CREATE TABLE

34. Renaming Nested Tables • Query USER_NESTED_TABLES to find the direct descendant of the Parent Table SQL> select PARENT_TABLE_COLUMN, TABLE_NAME 2 from USER_NESTED_TABLES 3 where PARENT_TABLE_NAME = 'PURCHASEORDER' 4 / PARENT_TABLE_COLUMN TABLE_NAME -------------------------------- ----------------------- "XMLDATA"."LINEITEMS"."LINEITEM" SYS_NTDfLwYKWcRxmyssvmCvRMqw== "XMLDATA"."ACTIONS"."ACTION" SYS_NTqxF5epLrSniXaAWq5A4Uig==

35. Renaming Nested Table s SQL> rename "SYS_NTDfLwYKWcRxmyssvmCvRMqw==" to LINEITEM_TABLE 2 / Table renamed. SQL> rename "SYS_NTqxF5epLrSniXaAWq5A4Uig==" to ACTION_TABLE 2 / Table renamed. SQL> select PARENT_TABLE_COLUMN, TABLE_NAME 2 from USER_NESTED_TABLES 3 where PARENT_TABLE_NAME = 'PURCHASEORDER' 4 / PARENT_TABLE_COLUMN TABLE_NAME --------------------------------- ---------------------- "XMLDATA"."ACTIONS"."ACTION" ACTION_TABLE "XMLDATA"."LINEITEMS"."LINEITEM" LINEITEM_TABLE

36. Multi-Level Nested Tables • Collections within Collections result in Storage Models consisting of multiple levels of Nested Tables • Repeat the process using the Nested Table name as the PARENT_TABLE_NAME

37. Manual Table Creation • Can create Nested Tables as part of create Table • Need to know the name of the VARRAYs that will stored as Nested Tables • Allows flexibility, only use Nested Table Storage only where it adds value • Must use Nested Table storage with collections that contain an element stored as a CLOB.

38. Manual Table Creation Example create table PURCHASEORDER of XMLTYPE XMLSCHEMA "http://localhost:8080/home/SCOTT/xsd/purchaseOrder.xsd" ELEMENT "PurchaseOrder" varray "XMLDATA"."ACTIONS"."ACTION" store as table ACTION_TABLE ( (primary key (NESTED_TABLE_ID, ARRAY_INDEX)) organization index overflow ) varray "XMLDATA"."LINEITEMS"."LINEITEM" store as table LINEITEM_TABLE ( (primary key (NESTED_TABLE_ID, ARRAY_INDEX)) organization index overflow )

39. CLOB

40. CLOB model • Annotation: xdb:SQLType=“CLOB” • Collection stored as XML text • Reduced complexity • Fast Ingestion and Retrieval of Collection • Lower memory and processing requirements • Index using CTXPATH index • No Optimization of Fragment Level operations • XPath based Functions evaluated via DOM API • Fetching Fragments requires Parsing • Updating Requires Parsing and Rewriting

41. CLOB : Objects <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T"> <xs:sequence> <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLType="CLOB"/> </xs:sequence> </xs:comlexType> SQL> describe LINEITEMS_T LINEITEMS_T is NOT FINAL Name Null? Type ------------------- -------- ------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T LINEITEM CLOB

42. ABANDA … CLOB : Storage Reference User Id … LineItem ABANDA-20.. <LineItem ItemNumber="1"> <Description>Good … <Part Id="3742914 …</LineItem> <LineItem ItemNumber="2“ <Description>Uriah.. <Part Id="6950030…</LineItem>

43. CLOB: Query Re-Write SELECT P.XMLDATA."Reference", EXTRACTVALUE(value(l),'/LineItem/@ItemNumber') FROM PURCHASEORDER P, TABLE ( XMLSEQUENCE ( EXTRACT ( VALUE(P), '/PurchaseOrder/LineItems/LineItem/' || 'Part/@Id="717951002372"]' ) ) ) l • Evaluated using by Parsing CLOB and using DOM API

44. XMLTypeTable

45. XMLType Table model • Annotation xdb:SQLInline=“false” • Also known as ‘out-of-line’ storage • Collection converted into a set of XMLType objects stored in a separate XMLType table. • Parent table contains a VARRAY of REF XMLTYPE that points to rows in the child table • Index using B-Tree, Functional and CtxXPath indexes • SQL Based Fragment access and update. • Must allow Schema Registration to create the Tables.

46. XMLType Table : Objects <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T"> <xs:sequence> <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLInline="false“ xdb:defaultTable="LINEITEM_TABLE"/> </xs:sequence> </xs:comlexType> SQL> describe LINEITEMS_T LINEITEMS_T is NOT FINAL Name Null? Type ------------------- -------- ------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T LINEITEM XDB.XDB$XMLTYPE_REF_LIST_T

47. XMLType Table : Objects SQL> describe XDB.XDB$XMLTYPE_REF_LIST_T XDB.XDB$XMLTYPE_REF_LIST_T VARRAY(2147483647) OF REF XMLTYPE SQL> describe LINEITEM_TABLE Name Null? Type ------------------------- -------- ---------------------------- TABLE of SYS.XMLTYPE( XMLSchema "http://localhost:8080/home/SCOTT/xsd/purchaseOrder.xsd" Element "LineItem“) STORAGE Object-relational TYPE "LINEITEM_T"

48. XMLType with Ref Table • Combine with xdb:storeVarrayAsTable=“true” to force VARRAY of REFS to be stored in a nested table • Each row in the nested table consists of SETID and a REF to a ROW in the child table • Scope the REFS in the child table to allow full optimization of queries rename "SYS_NTDfLwYKWcRxmyssvmCvRMqw==" to LINEITEM_REF_TABLE alter table LINEITEM_REF_TABLE add (scope for (COLUMN_VALUE) is LINEITEM_TABLE)

49. Scope REF of XMLType select PARENT_TABLE_COLUMN, TABLE_NAME from USER_NESTED_TABLES where PARENT_TABLE_NAME = 'PURCHASEORDER‘/ PARENT_TABLE_COLUMN TABLE_NAME -------------------------------- ----------------------- "XMLDATA"."LINEITEMS"."LINEITEM" SYS_NTDfLwYKWcRxmyssvmCvRMqw== rename "SYS_NTDfLwYKWcRxmyssvmCvRMqw==" to LINEITEM_REF_TABLE /alter table LINEITEM_REF_TABLE add (scope for (COLUMN_VALUE) is LINEITEM_TABLE)/create index LINEITEM_REF_INDEX on LINEITEM_REF_TABLE (COLUMN_VALUE) /

50. XMLType Table : Query Rewrite SELECT P.XMLDATA."Reference", L.XMLDATA."Item" FROM PURCHASEORDER p, TABLE("P.XMLDATA."LineItems"."LineItem") R, LINEITEM_TABLE L WHERE L."Part"."PartNumber" = '717951002372' AND VALUE(R)= Ref(l) • Evaluated using SQL to query contents of the XMLType table