學如逆水行舟

oracle 9i 升級 10g 再一問題

針對 group by 語法 9i 與 10g的不同，如下官方說明:

The GROUP BY clause still performs sort operations but in version 10G,

new internal Sort algorithms have been introduced which do not guarantee

the order of data retrieval and may change the order for returned rows.

9i 會進行 sort group by

10g 會進行 hash group by

所以 9i 在作 group by 時會有排序.

Note: 295819.1

官方例子:

10g Example:

SQL> alter session set optimizer_features_enable="10.2.0.1";

SQL> SELECT TABLESPACE_NAME ,CLUSTER_NAME,COUNT(*)

FROM DBA_TABLES GROUP BY TABLESPACE_NAME ,CLUSTER_NAME;

9i Example:

SQL> alter session set optimizer_features_enable="9.2.0";

SQL> SELECT TABLESPACE_NAME ,CLUSTER_NAME,COUNT(*)

FROM DBA_TABLES GROUP BY TABLESPACE_NAME ,CLUSTER_NAME;