create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1',cascade => true);
exec dbms_stats.gather_table_stats(user,'test2',cascade => true);
unnest我们称为对子查询展开，是别让子查询孤单地嵌套(nest)在里面。所以un_unnest双重否定代表肯定，即让子查询不展开，让它嵌套(nest)在里面。
select test1.object_id from test1 where exists
(select 1 from test2 where test1.object_id=test2.object_id*10);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 328 (3)| 00:00:04 |
|* 1 | HASH JOIN SEMI | | 1 | 10 | 328 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| test1 | 51318 | 250K| 163 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL| test2 | 51319 | 250K| 163 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("test1"."OBJECT_ID"="test2"."OBJECT_ID"*10)
----------------------------------------------------------
1 recursive calls
0 db block gets
1750 consistent gets
0 physical reads
0 redo size
72669 bytes sent via SQL*Net to client
4074 bytes received via SQL*Net from client
336 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5020 rows processed
10046跟踪：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 336 0.07 0.07 0 1750 0 5020
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 338 0.07 0.09 0 1750 0 5020
select test1.object_id from test1 where exists
(select /*+no_unnest*/ 1 from test2 where test1.object_id=test2.object_id*10)
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 81532 (1)| 00:16:19 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| test1 | 51318 | 250K| 163 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| test2 | 2 | 10 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "test2" "test2"
WHERE "test2"."OBJECT_ID"*10=:B1))
3 - filter("test2"."OBJECT_ID"*10=:B1)
----------------------------------------------------------
1 recursive calls
0 db block gets
33058028 consistent gets
0 physical reads
0 redo size
72669 bytes sent via SQL*Net to client
4074 bytes received via SQL*Net from client
336 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5020 rows processed
10046跟踪：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 336 393.10 394.32 0 33058028 0 5020
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 338 393.10 394.32 0 33058028 0 5020
select /*+use_nl(test1 test2)*/ test1.object_id from test1, test2
where test1.object_id = test2.object_id * 10;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51323 | 501K| 8279K (2)| 27:35:54 |
| 1 | NESTED LOOPS | | 51323 | 501K| 8279K (2)| 27:35:54 |
| 2 | TABLE ACCESS FULL| test1 | 51318 | 250K| 163 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| test2 | 1 | 5 | 161 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("test1"."OBJECT_ID"="test2"."OBJECT_ID"*10)
----------------------------------------------------------
1 recursive calls
0 db block gets
36437152 consistent gets
0 physical reads
0 redo size
72669 bytes sent via SQL*Net to client
4074 bytes received via SQL*Net from client
336 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5020 rows processed
10046跟踪：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 336 380.62 381.39 0 36437152 0 5020
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 338 380.62 381.39 0 36437152 0 5020
filter与nested loop相似，但它的独特之处在于会维护一个hash table。
举例，如果test1里取出object_id=1，那么对于test2来说即select 1 from test2 where test2.object_id*10=1,如果条件满足，那么对于子查询，输入输出对，即为(1（test1.object_id）,1（常量）)。它存储在hash table里，并且由于条件满足，test1.object_id=1被放入结果集。然后接着从test1取出object_id=2，如果子查询依旧条件满足，那么子查询产生另一个输入和输出，即(2,1)，被放入hash
table里；并且test1.object_id=2被放入结果集。接着假设test1里有重复的object_id，例如我们第三次从test1取出的object_id=2,那么由于我们对于子查询来说，已经有输入输出对(2,1)在hash table里了，所以就不用去再次全表扫描test2了，ORACLE非常聪明地知道object_id=2是结果集。
filter和neested loop相比,省去了一次全表扫描test2。这个hash table是有大小限制的，当被占满的时候，后续新的test1.object_id的FILTER就类似neested loop了。
由此可见，从buffer gets层面上来看，FILTER是应该优于nested loop的，尤其当外部查询需要传递给子查询的输入（此例中为test1.object_id）的distinct value非常小时，FILTER就会显得更优。
select test1.object_id from test1 where exists
(select /*+no_unnest*/ 1 from test2 where test1.object_type=test2.object_type);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1426 | 19964 | 235 (2)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| test1 | 51318 | 701K| 163 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| test2 | 2 | 18 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "test2" "test2"
WHERE "test2"."OBJECT_TYPE"=:B1))
3 - filter("test2"."OBJECT_TYPE"=:B1)
----------------------------------------------------------
1 recursive calls
0 db block gets
10977 consistent gets
0 physical reads
0 redo size
743550 bytes sent via SQL*Net to client
38031 bytes received via SQL*Net from client
3423 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51318 rows processed
10046跟踪：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3423 0.18 0.19 0 10977 0 51318
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3425 0.18 0.19 0 10977 0 51318
同样是test1和test2的全表扫描后的FILTER操作，却因为传给子查询的输入的distinct value的差别，两者相差的consistent gets却如此巨大，这跟neested loop是完全不一样的。
对于如此的两个全表扫描的结果集，使用hash join是最佳方法。
select test1.object_id from test1 where exists
(select 1 from test2 where test1.object_type=test2.object_type);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41340 | 928K| | 445 (3)| 00:00:06 |
|* 1 | HASH JOIN RIGHT SEMI| | 41340 | 928K| 1056K| 445 (3)| 00:00:06 |
| 2 | TABLE ACCESS FULL | test2 | 51319 | 451K| | 163 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL | test1 | 51318 | 701K| | 163 (2)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("test1"."OBJECT_TYPE"="test2"."OBJECT_TYPE")
----------------------------------------------------------
1 recursive calls
0 db block gets
4796 consistent gets
0 physical reads
0 redo size
743550 bytes sent via SQL*Net to client
38031 bytes received via SQL*Net from client
3423 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51318 rows processed
10046跟踪：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3423 0.21 0.15 0 4796 0 51318
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3425 0.21 0.15 0 4796 0 51318