filter操作的hash 碰撞

create table emp(
  dept_no    not null,
  sal,
  emp_no    not null,
  padding,
  constraint e_pk primary key(emp_no)
)
as
with generator as (
  select  --+ materialize
    rownum     id
  from  all_objects
  where  rownum <= 1000
)
select
  /*+ ordered use_nl(v2) */
  mod(rownum,6),
  rownum,
  rownum,
  rpad('x',60)
from
  generator  v1,
  generator  v2
where
  rownum <= 20000
;
begin
 dbms_stats.gather_table_stats(
  ownname   => user,
  tabname   => 'EMP',
  cascade   => true,
  estimate_percent => null,
  method_opt  =>'for all columns size 1'
 );
end;
/

select
/*+ no_merge(iv) */
 count(*)
  from (select outer.*
          from emp
         outer where outer.sal >
                     (select /*+ no_unnest */
                       avg(inner.sal)
                        from emp
                       inner where inner.dept_no = outer.dept_no)) iv;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=22022 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=22022 Card=1000)
   3    2       FILTER
   4    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=22 Card=1000 Bytes=8000)
   5    3         SORT (AGGREGATE)
   6    5           TABLE ACCESS (FULL) OF 'EMP' (Cost=22 Card=3333 Bytes=26664)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1547  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

update emp set dept_no = 67 where rownum = 1;
commit;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     738140  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

做第二個查詢前，更新了表裡的第一條記錄，把DEP_NO更新成了67.

效能出現了天壤之別。

因為在9I裡0和67會出現HASH碰撞。67因為在表裡的最前面，會首先進入HASH 表。

但是由於表裡等於0的記錄比較多，那麼這些記錄都需要去全表掃描EMP表裡，因為HASH 表裡不能儲存HASH 為0的部門號了

 但是如果表中的DEPT_NO是有序的，那麼無論如何，就不會出現上面的情況，在UPDATE後，效能會保持不變。

這是由於FILTER所具有的特性決定的，在取外表的新記錄的時候與上次的保留結果做比較，如果一樣就不需要去過濾子查詢，如果不一樣那就要去HASH TABLE裡去搜尋是否之前已經有保留的相關記錄。