超级慢，只要加上这个条件查询时。为什么，该如何解决？

Question

SELECT ID,CODE FROM V_PORT_CS WHERE CODE = '//1'    -- 40.11 SECONDS
SELECT ID,NAME FROM V_PORT_CS WHERE NAME = '1'     --0.078 SECONDS

如上：只要加上CODE 这个字段，去查V_PORT_CS这个视图就会超级慢（40.11秒），用其他字段都不会有问题，比如用name等去查。

以下是其他相关信息：

1.   SELECT COUNT(*) FROM V_PORT_CS;    -- 322212

该视图共32万条数据

2.视图定义

create or replace view v_port_cs as
(select
   ID,
   MBID,
   MRID,
   MSID,
   coderule(CODE,ID,'E_NPORT') AS CODE,
   NAME,

   RESOURCEID
from e_nport
where kindcode in (-99,1,2,3,6,200,101,9,108,4,8,7));

 

 3.  函数定义

create or replace function coderule(object_code in varchar2,tableid in number,tablename in varchar2) return varchar2 is
  rvalue varchar2(1000);
  eqcode varchar2(1000);
  efcode varchar2(1000);
  efidx   number;
  modulecode varchar2(1000);
  begin
       if tablename='E_NPORT' then 
       begin
       select efid into efidx from E_NPORT where id=tableid;
       select (select name from e_equipment where id=e.object_id) into eqcode from e_eqframe e where e.id=efidx;
       EXCEPTION
              when others then
              dbms_output.put_line(sqlerrm);
       end;
       begin
       select (select code from e_eqframe where id=e.efid) into efcode from E_NPORT e where e.id=tableid;
       EXCEPTION
              when others then
              dbms_output.put_line(sqlerrm);
       end;
       begin
       select (select code from e_module where id=e.moduleid) into modulecode from E_NPORT e where e.id=tableid;
       EXCEPTION
              when others then
              dbms_output.put_line(sqlerrm);
       end;
       if modulecode IS NULL then
          rvalue:=eqcode||'/'||efcode||'/'||object_code;
       end if;
       if modulecode IS NOT NULL then
          rvalue:=eqcode||'/'||efcode||'/'||modulecode||'/'||object_code;
       end if; 
    end if;
    return rvalue;
end coderule;

5

对于 视图中的name,code 等均做了索引

以上。

求解！

 

 

Answer 1

因为coderule里是一个完整的逻辑，如果其中的任意一个逻辑都是需要一个固定的时间n的话，那么23W条数据就至少是23W*n秒的时间，当然会很慢。

在正常的逻辑中，只进行一次23W行的扫描就会花去相当的时间了，所以通常我们会用上索引，那么对当前列的数据可以进行非常快速的查询了。
而coderule函数由于系统在进行语法分析时无法对其进行优化，那么会进行一次全表扫描，如果32万行数据都需要进行一次coderule（）操作的话，后果可想而知。
所以给你的建议是将逻辑提到外面来，那么分成多个SQL语句，但是如果每个SQL的操作都很简单，并且能被数据库识别，那么每个操作都会很快。

在你的上述逻辑上，只要写成一个存储过程，那么只需要查询一次原来的表e_nport，然后根据游标操作，就会大大加块查询速度了