如何让in/exists 子查询(半连接)作为驱动表？

来源：互联网发布：美国海军知乎编辑：程序博客网时间：2024/06/07 02:36

一哥们问我，怎么才能让子查询作为驱动表？ 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