SQL优化案例2-IN和EXISTS子查询作为驱动表优化

在NL连接方式中，用小表或小的结果集作业驱动表，是提高SQL执行效率的重要因素之一，下面我们看一下让in/exists子查询作为驱动表优化的案例。

select  rowid rid 
   from its_car_pass7 v 
  where 1 = 1 
    and pass_datetime >= 
        to_date('2013-07-06 :17:46:04', 'yyyy-mm-dd hh24:mi:ss') 
    and pass_datetime <= 
        to_date('2013-07-06 :18:46:06', 'yyyy-mm-dd hh24:mi:ss') 
    and v.pass_device_unid in 
        (select unid 
           from its_base_device 
          where dev_bay_unid in ('01685EFE4658C19D59C4DDAAEDD37393') 
            and dev_type = '1' 
            and dev_chk_flag = '1' 
            and dev_delete_flag = 'N') 
  order by v.pass_datetime asc 

Execution Plan  
 

Predicate Information (identified byoperation id): 
--------------------------------------------------- 
 
   5 -access("PASS_DATETIME">=TO_DATE(' 2013-07-06 17:46:04','syyyy-mm-dd hh24:mi:ss') AND 
              "PASS_DATETIME"<=TO_DATE(' 2013-07-06 18:46:06','syyyy-mm-dd hh24:mi:ss')) 
       filter("PASS_DATETIME">=TO_DATE('2013-07-06 17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND 
              "PASS_DATETIME"<=TO_DATE(' 2013-07-06 18:46:06','syyyy-mm-dd hh24:mi:ss')) 
   6 -access("V"."PASS_DEVICE_UNID"="UNID") 
   7 -filter("DEV_BAY_UNID"='01685EFE4658C19D59C4DDAAEDD37393' AND"DEV_TYPE"='1' AND 
              "DEV_DELETE_FLAG"='N' AND"DEV_CHK_FLAG"='1')  
Statistics 
---------------------------------------------------------- 
          1  recursivecalls 
          0  db block gets 
     110973  consistent gets 
          0  physical reads 
          0  redo size 
      47861  bytes sent via SQL*Net to client 
       1656  bytes received via SQL*Net from client 
        105  SQL*Net roundtripsto/from client 
          1  sorts (memory) 
          0  sorts (disk) 
       1560  rows processed 

查看outline部分信息

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));    

 OutlineData 
------------- 

select     rowid rid 

   from its_car_pass7 v 
  where 1 = 1 
    and pass_datetime >= 
        to_date('2013-07-06 :17:46:04', 'yyyy-mm-dd hh24:mi:ss') 
    and pass_datetime <= 
        to_date('2013-07-06 :18:46:06', 'yyyy-mm-dd hh24:mi:ss') 
    and v.pass_device_unid in 
        (select unid 
           from its_base_device 
          where dev_bay_unid in ('01685EFE4658C19D59C4DDAAEDD37393') 
            and dev_type = '1' 
            and dev_chk_flag = '1' 
            and dev_delete_flag = 'N') 
  order by v.pass_datetime asc 

优化后执行计划信息

Statistics 
---------------------------------------------------------- 
         1  recursivecalls 
         0  db blockgets  
   18645  consistent gets 
      130  physical reads 
        0  redo size 
    47861  bytes sent via SQL*Net to client 
     1657  bytes received via SQL*Net from client 
      105  SQL*Net roundtripsto/from client 
        1  sorts (memory) 
        0  sorts (disk) 
     1560  rows processed  

优化效果还是不错的，逻辑读有原有的 110973 变为：18645