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:
/*+ 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 'http://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.

No comments:

Post a Comment