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
XML Indexes are used to index data stored in XML documents. The following examples demonstrate a number of variants found in the on-line Oracle docs.
Dependencies
ALL_XML_INDEXES
DBA_XML_INDEXES
XMLTYPE
CDB_XML_INDEXES
USER_XML_INDEXES
Indexing XML without XML Indexes
Using XMLCAST and XMLQUERY
CREATE INDEX po_reference_ix
ON purchaseorder(
XMLCast(
XMLQuery('$p/PurchaseOrder/Reference' PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(128)));
Using EXTRACTVALUE
CREATE INDEX po_reference_ix
ON purchaseorder(
extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference'));
Using CAST
CREATE INDEX requestor_index
ON purchaseorder(
cast("XMLDATA"."REQUESTOR" AS VARCHAR2(128)));
Using the sys_orderkey_depth function
CREATE INDEX depth_ix
ON my_path_table (rid, sys_orderkey_depth(ORDER_KEY), ORDER_KEY);
Creating a Function-Based Index on Path-Table Column VALUE
CREATE INDEX fn_based_ix
ON po_path_table(SUBSTR(VALUE, 1, 100));
Indexing XML with XML Indexes
XML Index
CREATE INDEX <index_name>
ON [<schema_name>.]<table_name>(<column_name_or_expression>)
INDEXTYPE IS XDB.XMLINDEX
CREATE TABLE po_xmlindex OF XMLTYPE
XMLTYPE STORE AS BINARY XML
VIRTUAL COLUMNS
(date_col AS (XMLCast(XMLQuery('/PurchaseOrder/@orderDate' PASSING OBJECT_VALUE RETURNING CONTENT) AS DATE)));
CREATE INDEX po_xmlindex_ix
ON po_xmlindex(OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex;
XML Index with the Parameters Path Table Clause
CREATE INDEX <index_name>
ON [<schema_name>.]<table_name>(<column_name_or_expression>)
INDEXTYPE IS XDB.XMLINDEX
PARAMETERS ('<XML Index Parameters | PARAM <identifier>')
CREATE INDEX po_xmlindex_ix
ON po_xmlindex(OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PATH TABLE my_path_table');
XML Index with the Parameters Clause with Tablespace Specification
CREATE INDEX <index_name>
ON [<schema_name>.]<table_name>(<column_name_or_expression>)
INDEXTYPE IS XDB.XMLINDEX
PARAMETERS ('<XML Index Parameters | PARAM <identifier> [(TABLESPACE <tablespace_name>]')
CREATE INDEX po_xmlindex_ix
ON po_xmlindex(OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PATH TABLE my_path_table (TABLESPACE uwdata)');
XML Index with the Parameters Columns Clause
CREATE INDEX child_ex_xidx
ON child_ex p (xcol) INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('XMLTable po_index_tab_ex ''purchaseorder''
COLUMNS pid NUMBER(4) PATH ''@id''');
Specifying Storage Options When Creating an XMLIndex Index
CREATE INDEX po_xmlindex_ix
ON po_xmlindex(OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PATH TABLE 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)
PIKEY INDEX po_pikey_ix (LOGGING PCTFREE 1 INITRANS 3)
VALUE INDEX po_value_ix (LOGGING PCTFREE 1 INITRANS 3)');
XMLIndex Path Subsetting with CREATE INDEX
CREATE INDEX po_xmlindex_ix
ON po_binxml (OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PATHS (INCLUDE (/PurchaseOrder/LineItems//* /PurchaseOrder/Reference))');
XMLIndex Path Subsetting With Namespace Mapping Clause
CREATE INDEX po_xmlindex_ix
ON po_xmlindex(OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS('PATHS (INCLUDE (/PurchaseOrder/LineItems//* /PurchaseOrder/ipo:Reference)
NAMESPACE MAPPING(xmlns="http://xmlns.oracle.com" xmlns:ipo="http://xmlns.oracle.com/ipo"))');
Creating an XMLIndex Index in Parallel
CREATE INDEX sales_info_ix
ON sale_info (sale_po_clob)
INDEXTYPE IS XDB.XMLIndex
LOCAL PARALLEL 10;
Using Different PARALLEL Degrees for XMLIndex Internal Objects
CREATE INDEX po_xmlindex_ix
ON sale_info (sale_po_clob)
INDEXTYPE IS XDB.XMLIndex
LOCAL NOPARALLEL
PARAMETERS('PATH TABLE po_path_table(PARALLEL 10)
PIKEY INDEX po_pikey_ix
VALUE INDEX po_value_ix (PARALLEL 5)');
Other Examples
Create a Numeric Index on Column VALUE with createNumberIndex
ALTER INDEX po_struct
PARAMETERS('NONBLOCKING ADD_GROUP GROUP po_action_group
XMLTABLE po_idx_tab
''/PurchaseOrder''
COLUMNS actions XMLType PATH ''Actions/Action''
VIRTUAL
XMLTABLE po_idx_action
''/Action'' PASSING actions
COLUMNS actioned_by VARCHAR2(10) PATH ''User'',
date_actioned TIMESTAMP PATH ''Date''');
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
PL/SQL procedure successfully completed.
ALTER INDEX po_struct PARAMETERS('NONBLOCKING COMPLETE');
Related Queries
Return a secondary indexes on an XMLIndex Path Table
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;
