Oracle XML Indexes
Version 18c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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 exec dbms_xmlindex.createNumberIndex('OE', 'PO_XMLINDEX_IX', 'API_NUM_IX');
Creating a Date Index on Column VALUE with createDateIndex exec dbms_xmlindex.createDateIndex('OE', 'PO_XMLINDEX_IX', 'API_DATE_IX', 'dateTime');
XMLIndex with a Structured Component, Using Namespaces and Storage Options CREATE INDEX po_struct
ON po_binxml (OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('XMLTable po_ptab (TABLESPACE "USERTBS1" COMPRESS FOR OLTP)
XMLNAMESPACES (DEFAULT ''http://www.example.com/po''),
''/purchaseOrder''
COLUMNS orderdate DATE PATH ''@orderDate'',
id BINARY_DOUBLE PATH ''@id'',
items XMLType PATH ''items/item'' VIRTUAL XMLTable li_tab
(TABLESPACE "USERTBS1" COMPRESS FOR OLTP)
XMLNAMESPACES (DEFAULT ''http://www.example.com/po''),
''/item'' PASSING items
COLUMNS partnum VARCHAR2(15) PATH ''@partNum'',
description CLOB PATH ''productName'',
usprice BINARY_DOUBLE PATH ''USPrice'',
shipdat DATE PATH ''shipDate''');
XMLIndex with a Structured Component, Specifying TABLESPACE at the Index Level CREATE INDEX po_struct
ON po_binxml (OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('XMLTable po_ptab,
XMLNAMESPACES (DEFAULT ''http://www.example.com/po''),
''/purchaseOrder''
COLUMNS orderdate DATE PATH ''@orderDate'',
id BINARY_DOUBLE PATH ''@id'',
items XMLType PATH ''items/item'' VIRTUAL
XMLTable li_tab,
XMLNAMESPACES (DEFAULT ''http://www.example.com/po''),
''/item'' PASSING items
COLUMNS partnum VARCHAR2(15) PATH ''@partNum'',
description CLOB PATH ''productName'',
usprice BINARY_DOUBLE PATH ''USPrice'',
shipdat DATE PATH ''shipDate''
TABLESPACE "USERTBS1" COMPRESS FOR OLTP)');
XMLIndex Index: Adding a Structured Component CREATE INDEX po_xmlindex_ix
ON po_binxml (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;
/

ALTER INDEX po_xmlindex_ix PARAMETERS('PARAM myparam');
Using DBMS_XMLINDEX.PROCESS_PENDING To Index XML Data CREATE INDEX po_struct
ON po_binxml (OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('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''');

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;

Related Topics
Built-in Functions
Built-in Packages
DBMS_XMLINDEX
XML Functions
XMLExists
XMLQuery
XMLTable
What's New In 19c
What's New In 20c-21c

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-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx