1

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

0

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.