关于hash unique算法
最近查看执行计划时注意到和DISTINCT操作有关的10.2.0以后有的新的算法，
[@more@]hash unique和hash group by，10.1以前是使用sort unique和sort group by，简单总结一下，以后有更多的认识再回头来丰富我的理解，[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 17 12:43:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect yigao_bs_test/yigao_bs_test
Connected.
SQL> alter session set "_gby_hash_aggregation_enabled"=false ;
Session altered.
SQL> select cycle_id,count(publisher_id) from ad_zone_cycle_effect_20080701
2 group by cycle_id ;
60 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2508326428
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 1 | 25 |
3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 25 |
3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| AD_ZONE_CYCLE_EFFECT_20080701 | 1 | 25 |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
189 consistent gets
0 physical reads
0 redo size
1965 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
60 rows processed
SQL> alter session set "_gby_hash_aggregation_enabled"=true ;
Session altered.
SQL> select cycle_id,count(publisher_id) from ad_zone_cycle_effect_20080701
2 group by cycle_id ;
60 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1278382840
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 1 | 25 |
3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 25 |
3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| AD_ZONE_CYCLE_EFFECT_20080701 | 1 | 25 |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
189 consistent gets
0 physical reads
0 redo size
1965 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
60 rows processed
使用上面方法可以看到hash新算法中减少了排序操作，可想而知，数据量超大，磁盘排序必不可少的情况下新的算法的效果还是比较显著的，
而新的算法中采用了HASH后，可能会带来的HASH值碰撞问题在xzh这篇文章中有描述，http://xzh2000.itpub.net/post/96/228455，
关于HASH算法可能会产生碰撞问题，却是头一次听说，只知道ORACLE采用的HASH算法的复杂性通常能够保证值的唯一性，
留待以后做更深入的总结。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/768134/viewspace-1007604/ ，如需转载，请注明出处，否则将追究法律责任。
