Oracle10g在distinct操作时作了算法改进，使用Hash Unique 代理了以前的Sort Unique.该行为由隐藏参数”_gby_hash_aggregation_enabled”决定，optimizer_features_enable设置为10.2.0.1时默认为TRUE.

HASH UNIQUE 的CPU COST应该比SORT UNIQUE要低，同理常用HASH JOIN而少用SORT MERGE JOIN。


SQL>  create table t as select * from DBA_users;
Table created.

SQL> set autotrace on
SQL> select distinct password from t;
———————————–
| Id  | Operation          | Name |
———————————–
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT UNIQUE       |      |
|   2 |   TABLE ACCESS FULL| T    |
———————————–
Note
—–
- rule based optimizer used (consider using cbo)
Statistics
———————————————————-
1  recursive calls
0  db block gets
3  consistent gets
1  physical reads
0  redo size
752  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
9  rows processed

RBO模式下，仍然要做SORT,使用的是 SORT UNIQUE

SQL> show parameters opt
NAME                                 TYPE        VALUE
———————————— ———– ——————————
optimizer_features_enable            string      10.2.0.1
optimizer_mode                       string      RULE

SQL> alter session set optimizer_mode = choose;
Session altered.

SQL> analyze table t compute statistics;
Table analyzed.

SQL> select distinct password from t;

Execution Plan
———————————————————-
Plan hash value: 1901613472
—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |     9 |   144 |     3  (34)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |     9 |   144 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     9 |   144 |     2   (0)| 00:00:01 |
—————————————————————————
Statistics
———————————————————-
1  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
752  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
9  rows processed

HASH UNIQUE避免了排序，在数据量很大的时候应该能够看到较低的%CPU COST

SQL>  ALTER SESSION SET “_gby_hash_aggregation_enabled” = FALSE;
SQL>  select distinct password from t;
Execution Plan
———————————————————-
Plan hash value: 965418380
—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |     9 |   144 |     3  (34)| 00:00:01 |
|   1 |  SORT UNIQUE       |      |     9 |   144 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     9 |   144 |     2   (0)| 00:00:01 |
—————————————————————————
Statistics
———————————————————-
1  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
752  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
9  rows processed

_gby_hash_aggregation_enabled决定默认使用哪种方式执行DISTINCT.

_gby_hash_aggregation_enabled=false
停用Oracle HASH排序功能， group by 也会受影响。
前一阶段我就遇到过这个问题。对一个表的分区两千多万行数据进行汇总时，由于BUg报错
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], [] with Parameters:  p_Settlement_Date->20101024

文档ORA-600 [32695] [ID 468197.1]

对应Bug

Bug# 6471770   See Note:6471770.8
      ora-32690/OERI [32695] [hash aggregation can't be done] from Hash GROUP BY
      Fixed: 10.2.0.5, 11.1.0.7, 11.2, 10.2.0.3.P23, 10.2.0.4.P05

  Bug# 5893340   See Note:5893340.8
      OERI [32695] [hash aggregation can't be done] from hash aggregation
      Fixed: 10.2.0.4, 11.1.0.6, 10.2.0.3.P15

xuexilexuexile

   对于一个新手看不懂啊 。   后面要好好学习了。