如何让in/exists子查询(半连接)作为驱动表
程序员文章站
2023-11-18 18:41:04
如何让in/exists子查询(半连接)作为驱动表
一哥们问我,怎么才能让子查询作为驱动表? sql如下:
[html]
select rowid...
如何让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