Newbie question on indexing XML type
2823224Nov 3 2009 — edited Nov 4 2009I cannot get a query to use an index I created for an XML type. Here is my setup:
create table XmlTest( id number,
data_xml XMLType)
XmlType data_xml STORE AS CLOB;
I inserted 10,000 records like this ( each record has a unique id):
insert into XmlTest(id, data_xml) values
( 1
, XMLType('<company>
<department>
<id>10</id>
<name>Accounting</name>
</department>
</company>'));
I then created this index:
CREATE INDEX xmltest1
ON xmltest (extractValue(data_xml, '/company/department/id'));
When I run this query, it takes a long time - 12 seconds.
SELECT id from xmltest
WHERE EXISTSNODE( data_xml, '/company/department[id="37500"= 1;
it is doing a full table scan. What is wrong with my index?
Thanks!