欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

如何让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