slow xml

[mbames]

I am sure that anyone who has tried to use XMLType in 9i will know what I am about to say. I have a test table with 1/2 million records (which the live system is likely to have many more). However when using the .extract() function to find a given xml tage for a value of my choosing the query took a 58 minutes to run. ie.

select count(id) from mytable e where e.extract('/number/one/text()').getStringVal()='one';

So nothing too complicated, when it had finished running it told me there were 11,111 matching records (which was correct). However the performance is well off the mark (even if in this case Oracle is running on a p2-400 with 896MB with W2k as its host OS). Should I always expect the .extract() function to be slow? Or is there some clever way that I can get Oracle in index the xml?

However I suspect that indexing the xmltype may not work as their will always be a gently trickle of data into the table.

Cheers,
Matt