ITPub博客

首页 > Linux操作系统 > Linux操作系统 > join-semi and join-anti

join-semi and join-anti

原创 Linux操作系统 作者:shuangoracle 时间:2012-05-27 22:15:47 0 删除 编辑
测试环境:
SQL> select id,name from t1;

        ID NAME
---------- --------------------
         1 A
         2 B
         3
         4 D

SQL> select id,name from t2;

        ID NAME
---------- --------------------
         1 A

SQL> SELECT TABLE_NAME,COLUMN_NAME,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME IN ('T1','T2') AND COLUMN_NAME='ID';

TABLE_NAME                     COLUMN_NAME                    N
------------------------------ ------------------------------ -
T1                             ID                             Y
T2                             ID                             Y

SQL> set linesize 200 pagesize 200
SQL> set autot on exp

--in------HASH JOIN SEMI
SQL> select id,name from t1 where t1.id in (select t2.id from t2);


Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    38 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     1 |    38 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |   100 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."ID")

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


--exists-------HASH JOIN SEMI
SQL> select id,name from t1 where exists (select t2.id from t2 where t2.id=t1.id);


Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    38 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     1 |    38 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |   100 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T2"."ID"="T1"."ID")

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

--not in-----此时t1和t2的字段id默认值都为null
--当处理not in 时,如果连接字段可以为null,则oracle会不选择join anti而选择filter
SQL> select id,name from t1 where t1.id not in  (select t2.id from t2);


Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    25 |     6   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |     4 |   100 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T2" "T2" WHERE
              LNNVL("T2"."ID"<>:B1)))
   3 - filter(LNNVL("T2"."ID"<>:B1))

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

--not exists------HASH JOIN ANTI
SQL> select id,name from t1 where not exists (select t2.id from t2 where t2.id=t1.id);


Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   114 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     3 |   114 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |   100 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T2"."ID"="T1"."ID")

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

--将t1和t2连接字段id修改默认值为not null
SQL> alter table t1 modify id not null;

Table altered.

SQL> alter table t2 modify id not null;

Table altered.

--not in ------HASH JOIN ANTI
SQL> select id,name from t1 where t1.id not in  (select t2.id from t2);


Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   114 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     3 |   114 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |   100 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."ID")

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


由上:
当使用in和exists时,oracle会选择join-semi方式;
当使用not in时,如果连接字段(包括外查询和子查询字段)默认值为not null,则oracle会选择join-anti,否则会选择filter;
而not exists却不受连接字段是否为not null的限制,都会选择join-anti。

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

请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    214432