玉面飞龙的BLOG

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.