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

 当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> 

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