ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Semi join 与anti join

Semi join 与anti join

原创 Linux操作系统 作者:lsq_008 时间:2011-04-19 20:25:06 0 删除 编辑
 

1.semi join

 

Oracle在处理existsin的时候,会使用semi join的连接方式:

 

sys@EBANK>select object_name,object_type from test where object_type in(select object_type from dept where deptno>1000) ;

 

9544 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 272673514

 

-----------------------------------------------------------------

| Id  | Operation            | Name     | Rows  | Bytes | Cost  |

-----------------------------------------------------------------

|   0 | SELECT STATEMENT     |          |  9554 |   335K|    31 |

|*  1 |  HASH JOIN RIGHT SEMI|          |  9554 |   335K|    31 |

|   2 |   VIEW               | VW_NSO_1 |  8554 | 94094 |     6 |

|*  3 |    TABLE ACCESS FULL | DEPT     |  8554 |   200K|     6 |

|   4 |   TABLE ACCESS FULL  | TEST     |  9554 |   233K|    22 |

-----------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("OBJECT_TYPE"="$nso_col_1")

   3 - filter("DEPTNO">1000)

 

Note

-----

   - cpu costing is off (consider enabling it)

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

         16  recursive calls

          0  db block gets

        943  consistent gets

          0  physical reads

          0  redo size

     319908  bytes sent via SQL*Net to client

       7376  bytes received via SQL*Net from client

        638  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

       9544  rows processed

 

 

sys@EBANK>select o.object_name,o.object_type from test o where exists(select 1 from dept d where o.object_type=d.object_type and d.deptno>1000);

 

9544 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1102587590

 

----------------------------------------------------------------

| Id  | Operation            | Name    | Rows  | Bytes | Cost  |

----------------------------------------------------------------

|   0 | SELECT STATEMENT     |         |  9554 |   335K|    31 |

|*  1 |  HASH JOIN RIGHT SEMI|         |  9554 |   335K|    31 |

|   2 |   VIEW               | VW_SQ_1 |  8554 | 94094 |     6 |

|*  3 |    TABLE ACCESS FULL | DEPT    |  8554 |   200K|     6 |

|   4 |   TABLE ACCESS FULL  | TEST    |  9554 |   233K|    22 |

----------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("O"."OBJECT_TYPE"="OBJECT_TYPE")

   3 - filter("D"."DEPTNO">1000)

 

Note

-----

   - cpu costing is off (consider enabling it)

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

         14  recursive calls

          0  db block gets

        911  consistent gets

          0  physical reads

          0  redo size

     319908  bytes sent via SQL*Net to client

       7376  bytes received via SQL*Net from client

        638  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       9544  rows processed

 

  1. Anti join

 

当遇到not existsnot in的时候,oracle会使用anti join的连接方式:

 

sys@EBANK>select o.object_name,o.object_type from test o where not  exists(select 1 from dept d where o.object_type=d.object_type and d.deptno>1000);

 

10 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2621105150

 

-------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost  |

-------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |     1 |    49 |    33 |

|*  1 |  HASH JOIN RIGHT ANTI|      |     1 |    49 |    33 |

|*  2 |   TABLE ACCESS FULL  | DEPT |  8554 |   200K|     6 |

|   3 |   TABLE ACCESS FULL  | TEST |  9554 |   233K|    22 |

-------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("O"."OBJECT_TYPE"="D"."OBJECT_TYPE")

   2 - filter("D"."DEPTNO">1000)

 

Note

-----

   - cpu costing is off (consider enabling it)

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

          4  recursive calls

          0  db block gets

        188  consistent gets

          0  physical reads

          0  redo size

        662  bytes sent via SQL*Net to client

        380  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         10  rows processed

 

但在处理not in的时候,如果连接字段可以为null,将无法使用anti join,而只能使用filter操作:

 

sys@EBANK>select o.object_name,o.object_type from test o where object_type not in(select object_type from dept where deptno>1000);

 

10 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2303781323

 

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |  9273 |   226K|    90 |

|*  1 |  FILTER            |      |       |       |       |

|   2 |   TABLE ACCESS FULL| TEST |  9554 |   233K|    22 |

|*  3 |   TABLE ACCESS FULL| DEPT |  8126 |   190K|     2 |

-----------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DEPT" "DEPT" WHERE

              "DEPTNO">1000 AND LNNVL("OBJECT_TYPE"<>:B1)))

   3 - filter("DEPTNO">1000 AND LNNVL("OBJECT_TYPE"<>:B1))

 

Note

-----

   - cpu costing is off (consider enabling it)

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

        120  recursive calls

          0  db block gets

        811  consistent gets

          0  physical reads

          0  redo size

        662  bytes sent via SQL*Net to client

        380  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

         10  rows processed

 

object_type字段添加not null的约束后,执行计划由filter改为了anti  join

 

sys@EBANK>alter table test modify (object_type VARCHAR2(19) not null);

 

Table altered.

 

sys@EBANK>alter table dept modify (object_type VARCHAR2(19) not null);

 

Table altered.

 

sys@EBANK>set autot traceonly

sys@EBANK>select o.object_name,o.object_type from test o where object_type not in(select object_type from dept where deptno>1000);

 

10 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2621105150

 

-------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost  |

-------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |     1 |    49 |    33 |

|*  1 |  HASH JOIN RIGHT ANTI|      |     1 |    49 |    33 |

|*  2 |   TABLE ACCESS FULL  | DEPT |  8554 |   200K|     6 |

|   3 |   TABLE ACCESS FULL  | TEST |  9554 |   233K|    22 |

-------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("OBJECT_TYPE"="OBJECT_TYPE")

   2 - filter("DEPTNO">1000)

 

Note

-----

   - cpu costing is off (consider enabling it)

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

        232  recursive calls

          0  db block gets

        209  consistent gets

          0  physical reads

          0  redo size

        662  bytes sent via SQL*Net to client

        380  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

         10  rows processed

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-692908/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1254610