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

Question

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 ('<name>Alice</name>');
INSERT INTO t_xml VALUES ('<name>Bob</name>');
INSERT INTO t_xml VALUES ('<name>Carol</name>');
INSERT INTO t_xml VALUES ('<name>Chuck</name>');

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().

Answer 1

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.