在NL连接方式中，用小表或小的结果集作业驱动表，是提高SQL执行效率的重要因素之一，下面我们看一下让in/exists子查询作为驱动表优化的案例
[color=#1915fd]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 http://photo.blog.sina.com.cn/showpic.html#blogid=61cd89f60102efam&url=http://album.sina.com.cn/pic/001N2SGity6FT4MVeW945][img=690,246]http://s6.sinaimg.cn/mw690/001N2SGity6FT4MVeW945&690[/img] Predicate Information (identified by operation 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 recursive calls 0 db block gets [color=#fd0a39]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 roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1560 rows processed 查看outline部分信息 [color=#2b1fff]SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION')); Outline Data ------------- http://photo.blog.sina.com.cn/showpic.html#blogid=61cd89f60102efam&url=http://album.sina.com.cn/pic/001N2SGity6FT5o0nYe12][img]http://s3.sinaimg.cn/mw690/001N2SGity6FT5o0nYe12&690[/img] SQL优化后： [color=#1b10ff]select http://photo.blog.sina.com.cn/showpic.html#blogid=61cd89f60102efam&url=http://album.sina.com.cn/pic/001N2SGity6FT5HcuFI82][img]http://s3.sinaimg.cn/mw690/001N2SGity6FT5HcuFI82&690[/img] 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 优化后执行计划信息 http://photo.blog.sina.com.cn/showpic.html#blogid=61cd89f60102efam&url=http://album.sina.com.cn/pic/001N2SGity6FT4PJk1117][img=690,162]http://s8.sinaimg.cn/mw690/001N2SGity6FT4PJk1117&690[/img] Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets [color=#fd0a39] 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 roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1560 rows processed 优化效果还是不错的，逻辑读有原有的 [color=#fd0a39]110973 变为：18645