提高WHERE子句中EXTRACTVALUE的性能

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

是否有可能在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.

当EXTRACTVALUE在WHERE子句中时,整个查询执行15秒,肯定太长。

When EXTRACTVALUE is used in select statement like this

在这样的select语句中使用EXTRACTVALUE时

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

it takes only 0.5 second.

它只需0.5秒。

Column body is CLOB type.

列体是CLOB类型。

2 个解决方案

#1

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

WHERE子句中的函数速度不慢。但是,如果您的IDE仅返回前N行,则可能会出现这种情况。

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.

这是示例表和数据。 1000行中只有一行包含状态“已取消”,这使其成为索引的良好候选者。

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.

查询需要3秒钟才能执行全表扫描。

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.

创建索引更改会将计划更改为INDEX RANGE SCAN,并将时间减少到0.03秒。

create index events_fbi on events
    (extractValue(xmltype(body), '/Event/Description/Status'));

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

#2

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.

然后从物化视图中进行选择。为了加快速度,您可以在状态列上添加索引。

#1