Oracle 10G Group by 等查詢語句結果排列發生變化.

Oracle 10G Group by, distinct 等查詢語句結果排列發生變化.  

摘錄: Oracle Forums

1.  DISTINCT  and GROUP BY are not guaranteed to sort the returned rows, There is one and only one way to order your result set: ORDER BY .
2. Oracle 9i version the sorting was done by database itself, but in 10g they made it optional... becoz of ur result take time..
    but if u want to enable the sorting with distinct u have to set the parameter        _gby_hash_aggregation_enabled to false...

In 9i the default sorting mechanism was unique in 10g its hash. so u have to set it false..

like

Consider the statement

SELECT DISTINCT c1 FROM t1;

In Oracle 9.2 this statement generates the following execution plan

0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 TABLE ACCESS (FULL) OF 'T1'

In Oracle 10.2 this statement generates the following execution plan

0 SELECT STATEMENT Optimizer=CHOOSE
1 0 HASH (UNIQUE)
2 1 TABLE ACCESS (FULL) OF 'T1'

However, if the "_gby_hash_aggregation_enabled" parameter is set to FALSE (default TRUE) as follows

ALTER SESSION SET "_gby_hash_aggregation_enabled" = FALSE;

then the following execution plan is generated:

0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 TABLE ACCESS (FULL) OF 'T1'