在oracle中对于二个表JOIN的时候我们经常用的是HASH JOIN,NESTED LOOP JOIN,SORT MERGE JOIN,这里简单的介绍一下不常用的FILTER JOIN.
对于FILTER操作其实就是我们比较熟悉的NESTED LOOP操作,只是相对来说优化了一下算法,ORACLE会把每次JOIN的结果放在一个HASH表里,如果后面的循环里碰到相同的条件的话就直接到HASH表里去,而不用去全表扫描被驱动表了.
换句话说FILTER的操作的效率取决于驱动表连接字段值的分布,值越少效率越高,看一下对于二个100行记录表的操作.
|
2 as select * from dba_objects where rownum<=100;
|
|
3 where exists (select /*+ no_unnest */ 1
|
|
5 where t01.object_id = t02.object_id);
|
|
----------------------------------------------------------
|
|
Plan hash value: 3813813230
|
|
---------------------------------------------------------------------------
|
|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
|
|
---------------------------------------------------------------------------
|
|
| 0 | SELECT STATEMENT | | 32 | 6624 | 12 (0)| 00:00:01 |
|
|
|* 1 | FILTER | | | | | |
|
|
| 2 | TABLE ACCESS FULL| T01 | 100 | 20700 | 3 (0)| 00:00:01 |
|
|
|* 3 | TABLE ACCESS FULL| T02 | 1 | 13 | 3 (0)| 00:00:01 |
|
|
---------------------------------------------------------------------------
|
|
Predicate Information (identified by operation id):
|
|
---------------------------------------------------
|
|
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T02" "T02" WHERE
|
|
3 - filter("T02"."OBJECT_ID"=:B1)
|
|
- dynamic sampling used for this statement (level=2)
|
|
----------------------------------------------------------
|
|
SQL> update t01 set object_id=2;
|
|
3 where exists (select /*+ no_unnest */ 1
|
|
5 where t01.object_id = t02.object_id);
|
|
----------------------------------------------------------
|
|
Plan hash value: 3813813230
|
|
---------------------------------------------------------------------------
|
|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
|
|
---------------------------------------------------------------------------
|
|
| 0 | SELECT STATEMENT | | 32 | 6624 | 12 (0)| 00:00:01 |
|
|
|* 1 | FILTER | | | | | |
|
|
| 2 | TABLE ACCESS FULL| T01 | 100 | 20700 | 3 (0)| 00:00:01 |
|
|
|* 3 | TABLE ACCESS FULL| T02 | 1 | 13 | 3 (0)| 00:00:01 |
|
|
---------------------------------------------------------------------------
|
|
Predicate Information (identified by operation id):
|
|
---------------------------------------------------
|
|
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T02" "T02" WHERE
|
|
3 - filter("T02"."OBJECT_ID"=:B1)
|
|
- dynamic sampling used for this statement (level=2)
|
|
----------------------------------------------------------
|
|
5289 bytes sent via SQL*Net to client
|
|
589 bytes received via SQL*Net from client
|
|
8 SQL*Net roundtrips to/from client
|