How to create an index used by a WHERE extractValue(…) LIKE '…'? Setup

Let's say you have a table with some XML data. For the sake of argument, say:

CREATE TABLE t_xml (xml XMLTYPE NOT NULL);
INSERT INTO t_xml VALUES ('&lt;name&gt;Alice&lt;/name&gt;');
INSERT INTO t_xml VALUES ('&lt;name&gt;Bob&lt;/name&gt;');
INSERT INTO t_xml VALUES ('&lt;name&gt;Carol&lt;/name&gt;');
INSERT INTO t_xml VALUES ('&lt;name&gt;Chuck&lt;/name&gt;');


A function index works with =

Consider the following query:

SELECT * FROM t_xml WHERE lower(extractValue(xml, '/name')) = 'alice'


If I want this query to use an index, I can create a function index:

CREATE INDEX t_xml_f ON t_xml(lower(extractValue(xml, '/name')));


With this an explain plan tells me:

SELECT STATEMENT  
 TABLE ACCESS BY INDEX ROWID T_XML"
  INDEX RANGE SCAN T_XML_F


So far so good.

What index to use with LIKE?

But instead of =, what I really want is a LIKE. What index can I create so that the following query doesn't do a full table scan?

SELECT * FROM t_xml WHERE lower(extractValue(xml, '/name')) LIKE '%a%'


Now, Oracle won't use the above index with a LIKE, and this for a reason. If I wasn't using an extractValue(), I could use a text index. But I am using an extractValue().
A generic XMLIndex should do the trick:

CREATE INDEX t_xml_x ON t_xml(xml) INDEXTYPE IS XDB.XMLIndex;


Your explain plan will be:

SELECT STATEMENT  
 FILTER  
  TABLE ACCESS FULL T_XML
  TABLE ACCESS BY INDEX ROWID SYS15471_T_XML_X_PATH_TABLE
   INDEX RANGE SCAN SYS15471_T_XML_X_PIKEY_IX


You should be good with this, as long as your XPath expression doesn't use any of the constructs not indexed by XMLIndex.