日期:2014-05-18  浏览次数:20465 次

表关联与exist的运行效率对比
如题。我写了俩段sql,一个是表关联的,一个是用exist的。A表数据量级百万,B表不到1万
从执行计划上看,是exist的耗费低;但是执行的时间,是表关联的用时少。
sql如下:SELECT COUNT(1) -- 耗费 9895 ,用时1.172s/1.532s/1.231s
  FROM table1 A, table2 B
  WHERE A.JOB_CODE IS NOT NULL AND B.FACTDETAILNO = '5005'  
  AND (B.FACTDETAILCODE = SUBSTR(A.JOB_CODE, 1, 3) OR
  B.FACTDETAILCODE = SUBSTR(A.JOB_CODE, 1, 2));

SELECT COUNT(1)-- 耗费 5950 ,用时3.721s/2.781s/4.562s
  FROM table1 A
 WHERE A.JOB_CODE IS NOT NULL
  AND EXISTS (SELECT 1
  FROM table2 B AND B.FACTDETAILNO = '5005' 
  WHERE (B.FACTDETAILCODE = SUBSTR(A.JOB_CODE, 1, 2) OR
  B.FACTDETAILCODE = SUBSTR(A.JOB_CODE, 1, 3))
  )
表关联,与exist的查询过程是怎么查询的,哪个更优些,或者分情况。
 

------解决方案--------------------
條件存在1對多關係時用第2种
------解决方案--------------------
用exist不稳定,看你内外表的匹配度,或快或慢
------解决方案--------------------
改改,有兩個地方沒改到
SQL code
SELECT COUNT(1)-- 耗费 5950 ,用时3.721s/2.781s/4.562s
  FROM table1 A
 WHERE A.JOB_CODE IS NOT NULL
  AND EXISTS   (SELECT 1     FROM table2 B   WHERE  B.FACTDETAILNO = '5005'   AND    A.JOB_CODE LIKE A.JOB_CODE+'%')