如何让in/exists子查询(半连接)作为驱动表
一哥们问我，怎么才能让子查询作为驱动表？ sql如下：
[html]
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
/
执行计划如下：
[html]
execution plan
----------------------------------------------------------
plan hash value: 3634433140
--------------------------------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 111 | 2 (50)| 00:00:01 | | |
| 1 | sort order by | | 1 | 111 | 2 (50)| 00:00:01 | | |
| 2 | nested loops | | | | | | | |
| 3 | nested loops | | 1 | 111 | 1 (0)| 00:00:01 | | |
| 4 | partition range single | | 1 | 39 | 1 (0)| 00:00:01 | 1284 | 1284 |
|* 5 | index skip scan | idx_vt7_deviceid | 1 | 39 | 1 (0)| 00:00:01 | 1284 | 1284 |
|* 6 | index unique scan | pk_its_base_device | 1 | | 0 (0)| 00:00:01 | | |
|* 7 | table access by index rowid| its_base_device | 1 | 72 | 0 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------
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
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
这里我们就不管统计信息是否准确了，也不管sql优化的问题，就单单讨论哥们问的问题吧。
那哥们说，怎么才能让子查询作为驱动表呢？他自己试了很多方法就是搞不定。 那我们来亲自搞搞吧
[html]
explain plan for 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
/
执行计划如下
[html]
select * from table(dbms_xplan.display(null, null, 'advanced -projection'));
-----------------------------------------------------------
plan hash value: 2191740724
---------------------------------------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 111 | 2092k (1)| 06:58:26 | | |
| 1 | nested loops | | | | | | | |
| 2 | nested loops | | 1 | 111 | 2092k (1)| 06:58:26 | | |
| 3 | partition range single | | 1 | 39 | 2092k (1)| 06:58:26 | 1284 | 1284 |
| 4 | table access by local index rowid| its_car_pass7 | 1 | 39 | 2092k (1)| 06:58:26 | 1284 | 1284 |
|* 5 | index range scan | idx_vt7_datetime | 1 | | 6029 (1)| 00:01:13 | 1284 | 1284 |
|* 6 | index unique scan | pk_its_base_device | 1 | | 0 (0)| 00:00:01 | | |
|* 7 | table access by index rowid | its_base_device | 1 | 72 | 0 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------
query block name / object alias (identified by operation id):
-------------------------------------------------------------
1 - sel$5da710d3
4 - sel$5da710d3 / v@sel$1
5 - sel$5da710d3 / v@sel$1
6 - sel$5da710d3 / its_base_device@sel$2
7 - sel$5da710d3 / its_base_device@sel$2
outline data
-------------
/*+
begin_outline_data
nlj_batching(@"sel$5da710d3" "its_base_device"@"sel$2")
use_nl(@"sel$5da710d3" "its_base_device"@"sel$2")
leading(@"sel$5da710d3" "v"@"sel$1" "its_base_device"@"sel$2")
index(@"sel$5da710d3" "its_base_device"@"sel$2" ("its_base_device"."unid"))
index_rs_asc(@"sel$5da710d3" "v"@"sel$1" ("its_car_pass7"."pass_datetime"))
outline(@"sel$2")
outline(@"sel$1")
unnest(@"sel$2")
outline_leaf(@"sel$5da710d3")
first_rows
db_version('11.2.0.3')
optimizer_features_enable('11.2.0.3')
ignore_optim_embedded_hints
end_outline_data
*/
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'))
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')
tmd 执行计划又变了，我们也先别管执行计划为啥变了，驱动表仍然是 its_car_pass7，现在我们来改变驱动表
[html]
select /*+ leading(its_base_device@sel$2) */ 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
----------------------------------------------------------
plan hash value: 712001411
-------------------------------------------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 111 | 25 (4)| 00:00:01 | | |
| 1 | sort order by | | 1 | 111 | 25 (4)| 00:00:01 | | |
|* 2 | table access by local index rowid| its_car_pass7 | 1 | 39 | 2 (0)| 00:00:01 | 1284 | 1284 |
| 3 | nested loops | | 1 | 111 | 24 (0)| 00:00:01 | | |
|* 4 | table access by index rowid | its_base_device | 6 | 432 | 12 (0)| 00:00:01 | | |
|* 5 | index range scan | idx_device_dev_bay_unid | 7 | | 1 (0)| 00:00:01 | | |
| 6 | partition range single | | 44m| | 2 (0)| 00:00:01 | 1284 | 1284 |
|* 7 | index range scan | idx_vt7_pass_device_unid | 44m| | 2 (0)| 00:00:01 | 1284 | 1284 |
-------------------------------------------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - 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'))
4 - filter("dev_type"='1' and "dev_delete_flag"='n' and "dev_chk_flag"='1')
5 - access("dev_bay_unid"='01685efe4658c19d59c4ddaaedd37393')
7 - access("v"."pass_device_unid"="unid")
statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
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
驱动表改了之后，逻辑读从11w降低为1.8w