OS & Oracle

XMLType is an abstract type for programming, whose storage model can be unstructured storage or structure storage. 

Oracle XML DB uses XML schema as a mechanism for defining how the contents of an XMLType should be stored inside the database. Currently Oracle XML DB
provides two options:

1. Unstructured storage. The content of the XMLType is persisted as XML text using a CLOB datatype. This option is available for non-schema-based and schema-based XML content. When the XML is to be stored and retrieved as complete documents, unstructured storage may be the best solution as it offers the fastest rates of throughput when storing and retrieving XML content.
2. Structured storage. The content of the XMLType is persisted as a set of SQL objects. The structured storage option is only available when the XMLType table or column has been constrained to a global element defined by XML schema. If there is a need to extract or update sections of the document, perform XSL transformation on the document, or work through the DOM API, then structured storage may be the preferred storage type. Structured storage allows all these operations to take place more efficiently but at a greater overhead when storing and retrieving the entire document.

Let’s go to the index topic. This is the range of structured storage. Similar to the regular index, we can create an index when we create the table, or we can create an index using create index statement. Below is an example using create index.

CREATE INDEX ipurchaseorder_rejectedby
ON purchaseorder (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reject/User'));

One limitation is that this require the element to index must occur only once, as extractValue can’t return multiple values.

If the elements occurs multiple times, the index must be create when creating the table using nested table for the index element, one such example is as below:

CREATE TABLE purchaseorder OF XMLType
XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/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);

In this way, the indexes for action and lineitem is setup when creating the table.

This CREATE TABLE statement above is equivalent to the CREATE TABLE statement automatically generated by Oracle XML DB when the schema annotation storeVarrayAsTable="true" is included in the root element of the PurchaseOrder XML schema (and genTables="true" is set during schema registration). When this annotation is used, the nested tables generated by the XML schema registration process are given system-generated names, which can be difficult to work with. You can give them more meaningful names using the SQL statement RENAME TABLE.