As said in the former post, one of the disadvantages of creating a full blown XMLIndex, indexing on all possible values, is that the size of the XMLIndex is most of the time larger in size then the table itself.
This is one of the reasons that it is possible to make use of what is called “XMLIndex Path Subsetting“. In short, one creates only indexes on XPath locations which are needed. By default, XMLIndex indexes all possible XPath locations in your XML data, this is easy if you have no knowledge of what data will be selected. As said, the disadvantage is that this will use a lot of space.
Path Subsetting
With XMLIndex Path Subsetting one can remove all the index values that one doesn’t need after indexing all Xpath locations OR you create a skeleton structure and add all the index values on the XPath locations you actually need.
As in the relational database world, one should not build an index on every possible table column and concatenated column combinations. So, IMHO, I think it is easier to start from scratch with no index available and build only those indexes on index paths you need.
In the following example, I will demonstrate how you can do this. Given the examples from the “Oracle 11g – XMLIndex (part 1)” post, you can use XMLIndex path subsetting the following way…