sql连接的优化问题

一个简单的sql语句：

A.select w.bzz_bm, w.jcjg_jcz as bzz_val
  from ztjc_jcjg_tb w
 where bzz_bm in (417,418)
   and jcjg_kssj = (select max(jcjg_kssj)
                      from ztjc_jcjg_tb n
                     where w.bzz_bm = n.bzz_bm
                       and n.bzz_bm in (417,418)
                       and n.jcjg_kssj <= to_date('2010-12-23 08:00:00','yyyy-mm-dd hh24:mi:ss')
                       and n.jcjg_kssj > to_date('2010-12-23 08:00:00','yyyy-mm-dd hh24:mi:ss') - 1 / 24)；

ztjc_jcjg_tb 表里存有至少几千万条的数据，结果查询出来花了103.218秒，执行时间超过一分钟，只是一个简单查询出这个表里那两条记录在时间为最接近当天8点的值。

如果改成：

B.select w.bzz_bm, w.jcjg_jcz as bzz_val
  from ztjc_jcjg_tb w
 where bzz_bm in (417,418)
   and jcjg_kssj = (select max(jcjg_kssj)
                      from ztjc_jcjg_tb n
                     where  n.bzz_bm in (417,418)
                       and n.jcjg_kssj <= to_date('2010-12-23 08:00:00','yyyy-mm-dd hh24:mi:ss')
                       and n.jcjg_kssj > to_date('2010-12-23 08:00:00','yyyy-mm-dd hh24:mi:ss') - 1 / 24)

执行时间绝对在1秒之内，我测试的是0.171秒。

如果改成：

C.select w.bzz_bm, w.jcjg_jcz as bzz_val
  from ztjc_jcjg_tb w
 where bzz_bm = 417
   and jcjg_kssj = (select max(jcjg_kssj)
                      from ztjc_jcjg_tb n
                     where w.bzz_bm = n.bzz_bm
                       and n.bzz_bm = 417
                       and n.jcjg_kssj <= to_date('2010-12-23 08:00:00','yyyy-mm-dd hh24:mi:ss')
                       and n.jcjg_kssj > to_date('2010-12-23 08:00:00','yyyy-mm-dd hh24:mi:ss') - 1 / 24)

同样超过一分钟，我测试的是187.218秒，但是此时再写w.bzz_bm = n.bzz_bm就没有意义了，所以去掉以后测试一分钟内，0.031秒。

如果改成：

D.select bzz_bm,max(jcjg_kssj) as kssj,jcjg_jcz
          from ztjc_jcjg_tb n
         where n.bzz_bm in (417, 418)
           and n.jcjg_kssj <=
               to_date('2010-12-23 08:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and n.jcjg_kssj >
               to_date('2010-12-23 08:00:00', 'yyyy-mm-dd hh24:mi:ss') - 1 / 24 group by bzz_bm,jcjg_jcz

执行时间在1秒内，我测试的是0.281秒；

如果改成：

E.select w.bzz_bm, w.jcjg_jcz as bzz_val
  from ztjc_jcjg_tb w,
       (select bzz_bm, max(jcjg_kssj) as kssj
          from ztjc_jcjg_tb n
         where n.bzz_bm in (417, 418)
           and n.jcjg_kssj <=
               to_date('2010-12-23 08:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and n.jcjg_kssj >
               to_date('2010-12-23 08:00:00', 'yyyy-mm-dd hh24:mi:ss') -
               1 / 24
         group by bzz_bm) n
 where w.bzz_bm in (417, 418)
   and w.jcjg_kssj = n.kssj
   and w.bzz_bm = n.bzz_bm

执行时间又超过一分钟，我测试的是101.344秒，但如果去掉w.bzz_bm in (417, 418)这句，执行时间在1秒内，测试为0.015秒。

比较分析这些例子得出：影响这个SQL语句的因素有两个，一个是in，一个是连接。其实in就是把外表和内表做hash连接。hash join处理代价非常高，是数据库服务器内存和CPU的头号杀手之一，尤其是涉及到分区（数据量太大导致内存不够的情况，或者并发访问很高导致当前处理线 程无法获得足够的内存，那么数据量不是特大的情况下也可能需要进行分区）。举例如下：

Select * from T1 where x in ( select y from T2 )，执行过程相当于select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;这样表 T1 不可避免的要被完全扫描一遍，而表T1本身的数据量又是相当的大。B，D的执行速度倒是很快，可是不符合我们的规则，A，C是一个道理，加上连接后变的很慢，因为他对两个大数据表做的hash连接，而根据上面in的用法，相当于加上了distinct，distinct会使索引部分失效，因为它对你要查询的每一列进行筛选，再根据记录重组；E之所以变的很快，是因为内表的数据相当少，只有两条，而在bzz_bm和jcjg_kssj上建有索引，所以直接在外表里面查找就会变的很快。