CODEWITHSUNDEEP
sqlxmlperformanceoracle-databaseplsql
vanilla161
vanilla161

Reputation: 375

Improving performance of EXTRACTVALUE in WHERE clause

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

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

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

When EXTRACTVALUE is used in select statement like this

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

it takes only 0.5 second.

Column body is CLOB type.

Upvotes: 4

Views: 6567

Answers (2)

Jon Heller
Jon Heller

Reputation: 36862

Functions aren't slower in the WHERE clause. But it may appear that way if your IDE only returns the top N rows.

You can probably improve performance with a function based index.

Here's the sample table and data. Only one out of 1000 rows contains the status "Cancelled", making it a good candidate for an 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;
/

The query takes 3 seconds to perform a full table scan.

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

Creating an index changes changes the plan to an 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';

Upvotes: 3

Beryllium
Beryllium

Reputation: 12998

You can try to build 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 could put an index on the status column.

Upvotes: 1

Related Questions