小议分析函数中排序对结果的影响（一）

分析函数中经常会包括ORDER BY语句，而这个语句会对Oracle的运行结果产生影响。



看一个简单的例子：

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T VALUES (1, 'A');

已创建 1 行。

SQL> INSERT INTO T VALUES (3, 'C');

已创建 1 行。

SQL> INSERT INTO T VALUES (2, 'B');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM T;

ID NAME
---------- ------------------------------
1 A
3 C
2 B

如果不加任何的排序，直接查询T表，Oracle根据记录读取的顺序返回记录，得到的结果是1、3、2。

下面加上带ORDER BY语句的分析函数：

SQL> SELECT ID,
2 NAME,
3 SUM(ID) OVER(ORDER BY ID) SUM1
4 FROM T;

ID NAME SUM1
---------- ------------------------------ ----------
1 A 1
2 B 3
3 C 6

可以看到，加上分析函数之后，由于分析函数中ORDER BY的存在，改变了最终结果中的排列顺序。

下面将ORDER BY排序的字段改为倒排序：

SQL> SELECT ID,
2 NAME,
3 SUM(ID) OVER(ORDER BY ID DESC) SUM1
4 FROM T;

ID NAME SUM1
---------- ------------------------------ ----------
3 C 3
2 B 5
1 A 6

得到的最终结果也是倒序的，那么如果添加的分析函数，没有排序语句呢：

SQL> SELECT ID,
2 NAME,
3 SUM(ID) OVER() SUM1
4 FROM T;

ID NAME SUM1
---------- ------------------------------ ----------
1 A 6
3 C 6
2 B 6

这次得到结果的顺序就和没有添加分析函数时一样，Oracle根据记录的读取顺序返回了最终的结果。

如果分析函数中包括了ORDER BY语句，则分析函数在计算结果的过程中就会对ORDER BY指定列进行排序，否则就无法得到相应的结果，不过这个排序是基于窗口的。

当不指定窗口语句，也没有分区语句，那么参加排序就是所有的记录，因此Oracle根据ORDER BY语句中的顺序返回结果也就不奇怪了。

事实上，这种情况下，如果分析函数中的ORDER BY语句和SQL语句中的ORDER BY语句一致的话，Oracle可以节省一次排序操作。

SQL> SET AUTOT ON STAT
SQL> SELECT ID,
2 NAME,
3 SUM(ID) OVER(ORDER BY ID) SUM1
4 FROM T
5 ORDER BY ID;

ID NAME SUM1
---------- ------------------------------ ----------
1 A 1
2 B 3
3 C 6

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
572 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> SELECT ID,
2 NAME,
3 SUM(ID) OVER(ORDER BY ID DESC) SUM1
4 FROM T
5 ORDER BY ID;

ID NAME SUM1
---------- ------------------------------ ----------
1 A 6
2 B 5
3 C 3

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
572 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed

从这里就可以清晰的看到，第一个SQL中分析函数与SQL的排序一致，整个SQL只执行了1次内存中的排序。

而第二个SQL中由于分析函数与SQL的排序不一致，所以统计信息中可以看到2次内存中的排序。