Oracle DBMS_XMLINDEX
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose 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 convenient time, for example to a time when the load on the database is low. Thus a mechanism for asynchronous index maintenance is provided.
AUTHID CURRENT_USER
Dependencies
DBA_XML_INDEXES DBMS_XMLINDEX0 XMLINDEX_LIB
DBMS_STATS    
Documented No
First Available 12.1.0
Security Model Owned by XDB: Execute is granted to PUBLIC compiled with CURRENT_USER privs
Source {ORACLE_HOME}/rdbms/admin/dbmsxidx.sql
Subprograms
 
CREATEDATEINDEX
Creates a secondary index for date values in the VALUE column of a PATH TABLE which is the storage table of an XMLIndex dbms_xmlindex.createDateIndex(
xml_index_schema  IN VARCHAR2 := USER,
xml_index_name    IN VARCHAR2,
date_index_name   IN VARCHAR2 := NULL,
xmltypename       IN VARCHAR2 := NULL,
date_index_clause IN VARCHAR2 := NULL,
column_name       IN VARCHAR2 := NULL);
PRAGMA SUPPLEMENTAL_LOG_DATA(CreateNumberIndex, AUTO_WITH_COMMIT);
call dbms_xmlindex.createDateIndex('OE', 'PO_XMLINDEX_IX', 'API_DATE_IX', 'dateTime');
 
CREATENUMBERINDEX
Creates a secondary index for number values in the VALUE column of a PATH TABLE which is the storage table of an XMLIndex dbms_xmlindex.createNumberIndex(
xml_index_schema IN VARCHAR2 := USER,
xml_index_name   IN VARCHAR2,
num_index_name   IN VARCHAR2 := NULL,
num_index_clause IN VARCHAR2 := NULL,
xmltypename      IN VARCHAR2 := NULL,
column_name      IN VARCHAR2 := NULL);
See Demo at page bottom

call dbms_xmlindex.createNumberIndex('OE', 'PO_XMLINDEX_IX', 'API_NUM_IX');
 
DROPPARAMETER
Drops the XMLIndex parameter string that is associated with a given parameter identifier dbms_xmlindex.dropParameter(paramname IN VARCHAR2);
exec dbms_xmlindex.dropParameter('myIndexParam');
 
GETPARAMETER
Undocumented utility function dbms_xmlindex.getParameter(paramname IN VARCHAR2) RETURN VARCHAR2;
TBD
 
MODIFYPARAMETER
Modifies the XMLIndex parameter string that is associated with a given parameter name dbms_xmlindex.modifyParameter(paramname IN VARCHAR2, paramstr IN CLOB);
exec dbms_xmlindex.modifyparameter ('myIndexParam', 'PATH TABLE po_ptab PATH ID INDEX po_pidx ORDER KEY INDEX po_oidx VALUE INDEX po_vidx');
 
PROCESS_PENDING
Executes DMLs required to complete a NONBLOCKING alter index add_group/add_column operation 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);
PRAGMA SUPPLEMENTAL_LOG_DATA(process_pending, AUTO_WITH_COMMIT);
TBD
 
REGISTERPARAMETER
Registers a parameter string and XMLIndex parameter string pair in XDB dbms_xmlindex.registerParameter(paramname IN VARCHAR2, paramstr IN CLOB);
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'' ));


CREATE INDEX po_xmlindex_ix ON po_clob (OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PATH TABLE path_tab');

BEGIN
DBMS_XMLINDEX.registerParameter(
'myparam',
'ADD_GROUP GROUP po_item
XMLTable po_idx_tab ''/PurchaseOrder''
COLUMNS reference VARCHAR2(30) PATH ''Reference'',
requestor VARCHAR2(30) PATH ''Requestor'',
username VARCHAR2(30) PATH ''User'',
lineitem XMLType PATH ''LineItems/LineItem'' VIRTUAL
XMLTable po_index_lineitem ''/LineItem'' PASSING lineitem
COLUMNS itemno BINARY_DOUBLE PATH ''@ItemNumber'',
description VARCHAR2(256) PATH ''Description'',
partno VARCHAR2(14) PATH ''Part/@Id'',
quantity BINARY_DOUBLE PATH ''Part/@Quantity'',
unitprice BINARY_DOUBLE PATH ''Part/@UnitPrice''');
END;
/
 
SYNCINDEX
Manually synchronizes an XML index dbms_xmlindex.syncIndex(
xml_index_schema IN VARCHAR2 DEFAULT USER,
xml_index_name   IN VARCHAR2,
partition_name   IN VARCHAR2 DEFAULT NULL,
reindex          IN BOOLEAN  DEFAULT FALSE);
exec dbms_xmlindex.syncIndex('USER1', 'SS_TAB_XMLI', REINDEX=>TRUE);
 
Package Demo
This demo shows usage of multiple pieces of DBMS_XMLINDEX functionality conn / as sysdba

SQL> SELECT owner, index_name, index_type
2 FROM dba_indexes
3 WHERE ityp_name = 'XMLINDEX';

OWNER  INDEX_NAME      INDEX_TYPE
------ --------------- ---------------------------
XDB    XDB$ACL_XIDX    FUNCTION-BASED DOMAIN


SQL> SELECT dbms_metadata.get_ddl('INDEX','XDB$ACL_XIDX','XDB') FROM dual;

DBMS_METADATA.GET_DDL('INDEX','XDB$ACL_XIDX','XDB')
--------------------------------------------------------------------------------
CREATE INDEX "XDB"."XDB$ACL_XIDX" ON "XDB"."XDB$ACL" (OBJECT_VALUE)
INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS ('PATH TABLE XDBACL_PATH_TAB VALUE
INDEX XDBACL_PATH_TAB_VALUE_IDX')

SELECT c.INDEX_NAME, c.COLUMN_NAME, c.COLUMN_POSITION, e.COLUMN_EXPRESSION
FROM USER_IND_COLUMNS c LEFT OUTER JOIN USER_IND_EXPRESSIONS e
ON (c.INDEX_NAME = e.INDEX_NAME)
WHERE c.TABLE_NAME IN (SELECT PATH_TABLE_NAME FROM USER_XML_INDEXES
WHERE INDEX_NAME = 'PO_XMLINDEX_IX')
ORDER BY c.INDEX_NAME, c.COLUMN_NAME;

Related Topics
DBMS_XMLGEN
DBMS_XMLINDEX0
DBMS_XMLPARSER
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved