Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Newbie question on indexing XML type

2823224Nov 3 2009 — edited Nov 4 2009
I 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!

Comments

Jason_(A_Non)
I've yet to have the opportunity to work with indexes on XML data but I suspect it is because your index alone cannot satisfy the results of the query and the data you want is from every row in the table so a FTS is the best approach.

To restate, you are asking for the id value from the table where the XML contains a certain node. This node exists in every entry in the index, so why use the index if you have to go get data from the table.

Or it is because EXISTSNODE is checking for a different condition then your index was defined on. I suspect if you do something like /company/department/id[. = '10'] in your EXISTSNODE, it should use the index.

All this is a guess and the best place for you to look is the {forum:id=34} forum and also [Marco&apos;s Blog|http://www.liberidu.com/blog/]
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 2 2009
Added on Nov 3 2009
#general-xml, #index, #xml
1 comment
2,659 views