Jerry Held

1
(No Transcript)
2
40183 Oracle XML DB Performance and Tuning
Session id

• 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
• ltLineItem ItemNumber"1"gt
• ltDescriptiongtDuel at Ichijoji
  Templelt/Descriptiongt
• ltPart Id"37429125526" UnitPrice"29.95"
  Quantity"3"/
• lt/LineItemgt
• ltLineItem ItemNumber"2"gt
• ltDescriptiongtThe Red Shoeslt/Descriptiongt
• ltPart Id"37429128220" UnitPrice"39.95"
  Quantity"4"/gt
• lt/LineItemgt

9
Defining a Collection

• In XML Schema, Collections are defined using
  maxOccurs attribute
• ltxscomplexType name"LineItemsType
• ltxssequencegt
• ltxselement name"LineItem
• type"LineItemType
• maxOccurs"unbounded"/gt
• lt/xssequencegt
• lt/xscomplexTypegt

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/LineItemPart/_at_Id"71795
  1002372"'
• select extractValue(p.object_value,'/PurchaseOrder
  /Reference') extractValue(value(l),'/LineIt
  em/_at_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/LineItemPart/_at_Id"71795
  1002372"'
• select extractValue(object_value,'/PurchaseOrder/R
  eference') extractValue(value(l),'/LineItem
  /_at_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 xmlSequence function to generate a set of
  XMLType Objects from each fragment
• XPATH
• '/PurchaseOrder/LineItems/LineItemPart/_at_Id"71795
  1002372"'
• select extractValue(object_value,'/PurchaseOrder/R
  eference') extractValue(value(l),'/LineItem
  /_at_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 TABLE function to convert the set of
  XMLType objects into a virtual table
• XPATH
• '/PurchaseOrder/LineItems/LineItemPart/_at_Id"71795
  1002372"'
• select extractValue(p.object_value,'/PurchaseOrder
  /Reference') extractValue(value(l),'/LineIt
  em/_at_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

• ltxscomplexType name"LineItemsType"
  xdbSQLType"LINEITEMS_T"gt
• ltxssequencegt
• ltxselement name"LineItem"
  type"LineItemType"
  maxOccurs"unbounded"
  xdbSQLName"LINEITEM"
  xdbSQLCollType"LINEITEM_V"/gt
• lt/xssequencegt
• lt/xscomlexTypegt
• SQLgt desc LINEITEMS_T
• LINEITEMS_T is NOT FINAL
• Name Null? Type
• ------------------- -------- -------
• SYS_XDBPD XDB.XDBRAW_LIST_T
• LINEITEM LINEITEM_V

24
LOB Storage

• SQLgt describe LINEITEM_V
• LINEITEM_V VARRAY(2147483647) OF LINEITEM_T
• LINEITEM_T is NOT FINAL
• Name Null? Type
• ------------------- -------- -------
• SYS_XDBPD XDB.XDBRAW_LIST_T
• ITEMNUMBER NUMBER(38)
• DESCRIPTION VARCHAR2(256 CHAR)
• PART PART_T

25
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 xdbstoreVarrayAsTabletrue
• 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

• ltxscomplexType name"LineItemsType"
  xdbSQLType"LINEITEMS_T"gt
• ltxssequencegt
• ltxselement name"LineItem"
  type"LineItemType"
  maxOccurs"unbounded"
  xdbSQLName"LINEITEM"
  xdbSQLCollType"LINEITEM_V"/gt
• lt/xssequencegt
• lt/xscomlexTypegt
• SQLgt desc LINEITEMS_T
• LINEITEMS_T is NOT FINAL
• Name Null? Type
• ------------------- -------- -------
• SYS_XDBPD XDB.XDBRAW_LIST_T
• LINEITEM LINEITEM_V

30
Nested Table Objects

• SQLgt describe LINEITEM_V
• LINEITEM_V VARRAY(2147483647) OF LINEITEM_T
• LINEITEM_T is NOT FINAL
• Name Null? Type
• ------------------- -------- -------
• SYS_XDBPD XDB.XDBRAW_LIST_T
• ITEMNUMBER NUMBER(38)
• DESCRIPTION VARCHAR2(256 CHAR)
• PART PART_T
• SQLgt describe LINEITEM_TABLE
• Name Null? Type
• ------------------- -------- -------
• SYS_XDBPD XDB.XDBRAW_LIST_T
• ITEMNUMBER NUMBER(38)
• DESCRIPTION VARCHAR2(256 CHAR)
• PART PART_T

31
Nested Table Storage
Reference
User Id

LineItem
1
ABANDA-20..
ID
ItemNumber
Description
Part
Good Morn

1
1
Uriah Hee

2
1
Sisters

3
1
The Prince

4
1
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

• xdbstoreVarrayAsTabletrue 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
• SQLgt 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

• SQLgt rename "SYS_NTDfLwYKWcRxmyssvmCvRMqw" to
  LINEITEM_TABLE
• 2 /
• Table renamed.
• SQLgt rename "SYS_NTqxF5epLrSniXaAWq5A4Uig" to
  ACTION_TABLE
• 2 /
• Table renamed.
• SQLgt 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//localhost8080/home/SCOTT/xsd/pu
  rchaseOrder.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 xdbSQLTypeCLOB
• 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

• ltxscomplexType name"LineItemsType"
  xdbSQLType"LINEITEMS_T"gt
• ltxssequencegt
• ltxselement name"LineItem"
  type"LineItemType"
  maxOccurs"unbounded"
  xdbSQLName"LINEITEM"
  xdbSQLType"CLOB"/gt
• lt/xssequencegt
• lt/xscomlexTypegt
• SQLgt describe LINEITEMS_T
• LINEITEMS_T is NOT FINAL
• Name Null? Type
• ------------------- -------- -------
• SYS_XDBPD XDB.XDBRAW_LIST_T
• LINEITEM CLOB

42
CLOB Storage
Reference
User Id

LineItem
ABANDA-20..
ltLineItem ItemNumber"1"gt ltDescriptiongtGood
ltPart Id"3742914 lt/LineItemgt ltLineItem
ItemNumber"2 ltDescriptiongtUriah.. ltPart
Id"6950030lt/LineItemgt
43
CLOB Query Re-Write

• SELECT P.XMLDATA."Reference",
• EXTRACTVALUE(value(l),'/LineItem/_at_ItemNum
  ber')
• FROM PURCHASEORDER P,
• TABLE ( XMLSEQUENCE (
• EXTRACT
• ( VALUE(P),
  '/PurchaseOrder/LineItems/LineItem/'
  'Part/_at_Id"717951002372"' )
  ) ) l
• Evaluated using by Parsing CLOB and using DOM API

44
XMLTypeTable
45
XMLType Table model

• Annotation xdbSQLInlinefalse
• 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

• ltxscomplexType name"LineItemsType"
  xdbSQLType"LINEITEMS_T"gt
• ltxssequencegt
• ltxselement name"LineItem"
  type"LineItemType"
  maxOccurs"unbounded"
  xdbSQLName"LINEITEM"
  xdbSQLInline"false
  xdbdefaultTable"LINEITEM_TABLE"/gt
• lt/xssequencegt
• lt/xscomlexTypegt
• SQLgt describe LINEITEMS_T
• LINEITEMS_T is NOT FINAL
• Name Null? Type
• ------------------- -------- -------
• SYS_XDBPD XDB.XDBRAW_LIST_T
• LINEITEM XDB.XDBXMLTYPE_REF_
  LIST_T

47
XMLType Table Objects

• SQLgt describe XDB.XDBXMLTYPE_REF_LIST_T
• XDB.XDBXMLTYPE_REF_LIST_T VARRAY(2147483647)
  OF REF XMLTYPE
• SQLgt describe LINEITEM_TABLE
• Name Null? Type
• ------------------------- --------
  ----------------------------
• TABLE of SYS.XMLTYPE( XMLSchema
  "http//localhost8080/home/SCOTT/xsd/purchaseOrde
  r.xsd" Element "LineItem) STORAGE
  Object-relational TYPE "LINEITEM_T"

48
XMLType Table Storage
Reference
User Id

LineItem
Ref, Ref
ABANDA-20..
ItemNumber
Description
Part
Good Morn

1
Uriah Hee

2
Sisters

3
The Prince

4
49
XMLType with Ref Table

• Combine with xdbstoreVarrayAsTabletrue 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)

50
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)/cre
  ate index LINEITEM_REF_INDEX on
  LINEITEM_REF_TABLE (COLUMN_VALUE)
• /

51
XMLType Table Storage
Reference
User Id

LineItem
1
ABANDA-20..
ID
ItemNumber
Description
Part
Good Morn

1
1
Uriah Hee

2
1
Sisters

3
1
The Prince

4
1
52
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

53
Relative FTP Insert performance
54
Un-Indexed Query Performance
55
IndexingCollections
56
Indexing Collections

• Collections stored as LOB or CLOB can only be
  indexed using CtxXPath Index
• Collections stored as Nested Tables or
  Out-of-Line tables can be index using B-Tree
  Indexes
• Indexes on collections are created on the nested
  table or Out-of-Line Table, not the Parent Table

57
Indexing with Nested Tables

• Indexes on Nested Table are defined in terms of
  the SQL Type structure
• Nested Tables contain a foreign key called
  NESTED_TABLE_ID
• Consider adding NESTED_TABLE_ID to any secondary
  indexes

58
Nested Table Secondary Index

• create index iPartNumberIndex on LINEITEM_TABLE l
• (l.PART.PART_NUMBER, l.NESTED_TABLE_ID)
• Query Plan showing use of Index
• --------------------------------------------------
  -------------------------------------------------
• Id Operation Name
  Rows Bytes Cost (CPU) Time
• --------------------------------------------------
  -------------------------------------------------
• 0 SELECT STATEMENT
  662 17M 89 (2) 000002
• 1 NESTED LOOPS
  662 17M 89 (2) 000002
• 2 INDEX UNIQUE SCAN
  LINEITEM_TABLE_IOT 82 370K 6 (0)
  000001
• 3 INDEX RANGE SCAN
  IPARTNUMBERINDEX 33 3 (34)
  000001
• 4 TABLE ACCESS BY INDEX ROWID
  PURCHASEORDER 8 176K 2 (50)
  000001
• 5 INDEX UNIQUE SCAN
  SYS_C004261 1 1 (100)
  000001
• --------------------------------------------------
  -------------------------------------------------

59
Indexing with XMLType Tables

• Indexes on Out-Of-Line XMLType tables are created
  using XPath syntax
• Index is defined in terms of the extractValue()
  functions
• Index is not a function index. XPath re-write
  allows the index to be converted into
  Object-Relational SQL.
• XPath is from the root element of the Out-of-Line
  table, not the root of the parent document
• When storing REFs as nested table remember to
  index the nested table

60
XMLType Table Secondary Index

• create index iPartNumberIndex on LINEITEM_TABLE l
• (extractValue(value(l),'/LineItem/Part/_at_Id'))
• create index LINEITEM_REF_INDEX on
  LINEITEM_REF_TABLE
• (COLUMN_VALUE)
• Query Plan showing use of Index
• --------------------------------------------------
  --------------------------------------------------
  
• Id Operation Name
  Rows Bytes Cost (CPU) Time
  
• --------------------------------------------------
  --------------------------------------------------
  
• 0 SELECT STATEMENT
  4626 100M 591 (2) 000008
  
• 1 NESTED LOOPS
  4626 100M 591 (2) 000008
  
• 2 HASH JOIN
  571 136K 14 (15) 000001
  
• 3 TABLE ACCESS BY INDEX ROWID
  LINEITEM_TABLE 571 115K 6 (17)
  000001
• 4 INDEX RANGE SCAN
  IPARTNUMBERINDEX 571 2 (50)
  000001
• 5 INDEX FAST FULL SCAN
  LINEITEM_REF_INDEX 8168 295K 8 (13)
  000001
• 6 TABLE ACCESS BY INDEX ROWID
  PURCHASEORDER 8 176K 2 (50)
  000001
• 7 INDEX RANGE SCAN
  SYS_C004315 8 1 (100)
  000001
• --------------------------------------------------
  --------------------------------------------------

61
Indexed Query Performance
62
DOMFidelity
63
DOM Fidelity

• Multiple namespaces with Prefixes
• XMLSchemaInstance attributes
• xsinill, xsiType
• Mixed Text, Substitution groups
• Comments and Processing Instructions
• Ordering of nodes in an all, choice
• Empty Vs Missing Nodes
• Default Values

64
Dom Fidelity

• Requires additional processing and storage
  overhead
• Specific meta data maintained for each instance
• Meta data managed at the Type Level in the
  SYS_XDBPD attribute
• SYS_XDBPD is stored as a (in-line) LOB.

65
Do you need it ?

• Can disable on a Type by Type basis using
  annotation xdbmaintainDOMfalse
• Not using Namespaces
• No Comments or Processing Instructions
• Do not care about empty Vs missing elements
• Elements in an All and Choice will be ordered as
  per the Schema, not the instance
• Not worried about defaults
• Type does not allow Mixed text

66
Type defn with DOM Fidelity

• desc LINEITEMS_T
• LINEITEMS_T is NOT FINAL
• Name Null? Type
• ------------------------------- --------
  ------------------
• SYS_XDBPD
  XDB.XDBRAW_LIST_T
• LINEITEM
  LINEITEM_V
• desc LINEITEM_V
• LINEITEM_V VARRAY(2147483647) OF LINEITEM_T
• LINEITEM_T is NOT FINAL
• Name Null? Type
• ------------------------------ --------
  ------------------
• SYS_XDBPD
  XDB.XDBRAW_LIST_T
• ITEMNUMBER
  NUMBER(38)
• DESCRIPTION
  VARCHAR2(256 CHAR)
• PART PART_T

67
xdbmaintainDOM

• ltxscomplexType name"LineItemsType"
  xdbSQLType"LINEITEMS_T"
  xdbmaintainDOM"false"gt
  ltxssequencegt
• ltxselement name"LineItem"
  type"LineItemType"
  maxOccurs"unbounded" xdbSQLName"LINEITEM"
  xdbSQLCollType"LINEITEM_V"/gt
  lt/xssequencegt
• lt/xscomplexTypegt ltxscomplexType
  name"LineItemType" xdbSQLType"LINEITEM_T"
  xdbmaintainDOM"false"gt
• ltxssequencegt
• ltxselement name"Description"
  type"DescriptionType
  xdbSQLName"DESCRIPTION"/gt
• ltxselement name"Part" type"PartType"
  xdbSQLName"PART"/gt lt/xssequencegt
• ltxsattribute name"ItemNumber"
  type"xsinteger"
• xdbSQLName"ITEMNUMBER"
  xdbSQLType"NUMBER"/gt
• lt/xscomplexTypegt

68
Type defn without DOM Fidelity

• desc LINEITEMS_T
• LINEITEMS_T is NOT FINAL
• Name Null? Type
• ------------------------------- --------
  ------------------
• LINEITEM
  LINEITEM_V
• desc LINEITEM_V
• LINEITEM_V VARRAY(2147483647) OF LINEITEM_T
• LINEITEM_T is NOT FINAL
• Name Null? Type
• ------------------------------ --------
  ------------------
• ITEMNUMBER
  NUMBER(38)
• DESCRIPTION
  VARCHAR2(256 CHAR)
• PART PART_T

69
Dom Fidelity
70
HierarchicalTables
71
Hierarchical Tables

• XMLType Tables can be Hierarchically Enabled
• Table is linked to the Oracle XML DB repository
• Each Row in the table corresponds to a Resource
  (document) in the XML DB repository
• Row Level Security defined by associating an ACL
  with a resource
• DML operations on the row reflected in the
  resource (Last Modified Date, Delete)

72
Hierarchical Considerations

• Tables created by Schema Registration are
  Hierarchically Enabled
• DML operations constrained by ACL
• Table Level Triggers sync DML operations on
  base-table with XDB repository
• Cannot bypass ACL security by accessing content
  via SQL
• Delete of a row in a table will delete the
  corresponding Resource

73
ACL Management

• XML DB architected to support systems with
  thousands or millions of ACLs
• ACL based security automatically enforced by
  calling the sys_checkAcl() function
• ACL check is based on ACLOID and OID
• Dedicated ACL cache ensures that Oracle XML DB
  can locate ACLs very efficiently
• ACLOID and OID are stored as hidden columns on an
  XMLType table

74
ACL Checking

• ACL checking can affect query performance
• Need ACLOID and OID in addition to other columns
• ACL Based security can be disabled using
  dbms_xdbz.disable_hierarchy()
• Turns off ACL Evaluation and repository
  synchronization
• In some cases disabling the hierarchy can lead to
  significant performance benefits

75
A
76
(No Transcript)