FILTER JOIN

n-lauren發表於2014-09-29
Filter

在oracle中對於二個表JOIN的時候我們經常用的是HASH JOIN,NESTED LOOP JOIN,SORT MERGE JOIN,這裡簡單的介紹一下不常用的FILTER JOIN.

對於FILTER操作其實就是我們比較熟悉的NESTED LOOP操作,只是相對來說優化了一下演算法,ORACLE會把每次JOIN的結果放在一個HASH表裡,如果後面的迴圈裡碰到相同的條件的話就直接到HASH表裡去,而不用去全表掃描被驅動表了.

換句話說FILTER的操作的效率取決於驅動表連線欄位值的分佈,值越少效率越高,看一下對於二個100行記錄表的操作.

SQL> create table t01
  2  as select * from dba_objects where rownum<=100;
 
Table created.
 
SQL> create table t02
  2  as select * from t01;
 
Table created.
 
SQL> select *
  2    from t01
  3   where exists (select /*+ no_unnest */ 1
  4            from t02
  5           where t01.object_id = t02.object_id);
 
100 rows selected.
 
Execution Plan
----------------------------------------------------------
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
              "T02"."OBJECT_ID"=:B1))
   3 - filter("T02"."OBJECT_ID"=:B1)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        323  consistent gets
          0  physical reads
 
SQL> update t01 set object_id=2; 
 
100 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> set autot trace
SQL> select *
  2    from t01
  3   where exists (select /*+ no_unnest */ 1
  4            from t02
  5           where t01.object_id = t02.object_id);
 
100 rows selected.
 
Execution Plan
----------------------------------------------------------
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
              "T02"."OBJECT_ID"=:B1))
   3 - filter("T02"."OBJECT_ID"=:B1)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       5289  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22990797/viewspace-1285384/，如需轉載，請註明出處，否則將追究法律責任。

相關文章