How to create XMLIndex? When its worth to use it?

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:

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.


test_table t,
xmlnamespaces(default ‘//’),
‘/element’ passing t.xml_content
id path ‘/activity/@id’,
name path ‘/activity/name’
) xt
where t.xml_content is not null
) where =’BUBU’ and’ID’

Here we have “” from inside XML. If would use only this filter criteria with “” 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 “” in filter predicate is present, “test_table” is first filtered on “”, 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.