Improving EXTRACTVALUE performance in WHERE clause

Question

Is there any way to improve the performance of this query in PL / SQL?

SELECT * FROM events WHERE EXTRACTVALUE(xmltype(body),'/Event/Description/Status') = 'Cancelled' 

When EXTRACTVALUE is in WHERE , the whole query EXTRACTVALUE 15 seconds, definitely too long.

When EXTRACTVALUE used in a select statement like this

 SELECT EXTRACTVALUE(xmltype(body),'/Event/Description/Status') FROM events 

it takes only 0.5 seconds.

The body column is a CLOB type.

Answer 1

Functions are not slower in the WHERE clause. But it might look like this if your IDE returns only the top N rows.

You can probably improve performance with a feature-based index.

Here is an example of a table and data. Only one of the 1000 rows contains the status "Canceled", which makes it a good candidate for the index.

 create table events(id number primary key, body clob); insert into events select level, '<Event> <Description> <Status>'|| case when mod(level, 1000) = 0 then 'Cancelled' else 'Active' end|| '</Status> </Description> </Event>' from dual connect by level <= 10000; commit; begin dbms_stats.gather_table_stats(user, 'EVENTS'); end; / 

It takes 3 seconds to complete a full table scan.

 SELECT * FROM events WHERE EXTRACTVALUE(xmltype(body),'/Event/Description/Status') = 'Cancelled'; 

Creating index changes changes the plan to INDEX RANGE SCAN and reduces the time to 0.03 seconds.

 create index events_fbi on events (extractValue(xmltype(body), '/Event/Description/Status')); SELECT * FROM events WHERE EXTRACTVALUE(xmltype(body),'/Event/Description/Status') = 'Cancelled'; 

Answer 2

You can try to create a materialized view:

 create view x as select e.*, EXTRACTVALUE(xmltype(body),'/Event/Description/Status') status FROM events e; create materialized view x2 as select * from x; 

Then select from the materialized view. To speed things up, you can put the index in the status column.