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里去搜索是否之前已经有保留的相关记录。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-715499/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-715499/