How to create XML index?
Very general way with all default applied as follows:
create index index_name on table_name (xmltype_column) indextype is xdb.xmlindex;
Check the name of PATH table
select path_table_name from user_xml_indexes where table_name = ‘table_name’ and index_name = ‘index_name’;
Check what secondary indexes are used
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 =’index_name’)
order by c.index_name, c.column_name;
How to turn off the use of XMLIndex?
Use one of optimizer hints:
/*+ NO_XMLINDEX_REWRITE */
/*+ NO_XMLINDEX_REWRITE_IN_SELECT */
/*+ NO_XML_QUERY_REWRITE */
When to use XML index ?
We can benefit from XMLIndex when queries with filter predicates from inside XML document exist. And there is no outside key, which could return particular XML doc for further processing.
Example:
select t.id, xt.id, xt.name
from
test_table t,
xmltable(
xmlnamespaces(default ‘//my.namespace.com’),
‘/element’ passing t.xml_content
columns
id path ‘/activity/@id’,
name path ‘/activity/name’
) xt
where t.xml_content is not null
) where xt.name =’BUBU’ and t.id=’ID’
Here we have “xt.name” from inside XML. If would use only this filter criteria with “t.id” absent, XMLIndex would be very beneficial to find particular row with “/activity/name/BUBU”. For DB engine it should be quite easy to do that, because “/activity/name” will be available in index path table (if created by default). If “t.id” in filter predicate is present, “test_table” is first filtered on “t.id”, then particular XML doc is passed for xpath processing. Therefore we not experience any XMLIdex advantages.
Thus – use XMLIndex when can’t uniquely identify row with XML content using only columns of outside table (i.e. column “id” of table “test_table”).
Learn more about XMLIndex from Oracle XML DB Developer’s guide.