oracle 游标 cursor 效率 慢

大神帮忙看下，这边有两段代码都是用游标取数据，第二个代码里面游标的sql里面带有参数。
a表两千万数据 b表两百万数据
游标sql取出来的数据只有两条，用sqlplus直接执行只需要10s。

现在遇到个问题，第一个代码10s可以执行完，但是第二个sql要半小时...
有没有大神可以帮忙解答下为什么？？？？？？？？？？
因为这个sql必须要带参数，急急急

DECLARE 
  CURSOR cur_query is
    select a.name from t_name a, t_dep b
    where a.id = b.id
    and a.name like '%hj%';
  rec_query cur_query%ROWTYPE;
BEGIN
  open cur_query;
  loop
    FETCH cur_query INTO rec_query.name;
    exit when cur_query%NOTFOUND;
  END LOOP; 
  close cur_query;
END;
/

---------------------------------------------------------------------------------

DECLARE
  v_name varchar2(10) := 'hj';
  CURSOR cur_query is
    select a.name from t_name a, t_dep b
    where a.id = b.id
    and a.name like '%' || hj || '%';
  rec_query cur_query%ROWTYPE;

BEGIN
  open cur_query;
  loop
    FETCH cur_query INTO rec_query.name;
    exit when cur_query%NOTFOUND;
  END LOOP; 
  close cur_query;
END;
/

12 个解决方案

#1

 v_name varchar2(10) := 'hj';
   CURSOR cur_query is
     select a.name from t_name a, t_dep b
     where a.id = b.id
     AND regexp_like(a.name,'*hj*')
   rec_query cur_query%ROWTYPE;
 用正则试试

#2

   CURSOR cur_query is
     select a.name from t_name a, t_dep b
     where a.id = b.id
     AND regexp_like(a.name,'*'||v_name||'*')
   rec_query cur_query%ROWTYPE;
 这样写

#3

用绑定变量试试

#4

在pl/sql代码中，sql语句里涉及的变量会自动绑定，如果说，第二个语句和第一个的区别只是将hj换成变量来，这里上下来看执行计划应该不会有区别。
问题是第二个语句用的变量是hj，上面并没有定义这个变量，应该会报错的。是手误呢，还是说执行的语句不是这样的？

#5

楼上说的不错，这两个语句其实执行计划不应该有啥区别，当然在变量一样的时候。
唯一值得确认的是楼主变量绑定的时候和第一条数据量不会不一样吧、

#6

引用 4 楼 wildwave 的回复:

在pl/sql代码中，sql语句里涉及的变量会自动绑定，如果说，第二个语句和第一个的区别只是将hj换成变量来，这里上下来看执行计划应该不会有区别。
问题是第二个语句用的变量是hj，上面并没有定义这个变量，应该会报错的。是手误呢，还是说执行的语句不是这样的？

不好意思是手误，应该是下面这个变量
v_name 

#7

引用 5 楼 z_shousi 的回复:

楼上说的不错，这两个语句其实执行计划不应该有啥区别，当然在变量一样的时候。
唯一值得确认的是楼主变量绑定的时候和第一条数据量不会不一样吧、

这个可以确认是一样的，因为第二个代码半小时左右后会输出结果，数据量也是两条。
第二条语句卡住的时候到数据库查了下正在执行的sql，貌似也没有问题，把like换成=号，然后去掉前后两个%号也可以很快查出数据...
感觉是like 和 两个%的问题，晕死

#8

刚试了下用引用游标也是可以很快查出数据，不过悲剧的是楼主用的客户端貌似不支持这个，编译不过....

v_sql varchar2(4000) := 'select a.name from t_name a, t_dep b
    where a.id = b.id
    and a.name like ''%' || hj || '%''';
    
open ref_cur_name for v_sql;

#9

可能是变量的问题。可以在v$sql_plan中查看两条语句的执行计划有何不同
数据量大的情况下like '%xx%'的写法效率很差
在这个场景里，如果是因为变量的原因，想让SQL语句像第一条那样执行，试试这么写

DECLARE
  v_name varchar2(10) := 'hj';
  cur_query sys_refcursor;
  type rec is record(name varchar2(1000));
  rec_query rec;

BEGIN
  open cur_query for 'select a.name from t_name a, t_dep b '
    ||'where a.id = b.id '
    ||'and a.name like ''%' || v_name || '%''';
  loop
    FETCH cur_query INTO rec_query.name;
    exit when cur_query%NOTFOUND;
  END LOOP; 
  close cur_query;
END;
/

#10

引用 2 楼 liao_z_j 的回复:

   CURSOR cur_query is
     select a.name from t_name a, t_dep b
     where a.id = b.id
     AND regexp_like(a.name,'*'||v_name||'*')
   rec_query cur_query%ROWTYPE;
 这样写

试了下，这个效率不行，也是半天不出结果...

#11

引用 9 楼 wildwave 的回复:

可能是变量的问题。可以在v$sql_plan中查看两条语句的执行计划有何不同
数据量大的情况下like '%xx%'的写法效率很差
在这个场景里，如果是因为变量的原因，想让SQL语句像第一条那样执行，试试这么写

DECLARE
  v_name varchar2(10) := 'hj';
  cur_query sys_refcursor;
  type rec is record(name varchar2(1000));
  rec_query rec;

BEGIN
  open cur_query for 'select a.name from t_name a, t_dep b '
    ||'where a.id = b.id '
    ||'and a.name like ''%' || v_name || '%''';
  loop
    FETCH cur_query INTO rec_query.name;
    exit when cur_query%NOTFOUND;
  END LOOP; 
  close cur_query;
END;
/

这个方法楼主刚试了，可行
不过我的代码是写在oracle form 9i里面，编译的时候报不支持open cur_query for 的特性，郁闷

#12

这个功能实现了（不过这个问题还是没得出结论，等待高高手来解答吧），结贴送分

最终解决方式是把这个游标去掉了，直接把变量拼接到sql语句中，让后把这个sql传给form（还好有这个特性）。