分析函数中经常会包括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次内存中的排序。