General discussion
January 21, 2003 at 10:16 PM
lalala

Function-based index on xmltype columns

by lalala . Updated 23 years, 8 months ago

Hey,

I created an index on a xmltype column on an extract function, but the index is not being used on the queries.

Here’s the code used when creating the index:
create index name_index on xml_table t
(substr(t.xml_type_field.extract(‘/XML/NAME/text()’).getStringVal(),1,255));

The query:
select t.xml_type_field.extract(‘/XML/NAME/text()’).getStringVal()
from xml_table t
where t.xml_type_field.extract(‘/XML/NAME/text()’).getStringVal() = ‘value’

Here’s what I get when I examinethe query
SELECT STATEMENT, GOAL = CHOOSE 100 113 23052
TABLE ACCESS FULL ADMIN XML_TABLE 100 113 23052

Both the table and index have been analized recently.
I use Oracle 9ias & pl/sql developer.

If anyone can help me, I thank you in advance

This discussion is locked

All Comments