Oracle | DBMS_XMLINDEX

Oracle DBMS_XMLINDEX Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

Documented No

Exceptions

Error Code	Reason
ORA-44737	Parameter <String> did not exist.

First Available 12.1

Security Model Owned by XDB: Execute is granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsxidx.sql

Subprograms

CREATEDATEINDEX	GETPARAMETER	REGISTERPARAMETER
CREATENUMBERINDEX	MODIFYPARAMETER	SYNCINDEX
DROPPARAMETER	PROCESS_PENDING	Demo

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);

Also see demo at page bottom

exec 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;

SELECT xdb.dbms_xmlindex.getParameter('Test')

      FROM dual;

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);

DECLARE

       num_errored NUMBER := 0;

       num_pending NUMBER := 0;

      BEGIN

        dbms_xmlindex.process_pending('oe', 'po_struct', num_pending, num_errored);

        dbms_output.put_line('Number of rows still pending = ' || num_pending);

        dbms_output.put_line('Number of rows with errors = ' || num_errored);

      END;

      /

      Number of rows still pending = 0

      Number of rows with errors = 0

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="https://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);

Related Demo

This demo shows usage of several pieces of related functionality conn sys@pdbdev as sysdba SELECT owner, index_name, index_type FROM dba_indexes WHERE ityp_name = 'XMLINDEX'; OWNER INDEX_NAME INDEX_TYPE ------ --------------- --------------------------- XDB XDB$ACL_XIDX FUNCTION-BASED DOMAIN 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;