ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 小议分析函数中排序对结果的影响(一)

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

原创 Linux操作系统 作者:yangtingkun 时间:2009-01-10 23:38:16 0 删除 编辑

分析函数中经常会包括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根据记录读取的顺序返回记录,得到的结果是132

下面加上带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次内存中的排序。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-536320/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1954
  • 访问量
    10632945