228 DBMS_XMLINDEX
The DBMS_XMLINDEX
package provides an interface to implement asynchronous indexing.
This chapter contains the following topics:
See Also:
Oracle XML DB Developer's Guide for more information about "XMLIndex"
228.1 DBMS_XMLINDEX Overview
DBMS_XMLINDEX
provides a mechanism for asynchronous index maintenance.
Asynchronous Index Maintenance
The basic XMLIndex is maintained on every DML operation. However, given the computing costs, in many cases the availability of stale result is adequate. In such situations, it is desirable to defer index updates to a more convenient time, for example when the load on the database is low. DBMS_XMLINDEX
provides this mechanism.
228.2 DBMS_XMLINDEX Security Model
Owned by XDB
, the DBMS_XMLINDEX
package must be created by SYS
or XDB
. The EXECUTE
privilege is granted to PUBLIC
.
Subprograms in this package are executed using the privileges of the current user.
228.3 Summary of DBMS_XMLINDEX Subprograms
This table lists the DBMS_XMLINDEX
subprograms and briefly describes them.
Table 228-1 DBMS_XMLINDEX Package Subprograms
Subprogram | Description |
---|---|
Creates a secondary index for date values in the |
|
Creates a secondary index for number values in the |
|
Drops the |
|
Modifies the |
|
Processes pending rows for a |
|
Registers a parameter string and |
|
Synchronizes the index manually |
228.3.1 CREATEDATEINDEX Procedure
This procedure creates a secondary index for date values in the VALUE
column of a PATH
TABLE
which is the storage table of an XMLIndex
. The second form of the procedure allows for the date_index_clause
to be set to an empty string.
Syntax
DBMS_XMLINDEX.CREATEDATEINDEX ( xml_index_schema IN VARCHAR2, xml_index_name IN VARCHAR2, date_index_name IN VARCHAR2, xmltypename IN VARCHAR2, date_index_clause IN VARCHAR2); DBMS_XMLINDEX.CREATEDATEINDEX ( xml_index_schema IN VARCHAR2 xml_index_name IN VARCHAR2, date_index_name IN VARCHAR2, xmltypename IN VARCHAR2);
Parameters
Table 228-2 CREATEDATEINDEX Procedure Parameters
Parameter | Description |
---|---|
|
Name of the owner of the |
|
Name of the |
|
Name of the secondary index to be created for date values in the |
|
The type to which values in the |
|
Storage clause to be applied to the date index during its creation. This is a string argument appended to the |
228.3.2 CREATENUMBERINDEX Procedure
This procedure creates a secondary index for number values in the VALUE
column of a PATH
TABLE
which is the storage table of an XMLIndex
.
Syntax
DBMS_XMLINDEX.CREATENUMBERINDEX ( xml_index_schema IN VARCHAR2, xml_index_name IN VARCHAR2, num_index_name IN VARCHAR2, num_index_clause IN VARCHAR2, xmltypename IN VARCHAR2);
Parameters
Table 228-3 CREATENUMBERINDEX Procedure Parameters
Parameter | Description |
---|---|
|
Name of the owner of the |
|
Name of the |
|
Name of the secondary index to be created for number values in the |
|
Storage clause to be applied to the number index during its creation. This is a string argument appended to the |
|
The type to which values in the |
228.3.3 DROPPARAMETER Procedure
This procedure drops the XMLIndex
parameter string that is associated with a given parameter identifier.
Syntax
DBMS_XMLINDEX.DROPPARAMETER ( name IN VARCHAR2);
Parameters
Table 228-4 DROPPARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
Identifier for parameter string |
Examples
DBMS_XMLINDEX.DROPPARAMETER ( 'myIndexParam');
228.3.4 MODIFYPARAMETER Procedure
This procedure modifies the XMLIndex
parameter string that is associated with a given parameter identifier.
Syntax
DBMS_XMLINDEX.MODIFYPARAMETER ( name IN VARCHAR2, parameter IN CLOB);
Parameters
Table 228-5 MODIFYPARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
Identifier for parameter string |
|
|
Examples
DBMS_XMLINDEX.MODIFYPARAMETER ( 'myIndexParam', 'PATH TABLE po_ptab PATH ID INDEX po_pidx ORDER KEY INDEX po_oidx VALUE INDEX po_vidx');
228.3.5 PROCESS_PENDING Procedure
This procedure processes executes DMLs required to complete a NONBLOCKING ALTER INDEX ADD_GROUP/ADD_COLUMN
operation on an XMLIndex
.
Syntax
DBMS_XMLINDEX.PROCESS_PENDING ( xml_index_schema IN VARCHAR2, xml_index_name IN VARCHAR2, pending_row_count OUT BINARY_INTEGER, error_row_count OUT BINARY_INTEGER);
Parameters
Table 228-6 PROCESS_PENDING Procedure Parameters
Parameter | Description |
---|---|
|
Name of the owner of the |
|
Name of the |
|
Number of pending rows to be processed |
|
Number of rows for which indexing may have failed because of an error |
Usage Notes
-
This procedure will iteratively attempt to index all necessary rows in small batches while skipping rows that are locked and rows for which index maintenance fails with an error. Therefore, it may have to be executed multiple times for an XMLIndex until all pending rows are processed. Once all pending rows are processed, user can complete the
NONBLOCKING
ALTER
INDEX
OPERATION
. -
If it is not possible process all the pending rows after multiple trials, the user will have to manually triage the locking or error issues by examining unprocessed rows in
SYS_AIXSXI_######_PENDINGTAB
and errors inSYS_AIXSXI_#####_ERRORTAB
.. Keeping track of rows and the errors is useful in triaging issues.
Examples
EXEC DBMS_XMLINDEX.PROCESS_PENDING( 'SCOTT', 'PO_XMLINDEX_IX', out_param1, out_param2);
228.3.6 REGISTERPARAMETER Procedure
This procedure registers a parameter identifier and XMLIndex
parameter string pair in XDB.
Syntax
DBMS_XMLINDEX.REGISTERPARAMETER ( name IN VARCHAR2, parameter IN CLOB);
Parameters
Table 228-7 REGISTERPARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
Identifier for parameter string |
|
|
Examples
DBMS_XMLINDEX.REGISTERPARAMETER ( 'myIndexParam', 'PATH TABLE po_ptab PATH ID INDEX po_pidx ORDER KEY INDEX po_oidx VALUE INDEX po_vidx PATHS(NAMESPACE MAPPING(xmlns:p="http://www.example.com/IPO")) GROUP MASTERGROUP XMLTABLE PO_TAB (''/p:PurchaseOrder'' COLUMNS REFERENCE VARCHAR2(30) PATH ''p:Reference'', REQUESTOR VARCHAR2(30) PATH ''p:Requestor'' ) GROUP ITEMGROUP XMLTABLE ITEMGROUP_TAB (''/p:PurchaseOrder/p:LineItems/p:LineItem'' COLUMNS LINENUMBER NUMBER(38) PATH ''@p:ItemNumber'', QUANTITY NUMBER(38) PATH ''@p:Quantity'', DESCRIPTION VARCHAR2(256) PATH ''p:Description'''));
228.3.7 SYNCINDEX Procedure
This function synchronizes an asynchronously maintained XMLIndex.
It applies to the XMLIndex changes that are logged in the pending table, and brings the path table up-to-date with the base XMLTYPE
column.
Syntax
DBMS_XMLINDEX.SYNCINDEX ( xml_index_schema IN VARCHAR2, xml_index_name IN VARCHAR2, partition_name IN VARCHAR2 DEFAULT NULL, reindex IN BOOLEAN DEFAULT FALSE);
Parameters
Table 228-8 SYNCINDEX Procedure Parameters
Parameter | Description |
---|---|
|
Name of the owner of the XMLIndex |
|
Name of the XMLIndex |
|
[Currently not supported] |
|
Default is |
Examples
EXEC DBMS_XMLINDEX.SYNCINDEX('USER1', 'SS_TAB_XMLI', REINDEX=>TRUE);