想不通IN为什么会比EXISTS效率高……

beamofsoul 2011-05-25 03:55:13
rt！



CREATE OR REPLACE PROCEDURE COLLECT(

       I_FORM_ID IN NUMBER,

       I_PERIOD_ID IN NUMBER,

       I_AREA_CODE IN VARCHAR2,

       I_TYPE IN NUMBER,            

       I_ORG_ID IN NUMBER,          

       I_USER_ID IN NUMBER,         

       I_REPORT_RECORD_ID IN NUMBER 

       )

       IS

                   V_STR VARCHAR2(4000);

                   V_VAL NUMBER;

                   V_F_TO_D_ID NUMBER;

                   V_DIC_CN_NAME VARCHAR2(100);

                   V_DIC_TYPE NUMBER;

                   V_FORM_ID NUMBER;

                   

                   V_NUMBER NUMBER;

      

                   V_YESTERYEAR_CLINIC INTEGER:=0;

                   V_YESTERYEAR_INPATIENT INTEGER:=0;

                   

                   V_AHEAD_RETIRE INTEGER:=0;

                   V_SICKNESS_RETIRE INTEGER:=0;

                   V_ALTERNATION INTEGER:=0;

                   V_RESIGNATION INTEGER:=0;

       BEGIN

        

   SELECT

        (CASE WHEN SUM(T1) > 0 THEN ROUND(SUM(A.T1 * A.T2) / SUM(T1), 2)END),

        (CASE WHEN SUM(T3) > 0 THEN ROUND(SUM(A.T3 * A.T4) / SUM(T3), 2)END)

   INTO V_YESTERYEAR_CLINIC, V_YESTERYEAR_INPATIENT

   FROM (SELECT T.RECORD_ID,

                SUM(DECODE(T.DIC_NAME, 'ORG_ITEM_31', T.FORM_DIC_VALUE, 0)) T1,

                SUM(DECODE(T.DIC_NAME, 'ORG_ITEM_33', T.FORM_DIC_VALUE, 0)) T2,

                SUM(DECODE(T.DIC_NAME, 'ORG_ITEM_32', T.FORM_DIC_VALUE, 0)) T3,

                SUM(DECODE(T.DIC_NAME, 'ORG_ITEM_34', T.FORM_DIC_VALUE, 0)) T4

           FROM FORM_DATA T, REPORT_RECORD R

          WHERE R.FORM_ID = I_FORM_ID

            AND R.PERIOD_ID = I_PERIOD_ID

            AND R.RECORD_FLAG = 3

            /*

            AND EXISTS (

                     SELECT 'X' 

                       FROM ORGANIZATION O 

                      WHERE R.ORGANIZATION_ID = O.ID

                        AND O.IS_OPEN = 1

                        START WITH O.AREA_CODE = I_AREA_CODE

                        CONNECT BY O.PARENT_ID = PRIOR O.ID

                     )

           */

            AND R.ORGANIZATION_ID IN

                (SELECT O.ID

                   FROM ORGANIZATION O

                  WHERE O.IS_OPEN = 1

                  START WITH O.AREA_CODE = I_AREA_CODE

                  CONNECT BY O.PARENT_ID = PRIOR O.ID)

           

            AND R.RECORD_ID = T.RECORD_ID

          GROUP BY T.RECORD_ID) A;

           

  SELECT

         COUNT(CASE WHEN E.FEN_LIU_VALUE = 1 THEN 1 END),

         COUNT(CASE WHEN E.FEN_LIU_VALUE = 2 THEN 1 END),

         COUNT(CASE WHEN E.FEN_LIU_VALUE = 3 THEN 1 END),

         COUNT(CASE WHEN E.FEN_LIU_VALUE = 4 THEN 1 END)

    INTO V_AHEAD_RETIRE,V_SICKNESS_RETIRE,V_ALTERNATION,V_RESIGNATION

    FROM EMPLOYEE E, ORGANIZATION O

   WHERE 

       /*

       EXISTS (

            SELECT 'X' 

              FROM ORGANIZATION O 

             WHERE E.ORGANIZATION_ID = O.ID

               AND O.IS_OPEN = 1

             START WITH O.AREA_CODE = I_AREA_CODE

           CONNECT BY O.PARENT_ID = PRIOR O.ID

              );

         */

         E.ORGANIZATION_ID IN

         (SELECT O.ID

            FROM ORGANIZATION O

           WHERE O.IS_OPEN = 1

           START WITH O.AREA_CODE = I_AREA_CODE

          CONNECT BY O.PARENT_ID = PRIOR O.ID);

   

   SELECT COUNT(1) INTO V_NUMBER FROM ALL_TABLES WHERE TABLE_NAME = 'SST';

   IF V_NUMBER=1 THEN

     EXECUTE IMMEDIATE 'DROP TABLE SST';

     END IF;



   V_STR:='CREATE GLOBAL TEMPORARY TABLE SST ( 

                COLLECT_ITEM_1 NUMBER, 

                COLLECT_ITEM_2 NUMBER,

                COLLECT_ITEM_3 NUMBER,

                COLLECT_ITEM_4 NUMBER,

                COLLECT_ITEM_5 NUMBER, 

                COLLECT_ITEM_6 NUMBER,

                COLLECT_ITEM_7 NUMBER,

                COLLECT_ITEM_8 NUMBER,

                COLLECT_ITEM_9 NUMBER, 

                COLLECT_ITEM_10 NUMBER,

                COLLECT_ITEM_11 NUMBER,

                COLLECT_ITEM_12 NUMBER,

                COLLECT_ITEM_13 NUMBER, 

                COLLECT_ITEM_14 NUMBER,

                COLLECT_ITEM_15 NUMBER,

                COLLECT_ITEM_16 NUMBER,

                COLLECT_ITEM_17 NUMBER, 

                COLLECT_ITEM_18 NUMBER,

                COLLECT_ITEM_19 NUMBER,

                COLLECT_ITEM_20 NUMBER,

                COLLECT_ITEM_21 NUMBER, 

                COLLECT_ITEM_22 NUMBER,

                COLLECT_ITEM_23 NUMBER,

                COLLECT_ITEM_24 NUMBER,

                COLLECT_ITEM_25 NUMBER,

                COLLECT_ITEM_26 NUMBER,

                COLLECT_ITEM_27 NUMBER

                ) 

                ON COMMIT PRESERVE ROWS'; 

   EXECUTE IMMEDIATE V_STR;

   

   

   V_STR:='

   INSERT INTO SST (

   SELECT 

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_1'' THEN 1 END) COLLECT_ITEM_1,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_2,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=7 THEN 1 END) COLLECT_ITEM_3,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=6 THEN 1 END) COLLECT_ITEM_4,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_66'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_5,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_88'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_6,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_92'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_7,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_67'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_8,

         SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_70'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_9,

         '||V_RESIGNATION||' AS COLLECT_ITEM_10,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_75'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_11,

         SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_76'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_12,

         SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_77'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_13,

         SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_78'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_14,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_79'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_15,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_82'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_16,

         SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_89'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_17,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_84'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_18,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_86'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_19,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_87'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_20,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_87'' AND T.FORM_DIC_VALUE=0 THEN 1 END) COLLECT_ITEM_21,

         COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_90'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_22,

         '||NVL(V_YESTERYEAR_CLINIC,0)||' AS COLLECT_ITEM_23,

         '||NVL(V_YESTERYEAR_INPATIENT,0)||' AS COLLECT_ITEM_24,

         '||V_AHEAD_RETIRE||' AS COLLECT_ITEM_25,

         '||V_SICKNESS_RETIRE||' AS COLLECT_ITEM_26,

         '||V_ALTERNATION||' AS COLLECT_ITEM_27

  FROM FORM_DATA T,REPORT_RECORD R

  WHERE  R.FORM_ID='||I_FORM_ID||'

  AND R.PERIOD_ID='||I_PERIOD_ID||'

  AND R.RECORD_FLAG = 3

  AND R.ORGANIZATION_ID IN (SELECT O.ID

  FROM ORGANIZATION O WHERE O.IS_OPEN = 1

  START WITH O.AREA_CODE='''||I_AREA_CODE||'''

  CONNECT BY O.PARENT_ID= PRIOR O.ID)

  AND R.RECORD_ID = T.RECORD_ID

  )';

  --Disp_Long_String(V_STR);

  EXECUTE IMMEDIATE V_STR;

  COMMIT;

  

  

  IF I_TYPE = 1 THEN

    V_FORM_ID:=58;

    END IF;

  IF I_TYPE = 2 THEN

    V_FORM_ID:=59;

    END IF;

  IF I_TYPE = 3 THEN

    V_FORM_ID:=60;

    END IF;

    

  IF I_REPORT_RECORD_ID IS NULL THEN

  

  

  INSERT INTO REPORT_RECORD VALUES(

                                      REPORT_RECORD_SEQ.Nextval,

                                      I_ORG_ID,

                                      (SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) FROM DUAL),

                                      V_FORM_ID,

                                      I_PERIOD_ID,

                                      I_USER_ID,

                                      (SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') FROM DUAL),

                                      I_ORG_ID,

                                      1,

                                      NULL

                                  );

  COMMIT;

  

  ELSE

    

  

  UPDATE REPORT_RECORD R 

  SET R.RECORD_FLAG = 1

  WHERE R.RECORD_ID = I_REPORT_RECORD_ID;

  COMMIT;

  

  V_STR:='TRUNCATE TABLE FORM_DATA WHERE FORM_ID='||V_FORM_ID||'';

  EXECUTE IMMEDIATE V_STR;

      

  END IF;





  FOR RS IN (SELECT DISTINCT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='SST')

        LOOP

            EXECUTE IMMEDIATE 'SELECT '||RS.COLUMN_NAME||' FROM SST' INTO V_VAL;

            

            SELECT 

                    FTD.F_TO_D_ID,

                    FTD.DIC_CN_NAME,

                    FTD.DIC_TYPE 

              INTO  V_F_TO_D_ID,

                    V_DIC_CN_NAME,

                    V_DIC_TYPE 

              FROM  FORM_TO_DIC FTD

             WHERE  FTD.DIC_NAME = RS.COLUMN_NAME 

               AND  ROWNUM <=1;

            

            INSERT INTO FORM_DATA VALUES(

                REPORT_RECORD_SEQ.CURRVAL,

                V_FORM_ID,

                V_F_TO_D_ID,

                V_VAL,

                RS.COLUMN_NAME,

                V_DIC_CN_NAME,

                V_DIC_TYPE,

                0,

                1,

                NULL,

                1);

        COMMIT;

        END LOOP;

        

  COMMIT;

  

  V_STR:='TRUNCATE TABLE SST';

  EXECUTE IMMEDIATE V_STR;

  V_STR:='DROP TABLE SST';

  EXECUTE IMMEDIATE V_STR; 



END COLLECT;


IN 1.7秒
EXISTS 3.5秒

传说中哪本书上，不是说EXISTS效率比IN要高么？求正解……
...全文
191 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
liuyi8903 2011-05-26
  • 打赏
  • 举报
 回复
没有执行计划来比较一下吗？

另外in exists是不能一概而论的，不同的用法不同的效果。否则就没有存在的必要性了。

自己应该多深入一下了解 。
njlywy 2011-05-25
  • 打赏
  • 举报
 回复
视情况而定，存在即合理…
hexiaofang_117 2011-05-25
  • 打赏
  • 举报
 回复
具体情况具体分析
luoyoumou 2011-05-25
  • 打赏
  • 举报
 回复 
-- 例如：有a、b两表，都是三个字段(id, name, sex)，

-- 有以下两个查询语句：



-- 方法一：

select a.id, a.name, a.sex

  from a

where a.id in (select b.id from b where b.sex=1);



-- 方法二：

select a.id, a.name, a.sex

  from a

where exists (select 1 from b where b.id=a.id and b.sex=1);



-- 如果子查询(select b.id from b where b.sex=1)返回的记录行非常少(比外层表要少N倍)，

-- 则可以用in(且最好用in)；

-- 如果子查询(select b.id from b where b.sex=1)返回的记录行非常多(比外层查询要多N倍)，

-- 则此时最用用exists替换in；
luoyoumou 2011-05-25
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 tangren 的回复:]

具体情况具体分析。
谁能说exists就比in效率一定高？
可以把相关语句拿出来，查看一下执行计划对比分析一下。

另外建议不要在过程内动态创建临时表。先在外部建好再使用。并肯没有必须drop.
理解一下oracle临时表是什么“临时”。
[/Quote]

-- 操：如果EXISTS一定比IN效率高，那还要IN干嘛啊？
beamofsoul 2011-05-25
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 tangren 的回复:]
具体情况具体分析。
谁能说exists就比in效率一定高？
可以把相关语句拿出来，查看一下执行计划对比分析一下。

另外建议不要在过程内动态创建临时表。先在外部建好再使用。并肯没有必须drop.
理解一下oracle临时表是什么“临时”。
[/Quote]

过程中动态创建临时表其实也是实属无奈，本来临时表是指表中的数据是临时的，不过我确实需要一个我这种使用方式的‘临时表’。哪位有更好的方法，还望不吝赐教~
304的的哥 2011-05-25
  • 打赏
  • 举报
 回复
凡事无绝对，乔丹
tangren 2011-05-25
  • 打赏
  • 举报
 回复
具体情况具体分析。
谁能说exists就比in效率一定高？
可以把相关语句拿出来，查看一下执行计划对比分析一下。

另外建议不要在过程内动态创建临时表。先在外部建好再使用。并肯没有必须drop.
理解一下oracle临时表是什么“临时”。

年华似水 2011-05-25
  • 打赏
  • 举报
 回复
in(a,b,c,d) 如果in中的个数是比较少的，个数是千个的话那当然会快罗

如果你的一个表的大小是 几个G，你再试一下，看还是那样不？肯定不是了
magicmaster 2011-05-25
  • 打赏
  • 举报
 回复
呃，路过，帮顶
零基础快速上手【软件测试常用SQL语句】实战课程
跟着课程大纲学习，了解数据库，了解软件测试过程中常用SQL语句，在以后的测试工作中，可以通过查询数据，分析数据，定位BUG。课程大纲如下：数据库的概念常用数据库软件+数据库理解数据库服务器连接工具介绍Navicat连接禅道MySQL服务器Navicat工具的筛选查询MySQL-单表查询语法格式MySQL-条件查询MySQL-多表联查语法格式MySQL-多表联查-设置列和表的别名MySQL-多表联查-2张表之间的关联MySQL-子查询-关键字inMySQL-子查询-关键字existsMySQL-进阶提升学习内容
sql优化--in和exists效率
in 和exists in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询。 一直以来认为exists 比in 效率高的说法是不准确的。如果查询的两个表大小相当，那么用in 和exists 差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 例如： 表A（小表），表B（大表）1...
IN和EXISTS的详解
从效率来看： 1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ; T1数据量小而T2数据量非常大时，T1&lt;&lt;T2 时，1) 的查询效率高。 2) select * from T1 where T1.a in (select T2.a from T2) ; T1数据量非常大而...
mysql 优化in 和exists
in 和exists in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询。 一直以来认为exists 比in 效率高的说法是不准确的。如果查询的两个表大小相当，那么用in 和exists 差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 例如： 表A（小表），表B（大表）1： ...
SQL查询中in、exists、not in、not exists的用法与区别
1、in和exists in是把外表和内表作hash(字典集合)连接，而exists是对外表作循环，每次循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的，如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in。 例如：表A(小表)，表B(大表) 方式一：索引使用 1）select * fr...
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章