当前位置：Oracle研究中心 > 开发DBA >
【学习笔记】Oracle 10g中distinct加强以及anti jion semi jion
时间:2016-12-07 20:49 来源:Oracle研究中心 作者:网络 点击: 次
天萃荷净
Oracle研究中心学习笔记：分享一篇关于Oracle 10g中distinct加强以及anti jion semi jion详细笔记。
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 10g中distinct加强以及anti jion,semi jion
SQL> conn roger/roger
Connected.
SQL> CREATE TABLE t1 AS SELECT * FROM dba_objects WHERE object_id < 30001;
TABLE created.
SQL> CREATE TABLE t2 AS SELECT * FROM dba_objects WHERE object_id < 20001;
TABLE created.
SQL> SET LINES 160
SQL> SET pagesize 50
SQL> SET pagesize 50
SQL> analyze TABLE t1 compute statistics;
TABLE analyzed.
SQL> analyze TABLE t2 compute statistics;
TABLE analyzed.
SQL> SET autot traceonly
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE NOT EXISTS
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');
7 ROWS selected.
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4203366459
------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |
| 1 | HASH UNIQUE | | 1 | 8 | 193 (2)| 00:00:03 |
|* 2 | HASH JOIN RIGHT ANTI| | 17022 | 132K| 192 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 3682 | 14728 | 115 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("B"."OWNER"="A"."OWNER")
3 - FILTER("B"."OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
670 consistent gets
0 physical reads
0 redo SIZE
516 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
7 ROWS processed
我们可以看到此时逻辑读之和为671，没有排序消耗。下面修改一个隐含参数继续比较：
SQL> ALTER SESSION SET "_gby_hash_aggregation_enabled"=FALSE;
SESSION altered.
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE NOT EXISTS
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');
7 ROWS selected.
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1130070542
------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |
| 1 | SORT UNIQUE | | 1 | 8 | 193 (2)| 00:00:03 |
|* 2 | HASH JOIN RIGHT ANTI| | 17022 | 132K| 192 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 3682 | 14728 | 115 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("B"."OWNER"="A"."OWNER")
3 - FILTER("B"."OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
670 consistent gets
0 physical reads
0 redo SIZE
516 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
1 sorts (memory)
0 sorts (disk)
7 ROWS processed
我们发现，此时虽然逻辑读是一样的，但是多了1个memory的排序操作，可见10.2里面跟以前版本在distinct 排序操作上有更进一步的优化了。
我们继续来看上面的执行计划，发现是HASH JOIN RIGHT ANTI，也就是anti jion。
我们知道oracle 在处理exist和in时，是使用的semi jion，然而在处理not exists和not in时是用的anti jion，下面我来验证一下：
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE EXISTS
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS')
3 /
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1951262108
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |
| 1 | SORT UNIQUE NOSORT | | 1 | 8 | 193 (2)| 00:00:03 |
|* 2 | HASH JOIN SEMI | | 2432 | 19456 | 192 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL| T2 | 2432 | 9728 | 76 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 3682 | 14728 | 115 (0)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("B"."OWNER"="A"."OWNER")
3 - FILTER("A"."OWNER"='SYS')
4 - FILTER("B"."OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
268 consistent gets
0 physical reads
0 redo SIZE
409 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
1 ROWS processed
可以看到此时是semi jion了。
下面来测试下not in的情况：
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE a.owner NOT IN
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');
7 ROWS selected.
Execution Plan
---------------------------------------------Oracleо-------------
Plan hash VALUE: 2122336124
-----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 32 | 1000 (1)| 00:00:13 |
| 1 | HASH UNIQUE | | 8 | 32 | 1000 (1)| 00:00:13 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| T1 | 460 | 1840 | 115 (0)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER( NOT EXISTS (SELECT 0 FROM "T1" "B" WHERE :B1='SYS' AND
"B"."OWNER"=:B2 AND LNNVL("B"."OWNER":B3)))
4 - FILTER(:B1='SYS')
5 - FILTER("B"."OWNER"=:B1 AND LNNVL("B"."OWNER":B2))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
268 consistent gets
0 physical reads
0 redo SIZE
516 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
7 ROWS processed
可以看到这里非常奇怪，居然走filter了。
SQL> ALTER TABLE t1 MODIFY (owner VARCHAR2(30) NOT NULL);
TABLE altered.
SQL> ALTER TABLE t2 MODIFY (owner VARCHAR2(30) NOT NULL);
TABLE altered.
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE a.owner NOT IN
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');
7 ROWS selected.
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4203366459
------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |
| 1 | HASH UNIQUE | | 1 | 8 | 193 (2)| 00:00:03 |
|* 2 | HASH JOIN RIGHT ANTI| | 17022 | 132K| 192 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 3682 | 14728 | 115 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("B"."OWNER"="A"."OWNER")
3 - FILTER("B"."OWNER"='SYS')
Statistics
----------------------------------------------------------
349 recursive calls
0 db block gets
715 consistent gets
1 physical reads
0 redo SIZE
516 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
8 sorts (memory)
0 sorts (disk)
7 ROWS processed
可以看到，目前走hash jion了,问兔子，他是这样说的：not in如果没有约束或语句避免null问题,内部会使用lnnvl函数,11g有null aware优化，比10g好点，因此not in写法特别注意null,lnnvl是undocument函数.
所以我这里将自动owner修改为not null，下面来看看11g中是不是这样的，我这里以11gR2为例：
——10.2.0.5
SQL> SET autot traceonly EXP
SQL> SELECT DISTINCT a.object_id FROM t2 a WHERE a.object_id NOT IN
2 (SELECT b.object_id FROM t1 b);
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3142026835
------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes |TempSpc| Cost (%CPU)| TIME |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19453 | 77812 | | 1122K (1)| 03:44:29 |
| 1 | HASH UNIQUE | | 19453 | 77812 | 240K| 1122K (1)| 03:44:29 |
|* 2 | FILTER | | | | | | |
| 3 | TABLE ACCESS FULL| T2 | 19454 | 77816 | | 76 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 4 | | 115 (0)| 00:00:02 |
------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER( NOT EXISTS (SELECT 0 FROM "T1" "B" WHERE
LNNVL("B"."OBJECT_ID":B1)))
4 - FILTER(LNNVL("B"."OBJECT_ID":B1))
——-11.2.0.2
SQL> SELECT * FROM v$version WHERE rownum < 2;
BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - Production
SQL>
SQL> SET autot traceonly EXP
SQL> SELECT DISTINCT a.object_id FROM t2 a WHERE a.object_id NOT IN
2 (SELECT b.object_id FROM t1 b);
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1298667172
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 195 | 1560 | 196 (2)| 00:00:03 |
| 1 | HASH UNIQUE | | 195 | 1560 | 196 (2)| 00:00:03 |
|* 2 | HASH JOIN ANTI NA | | 195 | 1560 | 195 (1)| 00:00:03 |
| 3 | TABLE ACCESS FULL| T2 | 19490 | 77960 | 77 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 29490 | 115K| 117 (0)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
可以看到11g中，即使object_id字段默认允许为空，sql仍然可以走hash anti jion,而不是去像10g一样走filter。 这里的NA表示null aware.
Oracle研究中心
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 10g中distinct加强以及anti jion,semi jion
SQL> conn roger/roger
Connected.
SQL> CREATE TABLE t1 AS SELECT * FROM dba_objects WHERE object_id < 30001;
TABLE created.
SQL> CREATE TABLE t2 AS SELECT * FROM dba_objects WHERE object_id < 20001;
TABLE created.
SQL> SET LINES 160
SQL> SET pagesize 50
SQL> SET pagesize 50
SQL> analyze TABLE t1 compute statistics;
TABLE analyzed.
SQL> analyze TABLE t2 compute statistics;
TABLE analyzed.
SQL> SET autot traceonly
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE NOT EXISTS
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');
7 ROWS selected.
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4203366459
------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |
| 1 | HASH UNIQUE | | 1 | 8 | 193 (2)| 00:00:03 |
|* 2 | HASH JOIN RIGHT ANTI| | 17022 | 132K| 192 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 3682 | 14728 | 115 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("B"."OWNER"="A"."OWNER")
3 - FILTER("B"."OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
670 consistent gets
0 physical reads
0 redo SIZE
516 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
7 ROWS processed
我们可以看到此时逻辑读之和为671，没有排序消耗。下面修改一个隐含参数继续比较：
SQL> ALTER SESSION SET "_gby_hash_aggregation_enabled"=FALSE;
SESSION altered.
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE NOT EXISTS
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');
7 ROWS selected.
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1130070542
------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |
| 1 | SORT UNIQUE | | 1 | 8 | 193 (2)| 00:00:03 |
|* 2 | HASH JOIN RIGHT ANTI| | 17022 | 132K| 192 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 3682 | 14728 | 115 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("B"."OWNER"="A"."OWNER")
3 - FILTER("B"."OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
670 consistent gets
0 physical reads
0 redo SIZE
516 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
1 sorts (memory)
0 sorts (disk)
7 ROWS processed
我们发现，此时虽然逻辑读是一样的，但是多了1个memory的排序操作，可见10.2里面跟以前版本在distinct 排序操作上有更进一步的优化了。
我们继续来看上面的执行计划，发现是HASH JOIN RIGHT ANTI，也就是anti jion。
我们知道oracle 在处理exist和in时，是使用的semi jion，然而在处理not exists和not in时是用的anti jion，下面我来验证一下：
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE EXISTS
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS')
3 /
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1951262108
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |
| 1 | SORT UNIQUE NOSORT | | 1 | 8 | 193 (2)| 00:00:03 |
|* 2 | HASH JOIN SEMI | | 2432 | 19456 | 192 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL| T2 | 2432 | 9728 | 76 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 3682 | 14728 | 115 (0)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("B"."OWNER"="A"."OWNER")
3 - FILTER("A"."OWNER"='SYS')
4 - FILTER("B"."OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
268 consistent gets
0 physical reads
0 redo SIZE
409 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
1 ROWS processed
可以看到此时是semi jion了。
下面来测试下not in的情况：
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE a.owner NOT IN
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');
7 ROWS selected.
Execution Plan
---------------------------------------------Oracleо-------------
Plan hash VALUE: 2122336124
-----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 32 | 1000 (1)| 00:00:13 |
| 1 | HASH UNIQUE | | 8 | 32 | 1000 (1)| 00:00:13 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| T1 | 460 | 1840 | 115 (0)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER( NOT EXISTS (SELECT 0 FROM "T1" "B" WHERE :B1='SYS' AND
"B"."OWNER"=:B2 AND LNNVL("B"."OWNER":B3)))
4 - FILTER(:B1='SYS')
5 - FILTER("B"."OWNER"=:B1 AND LNNVL("B"."OWNER":B2))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
268 consistent gets
0 physical reads
0 redo SIZE
516 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
0 sorts (memory)
0 sorts (disk)
7 ROWS processed
可以看到这里非常奇怪，居然走filter了。
SQL> ALTER TABLE t1 MODIFY (owner VARCHAR2(30) NOT NULL);
TABLE altered.
SQL> ALTER TABLE t2 MODIFY (owner VARCHAR2(30) NOT NULL);
TABLE altered.
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE a.owner NOT IN
2 (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');
7 ROWS selected.
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4203366459
------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |
| 1 | HASH UNIQUE | | 1 | 8 | 193 (2)| 00:00:03 |
|* 2 | HASH JOIN RIGHT ANTI| | 17022 | 132K| 192 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 3682 | 14728 | 115 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("B"."OWNER"="A"."OWNER")
3 - FILTER("B"."OWNER"='SYS')
Statistics
----------------------------------------------------------
349 recursive calls
0 db block gets
715 consistent gets
1 physical reads
0 redo SIZE
516 bytes sent via SQL*Net TO client
400 bytes received via SQL*Net FROM client
2 SQL*Net roundtrips TO/FROM client
8 sorts (memory)
0 sorts (disk)
7 ROWS processed
可以看到，目前走hash jion了,问兔子，他是这样说的：not in如果没有约束或语句避免null问题,内部会使用lnnvl函数,11g有null aware优化，比10g好点，因此not in写法特别注意null,lnnvl是undocument函数.
所以我这里将自动owner修改为not null，下面来看看11g中是不是这样的，我这里以11gR2为例：
——10.2.0.5
SQL> SET autot traceonly EXP
SQL> SELECT DISTINCT a.object_id FROM t2 a WHERE a.object_id NOT IN
2 (SELECT b.object_id FROM t1 b);
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3142026835
------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes |TempSpc| Cost (%CPU)| TIME |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19453 | 77812 | | 1122K (1)| 03:44:29 |
| 1 | HASH UNIQUE | | 19453 | 77812 | 240K| 1122K (1)| 03:44:29 |
|* 2 | FILTER | | | | | | |
| 3 | TABLE ACCESS FULL| T2 | 19454 | 77816 | | 76 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 4 | | 115 (0)| 00:00:02 |
------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - FILTER( NOT EXISTS (SELECT 0 FROM "T1" "B" WHERE
LNNVL("B"."OBJECT_ID":B1)))
4 - FILTER(LNNVL("B"."OBJECT_ID":B1))
——-11.2.0.2
SQL> SELECT * FROM v$version WHERE rownum < 2;
BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - Production
SQL>
SQL> SET autot traceonly EXP
SQL> SELECT DISTINCT a.object_id FROM t2 a WHERE a.object_id NOT IN
2 (SELECT b.object_id FROM t1 b);
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1298667172
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 195 | 1560 | 196 (2)| 00:00:03 |
| 1 | HASH UNIQUE | | 195 | 1560 | 196 (2)| 00:00:03 |
|* 2 | HASH JOIN ANTI NA | | 195 | 1560 | 195 (1)| 00:00:03 |
| 3 | TABLE ACCESS FULL| T2 | 19490 | 77960 | 77 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 29490 | 115K| 117 (0)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
可以看到11g中，即使object_id字段默认允许为空，sql仍然可以走hash anti jion,而不是去像10g一样走filter。 这里的NA表示null aware.
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 10g中distinct加强以及anti jion semi jion
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/1375.html
