请教一条查询语句的效率问题

Question

请教一条sql语句的执行效率
SELECT wo_no
      FROM permit_active_work_order_tab
      WHERE ( Permit_API.Get_Permit_Type_Id(permit_seq_) IN ('aa', 'bb', 'cc') )
      AND   permit_seq = permit_seq_
      AND   SUBSTR(WORK_ORDER_CONNECTION_API.Has_Connection_Down(WO_NO),1,5)='TRUE'
      ORDER BY wo_no;我在执行上述sql语句时发现效率很低
请教高手，这个原因是什么？
附函数体：Permit_API.Get_Permit_Type_Id 和 WORK_ORDER_CONNECTION_API.Has_Connection_Down
FUNCTION Get_Permit_Type_Id (
   permit_seq_ IN NUMBER ) RETURN VARCHAR2
IS
   temp_ PERMIT_TAB.permit_type_id%TYPE;
   CURSOR get_attr IS
      SELECT permit_type_id
      FROM PERMIT_TAB
      WHERE permit_seq = permit_seq_;
BEGIN
   OPEN get_attr;
   FETCH get_attr INTO temp_;
   CLOSE get_attr;
   RETURN temp_;
END Get_Permit_Type_Id;FUNCTION Has_Connection (
   wo_no_ IN NUMBER,
   conn_up_down_ IN VARCHAR2 ) RETURN VARCHAR2
IS
CURSOR C1 IS
   SELECT 1
   FROM   WORK_ORDER_CONNECTION_TAB
   WHERE  wo_no = wo_no_;CURSOR C2 IS
   SELECT 1
   FROM   WORK_ORDER_CONNECTION_TAB
   WHERE  connected_wo_no = wo_no_;has_connection_ NUMBER;BEGIN
   has_connection_ := 0;
   IF (conn_up_down_ = 'DOWNWARDS') THEN
      OPEN C1;
      FETCH C1 INTO has_connection_;
      CLOSE C1;
      IF (has_connection_ = 1) THEN
         RETURN('TRUE');
      ELSE
         RETURN('FALSE');
      END IF;
   ELSIF (conn_up_down_ = 'UPWARDS') THEN
      OPEN C2;
      FETCH C2 INTO has_connection_;
      CLOSE C2;
      IF (has_connection_ = 1) THEN
         RETURN('TRUE');
      ELSE
         RETURN('FALSE');
      END IF;
   ELSE
      RETURN('FALSE');
   END IF;
END Has_Connection;

Answer 1

最好是把函数中的代码提练出来，用纯SQL来完成，效率要高很多

Answer 2

你这两个function都可以直接写在where里面，第一个IN ('aa', 'bb', 'cc')直接放sql判断第二个用case when

Answer 3

SELECT wo_no
  FROM permit_active_work_order_tab
  WHERE 
exists(SELECT t.permit_type_id
  FROM PERMIT_TAB t WHERE t.permit_seq = permit_seq_ and t.permit_type_id IN ('aa', 'bb', 'cc'))
  AND permit_seq = permit_seq_
  AND 1 = CASE WO_NO
when 'DOWNWARDS' then
SELECT max(1)
  FROM WORK_ORDER_CONNECTION_TAB
  WHERE wo_no = WO_NO
and rownum = 1
when 'UPWARDS' then
  SELECT max(1)
  FROM WORK_ORDER_CONNECTION_TAB
  WHERE connected_wo_no = wo_no_
ELSE null END
  ORDER BY wo_no;

Answer 4

另外WORK_ORDER_CONNECTION_API.Has_Connection_Down(WO_NO)和FUNCTION Has_Connection (
  wo_no_ IN NUMBER,
  conn_up_down_ IN VARCHAR2 ) RETURN VARCHAR2声明的不一致

Answer 5

我刚才又测试了一下，发现另外一个查询仅仅比这个查询少了一个where条件：
AND   SUBSTR(WORK_ORDER_CONNECTION_API.Has_Connection_Down(WO_NO),1,5)='TRUE'速度问题就不是那么明显了。是不是说明问题主要出在这个where 上？我是用oracle 的profiler做的时间测试。

Answer 6

另外，Has_Connection_Down 函数的实体为：FUNCTION Has_Connection_Down (
   wo_no_ IN NUMBER ) RETURN VARCHAR2
IS
BEGIN
   RETURN Has_Connection(wo_no_,'DOWNWARDS');
END Has_Connection_Down;

Answer 7

还想请教一下：
SELECT wo_no
  FROM permit_active_work_order_tab
  WHERE 
exists(SELECT t.permit_type_id
  FROM PERMIT_TAB t WHERE t.permit_seq = permit_seq_ and t.permit_type_id IN ('aa', 'bb', 'cc'))
  AND permit_seq = permit_seq_
  AND 1 = CASE WO_NO
when 'DOWNWARDS' then
SELECT max(1)
  FROM WORK_ORDER_CONNECTION_TAB
  WHERE wo_no = WO_NO
and rownum = 1
when 'UPWARDS' then
  SELECT max(1)
  FROM WORK_ORDER_CONNECTION_TAB
  WHERE connected_wo_no = wo_no_
ELSE null END
  ORDER BY wo_no;
这段中的AND 1 = CASE WO_NO
when 'DOWNWARDS' then
SELECT max(1)
  FROM WORK_ORDER_CONNECTION_TAB
  WHERE wo_no = WO_NO
and rownum = 1
when 'UPWARDS' then
  SELECT max(1)
  FROM WORK_ORDER_CONNECTION_TAB
  WHERE connected_wo_no = wo_no_
ELSE null END为什么要用max(1) 是否可以直接用 select 1 呢？

Answer 8

你可以试试，我这里没有环境，我不记得了，不过我记得用max的话没有数据会返回NULLCASE WHEN里面有个地方忘记加rownum = 1了

Answer 9

SUBSTR(WORK_ORDER_CONNECTION_API.Has_Connection_Down(WO_NO),1,5)这个function是不能做函数索引的，只有把sql写在外面了

Answer 10

请教 如果我用 WORK_ORDER_CONNECTION_API.Has_Connection_Down(WO_NO) like '%true%'  代替SUBSTR(WORK_ORDER_CONNECTION_API.Has_Connection_Down(WO_NO),1,5)='TRUE'
是否能够提高查询效率？

Answer 11

你如果不打算把function里头的东西提出来的话，效率不可能有什么明显的变化，like这种通配符会全表扫描的。

Answer 12

查询如果要优化，只能是建立索引和SQL语句的写法

Answer 13

因为我根据高手们的提示考虑原来的时候   函数包含在substr这个函数里，建立函数索引不会有用。
现在把substr改成like 然后 针对WORK_ORDER_CONNECTION_API.Has_Connection_Down(WO_NO)这个函数建立专门的索引是否就可以有效果了呢？

Answer 14

like 效果不好
如果改成  like 'true%' 也许有效果
用 intstr ()>0 的写法 会好些
还是建议 把函数 提炼成 sql 

Answer 15

刚想试一下函数索引，但不知道怎么回事索引总是没有用到，查了一下发现很有搞头 http://ms.itpub.net/thread-1253175-1-1.html 这个是我找到的一个解释，还不是很明白，希望能给楼主点帮助，期待其他大牛的意见

Answer 16

那篇文章已经说了
自定义函数需DETERMINISTIC关键字也就是说函数返回值是确定的，简单的说，就是入参一定的情况下返回的值也是一定的。为什么要求这样？简单的说就是， oralce对这个函数的返回值和参数值做了计算，用于下次查询时候的匹配，如果入参一定的情况下函数返回的值不一定，oracle是找不到数据的。你的function查了表里面的数据，如果你那几张表数据不会变的话那没问题这个是我的理解，请高手指正你那句sql，建议把function里面的东西拿出来，function里面有两个分支，sql语句用union all在最外层实现，union all两部分的sql直接关联表速度会比较快，包括我一开始讲的方法，你可以试试那个快

Answer 17

SELECT wo_no
  FROM permit_active_work_order_tab
  WHERE 
exists(SELECT t.permit_type_id
  FROM PERMIT_TAB t WHERE t.permit_seq = permit_seq_ and t.permit_type_id IN ('aa', 'bb', 'cc'))
  AND permit_seq = permit_seq_
  AND 1 = CASE WO_NO
when 'DOWNWARDS' then
SELECT max(1)
  FROM WORK_ORDER_CONNECTION_TAB
  WHERE wo_no = WO_NO
and rownum = 1
when 'UPWARDS' then
  SELECT max(1)
  FROM WORK_ORDER_CONNECTION_TAB
  WHERE connected_wo_no = wo_no_
ELSE null END
  ORDER BY wo_no;
关于这段语句中的case when我有些疑问：case 后面跟的wo_no是传入的参数是吧   那么when后面的条件是针对什么来说的呢？wo_no吗？

Answer 18

我想请问一下palm_civet这位仁兄，我现在的情况如下：create index idx on test1(upper(name))
select count(*) from test1 where upper(name) = 'abc' 这种情况依然是全表查询，强制索引亦然。这样的话是因为函数是不确定的还是该查询不是基于代价的查询呢？