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

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要高么？求正解……

解决方案 »

  1.   

    in(a,b,c,d) 如果in中的个数是比较少的，个数是千个的话那当然会快罗如果你的一个表的大小是 几个G，你再试一下，看还是那样不？肯定不是了
      

  2.   

    具体情况具体分析。
    谁能说exists就比in效率一定高？
    可以把相关语句拿出来，查看一下执行计划对比分析一下。另外建议不要在过程内动态创建临时表。先在外部建好再使用。并肯没有必须drop.
    理解一下oracle临时表是什么“临时”。
      

  3.   


    -- 操：如果EXISTS一定比IN效率高，那还要IN干嘛啊？
      

  4.   

    -- 例如：有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；
      

  5.   

    没有执行计划来比较一下吗？另外in exists是不能一概而论的，不同的用法不同的效果。否则就没有存在的必要性了。自己应该多深入一下了解 。