`

包含IN子查询的SQL语句的优化

SQLUnixAccess.net 
阅读更多

 当SQL语句中包含in语句时,有时候会极大的影响性能,我们可以把in子查询用exists子查询或外连接替代:

例子如下:

   1.SQL语句中包含IN子查询:

SQL> select * from servers s
  2   where s.srvr_id = 3333333 or
  3   s.srvr_id in (select t.srvr_id
  4                         from serv_inst t
  5                        where t.si_status = 'Activated'
  6                          and t.type = 'UNIX')
  7  /

已选择11行。


执行计划
----------------------------------------------------------
Plan hash value: 910321333

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     6 |   378 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL| SERVERS   |    96 |  6048 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| SERV_INST |     2 |    38 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("S"."SRVR_ID"=3333333 OR  EXISTS (SELECT /*+ */ 0 FROM
              "SERV_INST" "T" WHERE "T"."SRVR_ID"=:B1 AND "T"."SI_STATUS"='Activated'
              AND "T"."TYPE"='UNIX'))
   3 - filter("T"."SRVR_ID"=:B1 AND "T"."SI_STATUS"='Activated' AND
              "T"."TYPE"='UNIX')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        818  consistent gets
          0  physical reads
          0  redo size
       1146  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL>

 2.把上面SQL语句改为EXISTS子查询形式

SQL> select *
  2    from servers s
  3   where s.srvr_id = 3333333 or exists (select 1
  4                         from serv_inst t
  5                        where  s.srvr_id = t.srvr_id 
  6                          and t.si_status = 'Activated'
  7                          and t.type = 'UNIX')
  8  /
 
已选择11行。


执行计划
----------------------------------------------------------
Plan hash value: 910321333

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     6 |   378 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL| SERVERS   |    96 |  6048 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| SERV_INST |     2 |    38 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("S"."SRVR_ID"=3333333 OR  EXISTS (SELECT /*+ */ 0 FROM
              "SERV_INST" "T" WHERE "T"."SRVR_ID"=:B1 AND "T"."SI_STATUS"='Activated'
              AND "T"."TYPE"='UNIX'))
   3 - filter("T"."SRVR_ID"=:B1 AND "T"."SI_STATUS"='Activated' AND
              "T"."TYPE"='UNIX')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        818  consistent gets
          0  physical reads
          0  redo size
       1146  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL>

3.改成另一种外连接的方式

SQL> select distinct s.*
  2    from servers s
  3    left join serv_inst t on s.srvr_id = t.srvr_id
  4                         and t.si_status = 'Activated'
  5                         and t.type = 'UNIX'
  6   where ((t.siid is not null) or (t.siid is null and s.srvr_id = 3333333))
  7  /

已选择11行。

执行计划
----------------------------------------------------------
Plan hash value: 2837582902

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    96 |  8448 |     7  (29)| 00:00:01 |
|   1 |  HASH UNIQUE         |           |    96 |  8448 |     7  (29)| 00:00:01 |
|*  2 |   FILTER             |           |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |           |    96 |  8448 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| SERVERS   |    96 |  6048 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| SERV_INST |    57 |  1425 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."SIID" IS NOT NULL OR "T"."SIID" IS NULL AND
              "S"."SRVR_ID"=3333333)
   3 - access("S"."SRVR_ID"="T"."SRVR_ID"(+))
   5 - filter("T"."TYPE"(+)='UNIX' AND "T"."SI_STATUS"(+)='Activated')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1154  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL>

 4.另一种外连接的写法

SQL> select distinct s.*
  2    from servers s, serv_inst t
  3   where s.srvr_id = t.srvr_id(+)
  4     and t.si_status(+) = 'Activated'
  5     and t.type(+) = 'UNIX'
  6     and ((t.siid is not null) or (t.siid is null and s.srvr_id = 3333333))
  7  /

已选择11行。


执行计划
----------------------------------------------------------
Plan hash value: 2837582902

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    96 |  8448 |     7  (29)| 00:00:01 |
|   1 |  HASH UNIQUE         |           |    96 |  8448 |     7  (29)| 00:00:01 |
|*  2 |   FILTER             |           |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |           |    96 |  8448 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| SERVERS   |    96 |  6048 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| SERV_INST |    57 |  1425 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."SIID" IS NOT NULL OR "T"."SIID" IS NULL AND
              "S"."SRVR_ID"=3333333)
   3 - access("S"."SRVR_ID"="T"."SRVR_ID"(+))
   5 - filter("T"."SI_STATUS"(+)='Activated' AND "T"."TYPE"(+)='UNIX')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1154  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL>

 以上三种方式比较,看来还是用外连接的方式查询效率更高

分享到：
| 关于数据文件头信息记录
评论
发表评论

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

相关推荐

    Effective MySQL之SQL语句最优化.pdf

    10. SQL语句优化的技术手段：技术手段包括但不限于使用子查询优化、使用JOIN代替子查询、避免SELECT *、使用更有效的查询方法（如IN代替OR）、利用数据库提供的存储过程和函数减少网络往返次数等。 11. 经验与实践...

    新一代智能SQL语句优化技术

    SQL语句优化涉及到多个方面，包括但不限于数据库设计、编程错误、索引问题以及SQL语句本身的问题。数据库设计问题可能源自不合理的数据模型、过度或不足的表关联等；编程问题可能是因为不恰当的事务处理、错误的游标...

    非常好用的SQL语句优化34条+sql语句基础

    在SQL编程领域，掌握高效的SQL语句优化技巧和基础知识是至关重要的。以下是对"非常好用的SQL语句优化34条+sql语句基础"这一主题的详细解析： 1. **索引优化**：索引是提高查询速度的关键。创建合适的索引（主键、...

    sql语句优化原则

    11. **优化T-SQL编写**：编写高效的SQL语句，减少不必要的JOIN操作，避免子查询和嵌套循环，合理使用临时表和存储过程，以及使用合适的数据类型，都是提高SQL性能的重要手段。 综上所述，SQL语句优化是一个涉及多...

    oracle性能优化之SQL语句优化

    除了上述方法，还可以通过重构SQL语句、使用索引、避免全表扫描、减少子查询、优化联接顺序等方式进行SQL优化。例如，创建合适的索引可以加速查询，但过多的索引可能会增加插入、更新和删除操作的开销。因此，索引的...

    SQL语句优化规则

    - **减少子查询**：尽可能将子查询转换为JOIN操作，因为JOIN在优化器中可能有更好的性能表现。 - **使用绑定变量**：绑定变量可以减少解析次数，提高SQL语句的可重用性。 - **考虑数据分布**：了解数据的分布特性...

    JAVA-SQL语句优化.doc

    最后，注意在复杂的查询中，使用子查询来获取特定列的数据，例如，通过`a.empno`获取地址信息，可以使用嵌套的子查询。这种方式在某些情况下比直接联接查询更为高效，特别是在表间关系复杂时。 总之，SQL语句优化是...

    sql server 语句优化

    3. **避免复杂的查询**：简化查询逻辑，避免使用过多的子查询或联接操作。 4. **充分利用内存资源**：适当增加内存配置，让更多的数据缓存在内存中。 5. **提高查询执行速度**：通过对查询计划进行优化，选择更高效...

    sql语句万能生成器，sql语句，sql语句生成

    这种工具通常包含各种功能，如：根据数据库结构自动生成SELECT、INSERT、UPDATE、DELETE语句，支持JOIN操作，生成复杂的子查询，以及处理分组、排序、条件过滤等。 使用SQL语句生成器，你可以： 1. **快速创建查询...

    sql语句优化之降龙十八掌

    在数据库管理中，SQL语句优化是提升系统性能的关键步骤，尤其对于大型系统而言，一个高效的SQL查询可以显著减少响应时间，改善用户体验。本文将详细阐述"sql语句优化之降龙十八掌"，逐一解析这十八个优化技巧，帮助...

    SQL查询思路优化与语句优化.pdf

    本文还讨论了SQL查询思路优化的方法，包括使用EXISTS和IN代替子查询、使用索引、避免使用SELECT \*、使用合适的连接方式和避免使用OR条件等。这些方法可以提高查询速度，简化管理和提高数据库性能。 关键词：SQL...

    最新整理的常用sql语句及优化大全

    6. **子查询**：在主查询内部嵌套一个查询，如`SELECT column FROM table WHERE column IN (SELECT column FROM another_table)`。 二、SQL优化 1. **索引优化**：为经常查询的列创建索引可以显著提高查询速度。...

    数据库设计（包括select语句、子查询、语句嵌套）

    在本话题中，我们将探讨数据库设计的基础，特别关注SELECT语句、子查询以及语句嵌套这三个核心概念。 首先，数据库设计不仅仅是创建表格，更重要的是理解和分析业务需求，以构建一个能够支持高效数据存储、检索和...

    Sql语句优化（很好的资料）

    SQL语句优化是提高软件系统效率的关键技术，尤其是在大数据量的场景下，高效的SQL能够显著提升数据库查询速度，降低系统资源消耗。以下是一些关于SQL优化的重要知识点： 1. **IN操作符**： - 使用IN操作符虽然使得...

    SQL语句优化，语法优化

    SQL语句优化是数据库性能提升的关键环节，尤其是在大数据量的环境下。优化SQL语句能够显著提高查询速度，减少服务器负载，提升系统整体效率。以下是一些关于SQL语句优化的重要知识点： 1. **选择最有效的表名顺序**...

    sql语句优化技术分析

    SQL语句优化是数据库管理中的核心技能之一，它关乎到系统的性能、响应时间和资源利用率。在处理大量数据时，有效的SQL优化策略能显著提升数据库应用的效率。以下是对SQL语句优化技术的详细分析： 一、理解执行计划 ...

    通过分析SQL语句的执行计划优化SQL(总结)

    子查询可能会导致额外的扫描和临时表创建，优化子查询可能包括转换为连接操作或者使用存在性谓词。 8. **并行执行**： 对于大型数据集，数据库系统可能支持并行执行计划，通过多个处理器同时处理任务来加速查询。...

    一般SQL语句优化整理

    ### 一般SQL语句优化整理 #### 概述 在数据库操作中，SQL查询语句是数据检索的核心，其性能直接影响到应用系统的响应时间和资源消耗。对于一般的SQL语句优化，不仅能够提升查询效率，还能改善用户体验。本文将对...

    Oracle——sql语句优化

    本文将深入探讨几种常见的SQL操作符及其优化方法，包括IN、NOT IN、、IS NULL、>、<、LIKE以及UNION等，旨在帮助数据库管理员和开发人员构建更为高效、响应迅速的应用系统。 #### IN操作符：灵活性与性能的平衡 IN...

    Oracle Sql语句转换成Mysql Sql语句

    3. **连接查询**：Oracle的JOIN语法允许在ON条件中使用子查询，而在MySQL中，子查询通常需要移到FROM或WHERE子句中。 4. **游标**：Oracle SQL支持游标，MySQL则不直接支持，需要使用存储过程或临时表来实现类似...

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