XML DB Improvements - Oracle 11g

XML DB makes major advancements since its inception in Oracle 9i Database Release 2.Oracle has long offered native XML support to allow storage of XML in object-relational or character large object(CLOB)formats.

Since its introduction into the database engine,Oracle has made incredible progress toward a high-performance native storage XML engine inside the data base.Oracle even provides easy-to-implement user interfaces in Enterprise Manager Database Console to set up XML DB in the database.

This configuration includes the ability to set up HTTP and FTP access to the database. In Oracle Database 11g,XML DB adopts features such as Binary XML, XML index,repository events, XQuery compliance, and much more. These new features in Oracle Database 11g allow for simplified XML manageability and supportability for DBAs and application developers.

Repository Events

Repository operations on files and folders (resources) include check in, check out,create, delete,link,lock,open,render,submit for version control, un check out,unlink,unlock,and update. Business requirements may require certain action when particular repository operations occur.For example,you may want to make a copy of a file prior to a delete operation on a specific folder for auditing purposes.Events allow for the XML DB repository to be programmable like database triggers allow the database to be programmable.

Support Content Repository API for Java: JSR-170

Oracle Database 11g XML DB Content Connector provides support for the Content Repository API for Java Technology,also known as JSR-170.JSR-170 specifications developed under the Java Community Process(JCP) program promise a unified API for accessing any compliant repository in a vendor- or implementation-neutral fashion.

JSR-170 is often called the JDBC of content repositories.Currently,customers write to proprietary APIs from each vendor to access their content repositories.The goal of the JSR-170 specification is to create a single content API that can be used to access data in any number of content repositories.You can plug in additional repositories as needed.Oracle Database 11g provides access of the XML DB repository using the JCR 1.0 Java API.

ACL Security

Oracle Database 11g enhances the ACL-based security model.XML DB ACL now complies with the industry-standard DAV ACL specifications and security standards in a WebDAV environment. Clients will notice improved interaction with the DAV ACL security model.

In addition, the ACL security model can be extended for user-defined ACLs to secure other kind of database objects.ACLs allow for a time-based security model with this release.You can set policies to enable access to resources during a certain time period or automatically publish and expire content based on the ACL.

New XMLType

Until now,there were three XMLType storage models supported by Oracle: structured, unstructured,and hybrid.The structured XMLType implies that the XML is well defined.Data in a structured XMLType is stored as a set of objects.This is also known as object-relational storage and object-based persistence.

The unstructured XMLType is stored in character LOBs datatypes.This is also known as text-based persistence.Oracle Database 11g introduces a new XMLType datatype known in the XML industry as Binary XMLType,also recognized as post-parse persistence.

The Binary XMLType introduces advantages to the XML world in the database. Binary XML format generally reduces the verbosity of XML documents,and thus the cost of parsing is reduced.Even though the XML is stored in a parsed state,data is still accessible via XPath.No encoding checks are performed on loading.

Thus,Binary XML brings forth no overhead (or at least reduced overhead) in parsing, validation, or conversions,which saves in time in IO and CPU cycles, which therefore translates into faster load times.Binary XML is capable of preserving infoset and DOM fidelity.If the XML data is going to be retrieved, Binary XML is the new solution for Oracle Database 11g.

Binary XML is the closest thing to a universal storage model for XML data by providing the storage benefits of both structured and unstructured data. Like structured storage,you can perform piecewise updates on Binary XML. Similar to unstructured storage,Binary XML data is kept in document order. Similarly,like structured XML,data and metadata can be separated at the database level.Another great benefit of Binary XML is that you can leverage binary storage for XML schema-based data even if the XML schema is not defined in completeness.Furthermore, with Binary XML, you can store XML schemas in the same table and query across common elements.

There are numerous advantages to Binary XML storage. First is high through put including fast DOM loading.Though Binary XML storage takes up more space than object-relational storage,it does considerably better than CLOB storage. Binary XML storage provides the most schema flexibility of the three storage models.

You have the option to store data and metadata together or separately. You can even use multiple schemas for the same XMLType table.Binary XML supports SQL scalar datatypes and SQL constraints. Binary XML supports indexing with XMLIndex,function-based indexes,and Oracle Text indexes.

The performance of XPath-based queries produce good results.Streaming XPath evaluation circumvents DOM construction and evaluates multiple XPath expressions in a single pass.Using XMLIndex,you can improve the performance of XPath-based queries.XMLIndex will be discussed in detail in subsequent sections.Lastly, for XML schema–based data, inserted rows are fully validated during DML activity.

Let’s look into how to create and manage this new XML datatype.First, let’s create an XML table of Binary XML:

SQL> create table edba_xml
2 of sys.xmltype xmltype
3 store as securefile
4 binary xml ( tablespace users
5 retention auto keep_duplicates
6* compress high ) tablespace users
SQL> /
Table created.

You’ll notice in this example that the XMLTYPE STORE AS SECUREFILE BINARY XML syntax distinguishes this XMLType as Binary XML. You will also notice that the SECUREFILE with KEEP_DUPLICATES clause is utilized.

Oracle Enterprise Manager Database Console provides full support for XML including the new Binary XMLType table.By utilizing the EM Database Console, you can easily implement Binary XML. One thing to note with Oracle Database 11g Enterprise Manager Database Console is that the location of the XML database management menus are located somewhere else.The XML database management options are located on the Schema tab on the middle left of the screen.

Figure Enterprise Manager Database Console XML database management menu options

Enterprise Manager Database Console XML database management menu options

Now, let’s create your first Binary XMLType table using Enterprise Manager Database Console by clicking the XMLType Tables link; just select Schema -> XML Database --> XML Type Tables. The easiest way to create an XMLType table is to base it on another XMLType table, as depicted in Figure.

Figure . XMLType table

XMLType table

Please pay particular attention to the Go button in the middle of the screen next to the Create Like drop-down list.You can use this to create an XMLType table based on another predefined XML Type.

The Create Like option will create the table as a copy of the source.So if the source table name is an object-relational storage type,then the target table must also be an object-relational storage type.In this particular example,the EDBA_SECURITY Binary XMLType table will be created based on the X$SECURITYCLASS table,as shown on the Binary XML Create Like screen.

Figure Binary XML Create Like screen

Binary XML Create Like screen

After you click the Go button, you will be redirected to the Create Table screen.You can see that the binary storage is defined by the schema, as shown on the Binary XML Create Table screen.

Figure Binary XML Create Table screen

Binary XML Create Table screen


The following SQL statement is generated by Enterprise Manager Database Console:

CREATE TABLE "SH"."EDBA_SECURITY" OF SYS.XMLTYPE XMLTYPE
STORE AS BINARY XML ( TABLESPACE "TOOLS")
XMLSCHEMA "
xmlns.oracle.
ELEMENT "securityClass"
TABLESPACE "TOOLS" PCTFREE 10 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT)
LOGGING NOCOMPRESS

Once this table is created, you can describe the table and verify that an XMLType table has been created:

SQL> desc edba_security
Name Null? Type
----------------------------------------- -------- -----------------
TABLE of XMLTYPE(XMLSchema
"http://xmlns.oracle.com/xs/securityclass.xsd" Element"securityClass")
STORAGE BINARY

Alternatively,you can create the Binary XMLType table without a schema definition.To create the Binary XMLType table,you can click the Create button.This will take you to the Create Table screen for XMLType tables.Oracle, by default,will create a Binary XMLType table.

Figure Create Table screen for XMLType tables

Create Table screen for XMLType tables
From a command-line perspective, creating an XMLType table is as simple as this:

SQL> create table edba_po
of sys.xmltype xmltype
store as securefile
binary xml (tablespace tools)
SQL> /

Table created.

Binary XMLType can be implemented as XMLType tables and columns. Here’s another example of Binary XML on a column range-partitioned by the CREATION_DATE column:

create table edba_xml_tab
( edba_xml_tab_id number not null,
creation_date date not null,
xml_data sys.xmltype not null
)
tablespace xml_data
xmltype column xml_data store as binary xml
partition by range (creation_date)
(
partition year_2006
values less than (to_date('01-jan-2006','dd-mon-yyyy'))
tablespace xml_data_2006,
partition year_2007
values less than (to_date('01-jan-2007','dd-mon-yyyy'))
tablespace xml_data_2007,
partition year_xxxx
values less than (maxvalue)
tablespace xml_data
)
/

XMLIndex

Previously,you had the option of XPath function–based indexes (using extract or extractvalue) or Oracle Text.Both XPath and Oracle Text have limitations.Oracle Database 11g introduces the new XMLIndex geared to index the internal structure of the XML data.XMLIndex overcomes the indexing limitations of CLOB storage by indexing the XML tags of the document,and determines fragments based on the XPath expressions.XMLIndex also indexes scalar node values and tracks document hierarchy information for each node it indexes.It tracks the parent-child, ancestor-descendant,and sibling relation ships.

Why Use XMLIndex?

SQL functions such as XMLQuery, XMLTable, XMLExists, XMLCast, extract,extractValue,and exists Node can take advantage of XMLIndex to improve the performance of XML retrieval. There are many advantages to using XMLIndex. Let’s review the major advantages of XMLIndex.First,the XMLIndex can be used in any part of a query.

XMLIndex is not bound to be used as part of a WHERE clause.XMLIndex canparticipate in range queries,aggregates,and arithmetic functions.If all the columns are used,XMLIndex can even fulfill fast full-index scans.Also, XMLIndex can be used to extract document fragments since it can be used as part of the SELECT clause and the FROM clause.

Function-based and CTXXPath indexes are not capable of extracting frag ments from documents. Second,XMLIndex can be used for both schema-based or nonschema- based XML documents.It is not restricted on the storage options for the XML: Binary XML, hybrid,or unstructured storage.XMLIndex can handle your XML as relational tables or as CLOBS.

This can eliminate the need to shred your XML.(Of course,you may have specific reasons to continue XML extractions.)Third, XMLIndex can handle XPath expressions that target collections. Fourth,XMLIndex supports parallelism for index creation and maintenance. Updates to XMLIndex on Binary XML can be executed in a piecewise manner for enhanced performance.

Why Use XMLIndex?

XMLIndex Components

The XMLIndex is based on the XMLType datatype.The XMLIndex is a domain index relevant for the domain of the XML data.It is also a logical index with three components:

  • Path index
  • Order index
  • Value index

The path index indexes the XML tags of the document and determines the document fragments.The order index indexes the hierarchical position of the nodes in the XML document.The value index obviously indexes the values of the XML document.This value index is what provides access for the WHERE clause predicate.Values can be retrieved on exact values or by range scans.

XMLIndex utilizes a shadow table for each XMLIndex referred to as the path table.The path table stores the row ID,locator (document frag ment and datatype information),the order key (hierarchical position of the node represented in decimal key) for each indexed node,and the value (text of an attribute node).

XMLIndex Path Table

Let’s look at how the XMLIndex path table looks.The path table information can be retrieved from the DBA_/ALL_/USER_XML_INDEXES view.The Oracle XDB schema ships with an XMLIndex for the XDB$ACL table.You can query the DBA_XML_INDEXES view to retrieve the index name and the path table name, as shown here:

SQL> select table_owner||'.'||table_name table_name,
2 table_owner||'.'||path_table_name path_table_name,
3 index_name
4* from dba_xml_indexes
SQL> /
TABLE_NAME PATH_TABLE_NAME INDEX_NAME
------------ ----------------- --------------------
XDB.XDB$ACL XDB.XDBACL_PATH_TAB XDB$ACL_XIDX let’s look at the path table associated with this XMLType table:
SQL> desc XDB.XDBACL_PATH_TAB
Name Null? Type
--------------- -------- --------------
RID ROWID
PATHID RAW(8)
ORDER_KEY RAW(1000)
LOCATOR RAW(2000)
VALUE VARCHAR2(4000)

The path index is represented by the PATHID and the RID; likewise, the order index is represented by the ORDER_KEY and the RID.The PATHID and ORDER_KEY columns are automatically indexed by Oracle.

The data in the ORDER_KEY column is in a decimal format.This is similar to the decimal format that you see in the Dewey Decimal system used in libraries. If the decimal number for the ORDER_KEY column resembles something like 1.11.12, this number would represent the node position of the 1st child of the 11th child of the 12th child of the document root node.

The LOCATOR column is used for fragment extraction.If the locator is for Binary XML of schema-based data, the datatype information would also be stored in this column.The VALUE column stores the text value of the simple element node without children or attribute nodes.All comments are ignored during indexing.

The VALUE column truncates any value larger than 4,000 bytes since it is a VARCHAR2(4000) datatype.For incidents where the VALUE column exceeds 4,000 bytes,the LOCATOR value is updated so that the complete value can be retrieved from the base table.In addition to the 4,000-byte limitation of the VALUE column,Oracle imposes another limit for the size of the key for the secondary index created on this column.

This limit is determined by the DB_BLOCK_SIZE parameter of the database. This is not just for XMLIndex but also applies for B-tree and function-based indexes.It is important to note that neither the VALUE column limitation of 4,000 bytes nor the index key size affects the results of the query.The only perceivable impact is on performance.

Detailed conceptual information is provided for the path table. Unfortu nately,you cannot access the path table.If you try to access the path table, you will get an error resembling this:

SQL> select order_key from XDB.XDBACL_PATH_TAB;
select order_key from XDB.XDBACL_PATH_TAB
*
ERROR at line 1:
ORA-30967: operation directly on the Path
Table is disallowed

The only thing you are allowed to do with the path table is create secon dary indexes on the path table columns. Besides this, you really cannot do anything else.You do not even gather statistic on the path table.The only object(s) you would want to gather statistics is on the XMLIndex or the base table for the XMLIndex.

At index creation time,you have the option of specifying the path table name using the PATH TABLE option in the PARAMETERS clause.If you do not specify a path name,a system-generated path name will be created.Also,you can specify the tablespace location to the path table.By default, the storage properties for the XMLIndex path table and the secondary indexes automatically inherit the storage properties of the base table of the XMLIndex.You can customize the storage parameters in the PARAMETERS clause.Here’s an example of the storage options:

('path table edba_po_path_table
(pctfree 5 pctused 90 initrans 5
storage (initial 1k next 2k minextents 3 buffer_pool keep)
nologging enable row movement parallel 3)
path id index edba_po_path_id_idx (logging pctfree 1 initrans 3)
order key index edba_po_order_key_idx (logging pctfree 1 initrans 3)
value index edba_po_value_idx (logging pctfree 1 initrans 3)');

The following CREATE INDEX syntax illustrates all the XMLIndex options that have been discussed so far:

SQL> create index edba_security_xmlindex
2 on edba_security v (value(v))
3 indextype is xdb.xmlindex
4 parameters('path table edba_security_path_table
5* (tablespace tools) async (sync manual)')
SQL> /
Index created.

Another big feature of Oracle’s XMLIndex indexes is that they can be created in parallel mode and can fully leverage Oracle’s parallel query server processes.In this example,you take the same query and add the parallel option at the end of the CREATE INDEX syntax:

SQL> create index edba_security_xmlindex
2 on edba_security v (value(v))
3 indextype is xdb.xmlindex
4 parameters('path table edba_security_path_table
5 (tablespace tools) async (sync manual)')
6* parallel 8
SQL> /
Index created.

Please note that indexes created with the parallel degree option do incur storage for each of the parallel processes as defined by the initial extent clause.If the initial extent of the index is 100MB and you specify a parallel degree of 8,Oracle will create 8×100MB extents during the index build.

EM Database Console to Create XMLIndex

Let’s see how XMLIndexes are created using the Enterprise Manager Database Console.Oracle Database 11g Enterprise Manager Database Console produces another menu option for the XMLIndex.From the home screen,navigate to the XMLIndex screen by selecting Schema -> XML Database -> XMLIndex.On the XMLIndex screen, click the Create button.The General XMLIndex screen will look like Figure.

Figure General XMLIndex screen

General XMLIndex screen

Once you enter the table name and the index name for the schema, click the Options tab to provide the path table name and tablespace information.

Figure XMLIndex options

Figure XMLIndex options

There are additional options on this screen; the XML Subsetting and Asynchronous options will be explained in the upcoming sections.

Now,the XMLIndex is created, so let’s see how you can retrieve this information from the data dictionary.You can query DBA_/ALL_/USER_INDEXES to retrieve high-level information about what table has XMLIndex indexes:

SQL> select table_name, index_name , index_type
2 from dba_indexes
3 where owner='SH'
4* and ityp_name= 'XMLINDEX'
SQL> /
TABLE_NAME INDEX_NAME INDEX_TYPE
------------- ------------------ --------------------
EDBA_SECURITY EDBA_SECURITY FUNCTION-BASED DOMAIN
XMLINDEX
EDBA_PO EDBA_PO_XMLINDEX FUNCTION-BASED DOMAIN

Earlier,the XMLIndex was referred to as a domain-level index,and querying the DBA_ INDEXES view indicates that the XMLIndex is a function-based domain index type.Also, note the ITYP_NAME column has the new value for XMLIndex.

You can also query the DBA_/ALL_/USER_XML_INDEXES view to get detailed information pertaining to an XMLIndex:

SQL> select table_name table_name,
2 path_table_name path_table_name,
3 index_name, async
4* from dba_xml_indexes
SQL> /
TABLE_NAME PATH_ INDEX_ SYNC
TABLE_NAME NAME
------------- -------------- ---------------- -----------
EDBA_SECURITY EDBA_SECURITY_ EDBA_SECURITY_ MANUAL
PATH_TABLE XMLINDEX
EDBA_PO SYS71294_EDBA PO EDBA_PO_XMLINDEX
XM_PATH_TABLE
XDB.XDB$ACL XDBACL_PATH_TAB XDB$ACL_XIDX

you can see that SYS71294_EDBA_PO_XM_PATH_TABLE is the system-generated name for the path table for EDBA_PO_XMLINDEX.

Index Maintenance

You can perform index maintenance on XML index.You can rename an XMLIndex using the ALTER INDEX command.For example:

SQL> alter index EDBA_PO_XMLINDEX rename to EDBA_PO_XMLINDEX_idx;

Index altered.

Changing an XMLIndex index name does not change the name of the path table.The path table will need to be renamed independently.Just like regular indexes, you can perform index rebuild functions.You can rebuild an index but cannot change tablespaces during this process.If you try tochange the tablespace for the XMLIndex during a rebuild operation, you will receive an error that looks like this:

SQL> alter index EDBA_PO_XMLINDEX_idx rebuild tablespace docs_d;
alter index EDBA_PO_XMLINDEX_idx rebuild tablespace docs_d
*
ERROR at line 1:
ORA-29871: invalid alter option for a domain index XMLIndex supports parallelism for index rebuilds:
SQL> alter index EDBA_PO_XMLINDEX_idx rebuild parallel 8;
Index altered.Index Maintenance

XMLIndex Secondary Indexes

You can create one or more secondary indexes on the VALUE column of the path table. If you do not explicitly specify a secondary index on the VALUE column, a default secondary is created for you.

The default secondary index is created based on the text data only.Optionally, you can index the VALUE column to be of another datatype.If you create a secondary index of NUMBER type,this index is used only when it is necessary.In addition to different datatype indexes, you can also create secondary indexes to be function-based or Oracle Text indexes.Let’s look at examples of creating secondary indexes using function-based indexes on the path table:

SQL> create index edba_po_xmlindex_upper_idx
on sys71294_edba_po_xm_path_table
(upper(substr(value, 1,120)));
Index created.

If the values are compared for uppercase values for the first 120 bytes,the UPPER(SUBSTR()) function can become useful.

You can also create Oracle Text context-based indexes on the VALUE column for full-text queries that utilize ora:contains XPath functions:

SQL> create index edba_secxmlindex_ctx
on edba_security_path_table(value)
indextype is ctxsys.context
parameters('TRANSACTIONAL');
Index created.

In the previous example,Oracle will utilize the VALUE column during predicate evaluation.The secondary index is by default a text-based index on the VALUE column of the path table.If all the data in the VALUE column happens to be numeric, you will want to change the datatype of the secondary index to NUMBER using the function-based index with the to_number (VALUE) clause.If there are any text values, you will receive an ORA-01722,“invalid number,” error during index creation. If you get this error, you can still create an index but must use the DBMS_XMLINDEX stored package. Oracle provides a Create Number Index procedure to allow the creation of a numeric index.

As you see here, the CreateNumberIndex procedure takes three parameters.The third parameter is the name of the numeric datatype index you want to name.

procedure createnumberindex
argument name type in/out default?
----------------------- ----------- ---------------
xml_index_schema varchar2 in
xml_index_name varchar2 in
num_index_name varchar2 in

Let’s see this in action:

SQL> begin
2 dbms_xmlindex.createnumberindex
('SH', 'EDBA_PO_XMLINDEX_IDX',
'EDBA_PO_XMLINDEX_IDX_N');
3* end;
SQL> /
PL/SQL procedure successfully completed.

Likewise, you can use CreateDateIndex to create a DATE datatype secondary index on the VALUE column of the path table.The CreateDateIndex procedure accepts one additional parameter, XMLTYPENAME.This parameter can be DATE or DATETIME, as shown here:

SQL> begin
2 dbms_xmlindex.createdateindex
('SH', 'EDBA_PO_XMLINDEX_IDX',
'EDBA_PO_XMLINDEX_IDX_D', 'DATETIME');
3* end;
SQL> /
PL/SQL procedure successfully completed.

Data Dictionary Views for XMLIndex Secondary Indexes

You created indexes on the VALUE column of the path table to improve the performance of your queries.Let’s see how you can determine from the data dictionary what is indexed.The following example queries the data dictionary and identifies what the path table is, what the secondary index name is, and what columns of the path table are indexed:

SQL> select table_name, index_name, column_name, column_position
2 from dba_ind_columns
3 where table_name in (select path_table_name from dba_xml_indexes
4 where index_name like '%DBA%')
5* order by index_name, column_name
SQL> / TABLE_NAME INDEX_NAME COLUMN_ COL_
NAME POS
----------------- ---------------------- ----------------- ---
SYS71294_EDBA_PO_ EDBA_PO_ SYS_NC00006$ 1
XM_PATH_TABLE XMLINDEX_FIDX
SYS71294_EDBA_PO_ EDBA_PO_ SYS_NC00009$ 1
XM_PATH_TABLE XMLINDEX_IDX_D
SYS71294_EDBA_PO_ EDBA_PO_ SYS_NC00010$ 1
XM_PATH_TABLE XMLINDEX_IDX_D2
SYS71294_EDBA_PO_ EDBA_PO_ SYS_NC00008$ 1
XM_PATH_TABLE XMLINDEX_IDX_N
SYS71294_EDBA_PO_ EDBA_PO_ SYS_NC00007$ 1
XM_PATH_TABLE XMLINDEX_UPPER_IDX
EDBA_SECURITY_ EDBA_ VALUE 1
PATH_TABLE SECXMLINDEX_CTX
SYS71294_EDBA_PO_ SYS71294_EDBA ORDER_KEY 2
XM_PATH_TABLE PO_XM_ORDKEY_IX
SYS71294_EDBA_PO_ SYS71294_EDBA_ RID 1
XM_PATH_TABLE PO_XM_ORDKEY_IX
SYS71294_EDBA_PO_ SYS71294_EDBA PATHID 1
XM_PATH_TABLE PO_XM_PATHID_IX
SYS71294_EDBA_PO_ SYS71294_EDBA_ RID 2
XM_PATH_TABLE PO_XM_PATHID_IX
SYS71294_EDBA_PO_ SYS71294_EDBA VALUE 1
XM_PATH_TABLE PO_XM_VALUE_IX
EDBA_SECURITY SYS71312_EDBA ORDER_KEY 2
PATH_TABLE SECUR_ORDKEY_IX
EDBA_SECURITY SYS71312_EDBA RID 1
PATH_TABLE SECUR_ORDKEY_IX
EDBA_SECURITY SYS71312_EDBA PATHID 1
PATH_TABLE SECUR_PATHID_IX
EDBA_SECURITY SYS71312_EDBA RID 2
PATH_TABLE SECUR_PATHID_IX
EDBA_SECURITY SYS71312_EDBA VALUE 1
PATH_TABLE SECUR_VALUE_IX
16 rows selected.

You can clearly see where the table name and/or index names are system generated.

Asynchronous Maintenance of XMLIndexes

Just like any other index,the XMLIndex index is updated with each insert,update,and delete of each row on the table.By default,the underlying base table and the XMLIndex are always synchronized.With XMLIndex,you can change this characteristic and defer theindex updates as applicable to the business.In situations where you have a lot of data processing, you may want to defer the index maintenance until the job is complete.Similar to the way that simple materialized views are refreshed,XMLIndex can be synchronized on an interval basis or at a specified time. An XMLIndex index that is synchronized on an interval basis can be referred to as a stale index.

A stale index should be considered only if the business can tolerate data latency.An important fact to note is that a stale index can have an effect on query results.The query result is only as good as the last XMLIndex synchroni zation.You compromise data latency for the performance of database load. Basically, if the XMLIndex is not up-to-date, queries that hit the underlying base table will also reflect the data as of the last synchronization time.

If you are planning on using deferred XMLIndex synchronization, you need to be aware of operations that automatically update the XMLIndex.Any DDL commands against the XMLIndex will implicitly force the XMLIndex resynchronization.This includes the creation of secondary indexes against the path table.Also,any DDL commands issued on the base table will cause the XML Index to resync.This includes using ALTER TABLE commands,creating indexes, or dropping indexes on the base table.

Let’s examine how asynchronous (ASYNC) maintenance can be implemented.The ASYNC option is part of the PARAMETERS clause of the CREATE INDEX or ALTER INDEX syntax.The ASYNC option has several possible values:

  • (ALWAYS)
  • (ON COMMIT)
  • (EVERY "repeat_interval")
  • (MANUAL)
  • (STALE)

The default value for the ASYNC option is ALWAYS.The ON COMMIT option can be compared to the two-phased commit. The commit command will not return the “Commit Complete” status message until the synchronization is complete.You may encounter a small delay in response since the synchronization is performed as a separate transaction.

The EVERY option with repeat_interval is the same as the repeat_interval specification for DBMS_SCHEDULER.You can have complete control over when to refresh the XMLIndex. One thing to note is that to use the EVERY option, you must have the CREATE JOB system-level privilege.MANUAL implies that the resynchronization will never occur automatically.A manual resync of the index will happen only with the DBMS_XMLINDEX.SYNCINDEX command.

The STALE value should not be changed.It is provided only for future use. The only thing you will have to know is that the staleness is set to TRUE when synchronization is set to ALWAYS; otherwise,this value is set to FALSE.Setting this value incorrectly will cause an error message to be generated.

Earlier, the XMLIndex was created with the ASYNC(SYNC MANUAL) clause.This implies that you have to manually synchronize the index with the DBMS_XMLINDEX package like you see here:

SQL> begin
2 dbms_xmlindex.syncindex('JJONES', 'EDBA_SECURITY_XMLINDEX');
3 end;
4 /
PL/SQL procedure successfully completed.

You can re-create this index and have it refresh when commits occur with the ON COMMIT value:

SQL> create index edba_security_xmlindex
2 on edba_security v (value(v))
3 indextype is xdb.xmlindex
4 parameters('path table edba_security_path_table
( tablespace tools) async (sync on commit)')
5* parallel 8
SQL> /
Index created.

You can synchronize the XMLIndex using the repeat interval attribute.To create a refresh schedule of every 30 minutes,you can set the repeat interval with the FREQ=MINUTELY and INTERVAL=30 parameters:

SQL> create index edba_security_xmlindex
2 on edba_security v (value(v))
3 indextype is xdb.xmlindex
4 parameters('path table edba_security_path_table
( tablespace tools)
async (sync every "freq=minutely; interval=30")')
5* parallel 8
6 /
Index created.

When XMLIndex synchronization is set to DEFERRED, changes made to the underlying base table are recorded in a pending table. Oracle records the ROWID and the operation of the DML.This is similar to the way the snapshot logs work. A row is inserted into the pending table for every row that is inserted, up dated, or deleted. The pending table is system generated.You have to queryDBA_/ALL_/ USER_XML_INDEXES view and the PEND_TABLE_NAME column to determine the pending table name:

SQL> select index_name, stale, async, pend_table_name
2 from dba_xml_indexes
3* where index_name='EDBA_SECURITY_XMLINDEX'
SQL> /
INDEX_NAME STALE ASYNC PEND_TABLE_NAME
-------------- ----- ----- ---------------
EDBA_ SECURITY-EVERY SYS71484_EDBA_
XMLINDEX SECUR_PEND _TABLE

Once you determine the pending table, you can describe it to see the table attributes:

SQL> desc SYS71484_EDBA_SECUR_PEND_TABLE
Name Null? Type
---------------------------- -------- ----------------------------
RID ROWID
OPERATION VARCHAR2(2)
ERRNUM NUMBER
ERRMSG VARCHAR2(2000)

Similarly to the path table,you cannot perform any DML against the pending table:

SQL> select count(*) from sys71484_edba_secur_pend_table; ERROR at line 1: ORA-30958: operation directly on the Pending Table is disallowed

XMLIndex Path Subsetting

A great benefit of XMLIndex is that you do not have to know XPath expressions.By default, XMLIndex indexes all XPath locations of the XML data. Just like any other index,the smaller index size will yield the faster retrieval of data.With XPath expressions, you can narrow your search criteria and,in effect,reduce the data of the XMLIndex index.This ultimately will lead to highly tuned XML applications by limiting the nodes of the XML document to be indexed.The XMLIndex XPath subsetting is achieved in two possible methods:

  • Exclusion
  • Inclusion

Exclusion implies that you want to exclude fragments from the complete XPath expression.On the flip side,inclusion implies that you want to start from an empty set and add paths as you go.XML subsetting can be accomplished by including the PATHS option to the CREATE INDEX or ALTER INDEX command.

Let’s review how this great feature works. First, let’s create a new XMLIndex with certain nodes included:

SQL> create index edba_po_idx on edba_po
(object_value) indextype is xdb.xmlindex
2 parameters ('paths
3 (include
4 (
5 /PurchaseOrder/LineItems//*
6 /PurchaseOrder/Reference
7 /PurchaseOrder/Actions
8 )
9 )'
10* )
SQL> /
Index created.

In this particular example,the elements for LineItems, Reference, and Actions are included in the initial index build process.The LineItems elements and their descendants are included in this index.Let’s assume that a new requirement came about to include the Requestor elements to the index.To add the Requested nodes, you can use the INCLUDE ADD parameters:

SQL> alter index edba_po_idx rebuild
2 parameters ('paths (include add (/PurchaseOrder/Requestor
3* ))')
SQL> /
Index altered.

If for some reason you need to add all the possible paths, you can use the ALL parameter. You can issue the options INCLUDE ADD (ALL)or EXCLUDE REMOVE (ALL).Both of these have equivalent results. Even though you can include all paths,you cannot exclude all paths.

SQL> alter index edba_po_idx rebuild
2* parameters ('PATHS (include add (ALL))')
SQL> /
Index altered.

This would be equivalent to issuing the index REBUILD command that you are accustomed to using:

SQL> alter index edba_po_idx rebuild;
Index altered.

Just like you can add nodes to an existing index,you can remove node entries from an XMLIndex using the EXCLUDE ADD option:

SQL> alter index edba_po_idx rebuild
2* parameters ('PATHS (EXCLUDE ADD (/PurchaseOrder/Reference))')
SQL> /
Index altered.

Native Web Services for Oracle XML DB

For DBAs and developers who spend considerable time with web development,the new native web services for Oracle XML DB provide an easy technology stack to implement XML applications on the Web.You no longer need to have Oracle’s Internet application server to serve XML services over the Web.This integration solution in the database provides another toolkit for the DBAs and developers.

Two services come natively with Oracle XML DB web services: the SQL and XQuery web service and the PL/SQL web service. The SQL and XQuery web service allows you to query and return data as XML data. The PL/SQL web service allows you to access stored procedures and functions over the Web. The native web services for Oracle XML DB rely on the following technology stack components:

  • Simple Object Access Protocol (SOAP) V1.1
  • HTTP post method
  • The Accept-Charsets field of the HTTP input header
  • Web Services Description Language (WSDL)

SOAP is a critical component for the XML DB web services.SOAP 1.1 is used by the HTTP POST method and for error handling.

All XML DB configuration files reside in the xdbconfig.xml file located in the root of the XML DB repository.

HTTP and FTP for XML DB

Oracle XML DB is part of the standard database creation process.By default,the XDB schema is locked out of the database.You must enable the XDB schema and also set up the appropriate HTTP and/or FTP ports.The FTP port is not a requirement for native web services for XML DB.

The ability to access the XML DB using HTTP or FTP is not a new Oracle Database 11g feature,but it is crucial to explain how to configure HTTP since it is the essence of enabling web services for XML DB.

The good news is that you can set up HTTP and FTP access to XML DB using Enterprise Manager Database Console.Using Enterprise Manager Database Console,the setup process is as simple as clearing out the zeroed-out port numbers and resetting them to the desired port designations.The XML DB configuration URL is located in the XML Database section of the Schema tab.

The XML DB Configuration screen even allows secure access to the XML DB repository using HTTPS.To confirm you have everything set up prior to HTTP or FTP access, you can use the lsnrctl status command.If everything is set up correctly, you should see line entries for HTTP and FTP similar to what is shown here as part of the lsnrctl status output:

HTTP and FTP for XML DB

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.dbaexpert.com)(PORT=8080))
(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.dbaexpert.com)
(PORT=2100))
(Presentation=FTP)(Session=RAW))

Please remember that if FTP is enabled, files that are being transferred with FTP will be uploaded to the XML DB repository.You can control security for HTTP and FTP using access control lists (ACLs).Once you have successfully set up the HTTP port for XML DB, you can verify connectivity by pointing your URL toYou should at this point be prompted for the XDB user ID and password.

Setup XML DB Servlet

For security reasons,native web services for Oracle XML DB are not set up in the database.You must manually enable XML DB web services.There are a couple of steps needed to set up the XML DB servlet.First,as the sys user,you need to grant the XDB WEBSERVICES role to the user:

SQL> grant XDB_WEBSERVICES to xdb;

Grant succeeded.

Alternatively,you can also grant the XDB_WEBSERVICES_OVER_HTTP role to the user.The XDB_WEBSERVICES role allows only for HTTPS communication with the database.This role is a requirement for XML DB web services.The XDB_WEBSERVICES_OVER_HTTP role allows for HTTP traffic. SQL> grant XDB_WEBSERVICES_OVER_HTTP to xdb;

Grant succeeded.

You may choose to grant the XDB_WEBSERVICES_WITH_PUBLIC role to the user.This role enables access to all the objects that are granted to PUBLIC over the web services.With this role enabled for the user,the user would have access to the PUBLIC objects as if they were logged in the database; otherwise,the security permissions would be given access to database objects less the PUBLIC objects.

Second, you must add the web services configuration servlet using the DBMS_XDB package:

DECLARE
SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
DBMS_XDB.deleteServletMapping(SERVLET_NAME);
DBMS_XDB.deleteServlet(SERVLET_NAME);
DBMS_XDB.addServlet(NAME => SERVLET_NAME,
LANGUAGE => 'C',
DISPNAME => 'Oracle Query Web Service',
DESCRIPT => 'Servlet for SCHEMA => 'XDB');
DBMS_XDB.addServletSecRole(SERVNAME => SERVLET_NAME,
ROLENAME => 'XDB_WEBSERVICES',
ROLELINK => 'XDB_WEBSERVICES');
DBMS_XDB.addServletMapping(PATTERN => '/orawsv/*',
NAME => SERVLET_NAME);
END;
/

Once the orawsv servlet is added, you can review the xdbconfig.xml file to verify the setup. Another way to verify correct setup is to use XQuery to query the orawsv keywords from the xdbconfig.xml file.

Validate the XML DB Web Service

Now you can access the XML DB servlet using your web browser of choice.The web service has a WSDL associated with it.The WSDL document specifies the location of the service and the operations (or methods) the service exposes.The access path of the WSDL document is as follows:

In this particular example, for the host rac11, you can access the WSDL document using the following URL:

This web page shows the following output:

<definitions name="orawsv" targetNamespace="http://xmlns.oracle.com/orawsv">
-
<types>
-
<xsd:schema targetNamespace=http://xmlns.oracle.com/orawsv
xsi:schemaLocation="ht
-
<xsd:element name="query">
-
<xsd:complexType>
-
<xsd:sequence>
-
<xsd:element name="query_text">
-
<xsd:complexType>
-
<xsd:simpleContent>
-
<xsd:extension base="xsd:string">
-
<xsd:attribute name="type">
-
<xsd:simpleType>
-
<xsd:restriction base="xsd:NMTOKEN">
<xsd:enumeration value="SQL"/>
<xsd:enumeration value="XQUERY"/>
<xsd:enumeration value="XQUERYX"/>
<xsd:enumeration value="PLSQL"/>
<xsd:enumeration value="TABLE"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:extension>
</xsd:simpleContent>
</xsd:complexType>
</xsd:element>
..
..
..
<service name="ORAWSVService">
<documentation>Oracle Web Service</documentation>
-
<port name="ORAWSVPort" binding="tns:ORAWSVBinding">
<soap:address location="http://rac11:8080/orawsv"/>
</port>
</service>
</definitions>

Validate the PL/SQL Web Service

Once the servlet is defined and verified for successful access, you can proceed to access the stored packages or procedures inside the database for web service access.The PL/SQL web services are located here:

■ Note The schema owner, package name, and procedure name have to be accessed in uppercase. If the uppercase schema owner or package name is not specified, you will receive an “Incorrect Input Doc/URL” page, as shown here:

<soap:faultcode>
<soap:Value>soap:Sender</soap:Value>
</soap:faultcode>
<soap:faultstring>Error processing input</soap:faultstring>
<soap:detail>
<OracleErrors>
Incorrect Input Doc/URL
</OracleErrors>
</soap:detail>
</soap:Fault>

Each stored function or procedure is associated with a dynamic web service that generates its own WSDL document.The WSDL for the stored functions can be accessed using the URL path specified earlier except you would simply replace the procedure with the function name. Let’s create a simple package called DBA_SALARIES with a function called MAXSALARY:

CREATE OR REPLACE PACKAGE dba_salaries AUTHID CURRENT_USER AS
FUNCTION maxsalary (p_org_name IN OUT VARCHAR2,
p_high_salary OUT NUMBER)
RETURN NUMBER;
END dba_salaries;
/
CREATE OR REPLACE PACKAGE BODY dba_salaries
AS
FUNCTION maxsalary
(p_org_name IN OUT VARCHAR2,
p_high_salary OUT NUMBER) return number
IS
CURSOR c1 IS
SELECT MAX(salary)
FROM edba_master
WHERE org_name=p_org_name;
BEGIN
OPEN c1; FETCH c1 INTO p_high_salary; CLOSE c1;
RETURN p_high_salary;
END;
END;
/

Once the package is created,you can validate the PL/SQL web service for the stored package.The DBA_SALARIES package that was created in the earlier example can be accessed using the following URL:

Or you can ask for the WSDL document from the fully qualified PACKAGE. FUNCTION nameinstead. Notice that the package name is a separate virtual directory from the stored procedure or function. Continuing from the DBA_SALARIES package, we will access the MAXSALARY function:

http://rac11:8080/orawsv/XDB/DBA_SALARIES/MAXSALARY?wsdl

Here’s the output:

- <definitions name="MAXSALARY" targetNamespace=
"http://xmlns.oracle.com/orawsv/XDB/- <types>
- <xsd:schema targetNamespace=
"http://xmlns.oracle.com/orawsv/XDB/DBA_SALARIES/MAXSA
- <xsd:element name="SNUMBER-MAXSALARYInput">
- <xsd:complexType>
- <xsd:sequence>
<xsd:element name="P_ORG_NAME-VARCHAR2-INOUT" type="xsd:string" />
- <xsd:element name="P_HIGH_SALARY-NUMBER-OUT">
<xsd:complexType />
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
- <xsd:element name="MAXSALARYOutput">
- <xsd:complexType>
- <xsd:sequence>
<xsd:element name="RETURN" type="xsd:double" />
<xsd:element name="P_ORG_NAME" type="xsd:string" />
<xsd:element name="P_HIGH_SALARY" type="xsd:double" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
</types>
- <message name="MAXSALARYInputMessage">
<part name="parameters" element="tns:SNUMBER-MAXSALARYInput" />
</message>
- <message name="MAXSALARYOutputMessage">
<part name="parameters" element="tns:MAXSALARYOutput" />
</message>
- <portType name="MAXSALARYPortType">
- <operation name="MAXSALARY">
<input message="tns:MAXSALARYInputMessage" />
<output message="tns:MAXSALARYOutputMessage" />
</operation>
</portType>
- <binding name="MAXSALARYBinding" type="tns:MAXSALARYPortType">
<soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http" />
- <operation name="MAXSALARY">
<soap:operation soapAction="MAXSALARY" />
- <input>
<soap:body parts="parameters" use="literal" />
</input>
- <output>
<soap:body parts="parameters" use="literal" />
</output>
</operation>
</binding>
- <service name="MAXSALARYService">
<documentation>Oracle Web Service</documentation>
- <port name="MAXSALARYPort" binding="tns:MAXSALARYBinding">
<soap:address location="http://rac1:8080/orawsv/XDB/DBA_SALARIES/MAXSALARY" />
</port>
</service>
</definitions>

In-Place XML Schema Evolution

Oracle Database 11g introduces new,in-place XML schema evolution capability.This particular feature enables certain kinds of changes to XML schemas with zero downtime.In-place schema evolution makes alterations to an existing XML schema without copying,deleting,or reinserting elements of the data.Schema evolution capability is a much longed for feature for a lot of companies that are reluctant to use schema-based XMLTypes.Here are the general restrictions for in-place schema evolution:

  • You cannot change the storage model.
  • You cannot make transformations that invalidate existing documents.

To implement in-place schema evolution,you use the DBMS_XMLSCHEMA. INPLACEEVOLVE procedure.This procedure accepts three parameters:

procedure inPlaceEvolve(schemaURL IN VARCHAR2,
diffXML IN XMLType,
flags IN NUMBER := EVOLVE_PRESERVE_VALIDITY | EVOLVE_TRACE_ONLY);

The first parameter identifies the URL of the XML schema to be evolved.The diffXML parameter specifies the XML schema differences document that specifies the changes to apply.The FLAG parameter is an optional bit mask parameter that controls the behavior of the procedure.There are two possible flags for this parameter: EVOLVE_SCAN_DATA and EVOLVE_PRESERVE_VALIDITY. By default, EVOLVE_PRESERVE_VALIDITY is set, and EVOLVE_SCAN_DATA is not set.

This implies that the validity of the document is ensured by inspecting the XML schema changes.You should accept the default parameter for the FLAG option only if the validity of the document can be guaranteed without scanning the instance documents.If the EVOLVE_PRESERVE_VALIDITY bit is not set, then the procedure will try to make changes to the XML schema even if it may introduce invalidity.

One of the options to the inPlaceEvolve procedure is diffXML.The diffXML document needs to be created before you can apply the in-place evolution of the schema.This document is the blueprint to delta changes that need to be applied to the XML schema.Changes are executed in order of operation as specified in the diffXML file.There are several ways to create the diffXML document: the XMLDiff JavaBean,the xmldiff command-line interface, or the XMLDIFF SQL function.

XQuery Enhancements

Oracle introduced XQuery to the database in Oracle Database 10g Release 2. Oracle continues to make improvements to enhance this feature set.Oracle Database 11g introduces two new functions: XMLExists and XMLCast.

XMLExists

The XMLExists function checks to see whether a specified XQuery returns a nonempty sequence. XMLExists is similar to the existsNode operator except it returns a TRUE/FALSE Boolean value and accepts an arbitrary XQuery expression.

■Note Oracle recommends you utilize XMLExists rather than use existsNode.

Let’s see how the XMLExists function works:

SQL> select object_value
2 from edba_po
3 where XMLExists('/PurchaseOrder[Requestor="John Z. Chen"]'
4* PASSING OBJECT_VALUE)
SQL> / OBJECT_VALUE
--------------------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
9 rows selected.

You can use the XMLExists function in SQL queries and in function-based indexes.To use XMLExists in a SQL statement,you must wrap it inside the CASE statement:

CASE WHEN XMLExists(...) THEN 'TRUE' ELSE 'FALSE' END.

XMLCast

Also with Oracle Database 11g XQuery,Oracle adds the XMLCast function.The XMLCast function takes the first argument and casts it to the second argument.The second argument must be of


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics