小議分析函式中排序對結果的影響（一）

分析函式中經常會包括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次記憶體中的排序。