如何让in/exists 子查询(半连接)作为驱动表？
一哥们问我，怎么才能让子查询作为驱动表？ SQL如下：
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----------------------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优化的问题，就单单讨论哥们问的问题吧。
那哥们说，怎么才能让子查询作为驱动表呢？他自己试了很多方法就是搞不定。 那我们来亲自搞搞吧
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 /执行计划如下
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，现在我们来改变驱动表
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