`
844604778
  • 浏览: 552201 次
文章分类
社区版块
存档分类
最新评论

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


分享到：
| ProcessOn:划时代性的在线作图工具
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    SQL普查优化信息汇总

    很多情况下ORACLE并不能为我们的SQL语句选择最有效的驱动表, 在我们自己确定了合适的驱动表之后,可以使用HINT: ORDERED,LEADING来指定合适的驱动表 WHERE子句中的连接条件书写顺序 那些可以过滤掉最大数量记录的...

    C#开发经验技巧宝典

    0897　EXISTS与子查询联合应用　525 0898　在FROM子句中的子查询　525 0899　在DELETE语句中应用子查询　526 0900　子查询与聚合函数的应用　526 0901　有效使用内连接　526 0902　如何使用LEFT OUTER JOIN查询　526...

    Java数据库编程宝典2

    7.5.4 使用带有DELETE命令的子查询 7.5.5 关联子查询 7.6 JDBC ResultSet 7.6.1 ResultSetMetaData 7.6.2 使用具有JDBC的SELECT来返回RecordSet 7.7 基于Swing的SQL查询面板 7.7.1 View菜单 7.7.2 ...

    Java数据库编程宝典4

    7.5.4 使用带有DELETE命令的子查询 7.5.5 关联子查询 7.6 JDBC ResultSet 7.6.1 ResultSetMetaData 7.6.2 使用具有JDBC的SELECT来返回RecordSet 7.7 基于Swing的SQL查询面板 7.7.1 View菜单 7.7.2 ...

    Java数据库编程宝典1

    7.5.4 使用带有DELETE命令的子查询 7.5.5 关联子查询 7.6 JDBC ResultSet 7.6.1 ResultSetMetaData 7.6.2 使用具有JDBC的SELECT来返回RecordSet 7.7 基于Swing的SQL查询面板 7.7.1 View菜单 7.7.2 ...

    Java数据库编程宝典3

    7.5.4 使用带有DELETE命令的子查询 7.5.5 关联子查询 7.6 JDBC ResultSet 7.6.1 ResultSetMetaData 7.6.2 使用具有JDBC的SELECT来返回RecordSet 7.7 基于Swing的SQL查询面板 7.7.1 View菜单 7.7.2 ...

    互联网金融公司在分布式数据库的运维实践

    1、业务上子查询SQL过多，需要大量改写为join关联查询语句，开发需要更改代码在MariaDB5.3版本里，就已经对子查询进行了优化，并采用semijoin半连接方式将SQL改写为了表关联join，从而提高了查询速度。通常情况下，...

    收获不止SQL优化

    12.2.2　避免子查询的错误执行计划 350 12.2.3　所在环境的资源不足等问题 351 12.3　本章习题、总结与延伸 351 第13章　动手，过程函数优化让SQL飞 352 13.1　PL/SQL优化重点 353 13.1.1　定义类型的优化 353 ...

    收获，不止SQL优化--抓住SQL的本质

    12.2.2　避免子查询的错误执行计划 350 12.2.3　所在环境的资源不足等问题 351 12.3　本章习题、总结与延伸 351 第13章　动手，过程函数优化让SQL飞 352 13.1　PL/SQL优化重点 353 13.1.1　定义类型的优化 353 ...

    Delphi开发范例宝典目录

    实例294　创建表和删除表　388 实例295　对ADO控件的无数据库操作　389 第9章　SQL技术　391 9.1　通用查询　392 实例296　SQL语句的应用方法　392 实例297　SQL语句的模糊查询　393 实例298　高级Like语句　394 ...

    C#编程经验技巧宝典

    C#编程经验技巧宝典源代码，目录如下： 第1章　开发环境　1 &lt;br&gt;1.1　Visual Studio开发环境安装与配置　2 &lt;br&gt;0001　安装Visual Studio 2005开发环境须知　2 &lt;br&gt;0002　配置合适的Visual Studio 2005...

    软件工程工资管理系统

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[员工信息]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[员工信息]( [工号] [char](10) NOT NULL, [姓名] [char](10) NULL,...

Magicbox
Global site tag (gtag.js) - Google Analytics