ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle分页查询格式(十一)

Oracle分页查询格式(十一)

原创 Linux操作系统 作者:yangtingkun 时间:2009-06-05 22:51:36 0 删除 编辑

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

这篇介绍分析函数用于分页。

Oracle分页查询格式(一):http://yangtingkun.itpub.net/post/468/100278

Oracle分页查询格式(二):http://yangtingkun.itpub.net/post/468/101703

Oracle分页查询格式(三):http://yangtingkun.itpub.net/post/468/104595

Oracle分页查询格式(四):http://yangtingkun.itpub.net/post/468/104867

Oracle分页查询格式(五):http://yangtingkun.itpub.net/post/468/107934

Oracle分页查询格式(六):http://yangtingkun.itpub.net/post/468/108677

Oracle分页查询格式(七):http://yangtingkun.itpub.net/post/468/109834

Oracle分页查询格式(八):http://yangtingkun.itpub.net/post/468/224557

Oracle分页查询格式(九):http://yangtingkun.itpub.net/post/468/224409

Oracle分页查询格式(十):http://yangtingkun.itpub.net/post/468/224823

 

 

Oracle8i推出了分析函数,9i中分析函数进一步发展,而且已经很稳定了。

利用分析函数的功能,一样可以实现分页的功能。

首先还是构造一张大表,作为分页查询的测试表:

SQL> CREATE TABLE T AS     
  2  SELECT /*+ NO_MERGE(A) NO_MERGE(B) */ *
  3  FROM DBA_SEQUENCES A, DBA_OBJECTS B;

表已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   4584838

SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
  5     FROM
  6     (
  7             SELECT OBJECT_ID, OBJECT_NAME FROM T
  8             ORDER BY OBJECT_NAME
  9     )
 10     WHERE ROWNUM <= 20
 11  )
 12  WHERE RN >= 11;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant

已选择10行。

已用时间:  00: 00: 02.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=20 Bytes=1840)
   1    0   VIEW (Cost=34093 Card=20 Bytes=1840)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=34093 Card=4584838 Bytes=362202202)
   4    3         SORT (ORDER BY STOPKEY) (Cost=34093 Card=4584838 Bytes=132960302)
   5    4           TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      96666  consistent gets
      56154  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant

已选择10行。

已用时间:  00: 00: 02.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=34093 Card=4584838 Bytes=421805096)
   2    1     WINDOW (SORT PUSHED RANK) (Cost=34093 Card=4584838 Bytes=132960302)
   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      96666  consistent gets
      56165  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

仔细观察标准分页SQL和利用分析函数的分页SQL,这里面有不少有意思的差别。

首先二者得到的结果是不同的。不过这其实并没有关系,因为这本身就是两个不同的SQL,而且二者的结果都是符合查询条件的。导致这个现象的原因有两个,一个是两个SQL使用的排序算法不同,另一个是排序字段不唯一。解决这个问题其实也很简单,只需要保证排序结果唯一就可以了。

如果从性能上讲,二者没有本质的差别,由于ORDER BY的列上没有索引,因此两个SQL都必须对表进行FULL TABLE SCAN。关键在于这两个SQL在能否体现出分页的特点——STOP KEY。由于用户当前执行的查询可能只是返回前100条记录,那么分页SQL就没有必要对所有的数据进行完全的排序,只需要找到最小或最大的100条记录,就可以返回结果了。在两个SQL中,都能实现这个功能,其中标准分页采用的是:SORT (ORDER BY STOPKEY);而分析函数使用的是:WINDOW (SORT PUSHED RANK)。只要具有将STOP KEY推入到排序操作内的功能,就基本上满足分页条件。

SQL的结构上,标准分页需要3层嵌套,而分析函数只需要2层。但是并不意味分析函数减少了一层嵌套,效率就一定高于标准分页,事实上,多次测试显示,标准分页似乎还要略快一点。与错误的分页写法相比,这两个SQL的效率都是足够高的,二者之前的差别几乎可以忽略:

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
  5     FROM
  6     (
  7             SELECT OBJECT_ID, OBJECT_NAME FROM T
  8             ORDER BY OBJECT_NAME
  9     )
 10  )
 11  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17870 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant

已选择10行。

已用时间:  00: 00: 13.18

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=34093 Card=4584838 Bytes=421805096)
   2    1     COUNT
   3    2       VIEW (Cost=34093 Card=4584838 Bytes=362202202)
   4    3         SORT (ORDER BY) (Cost=34093 Card=4584838 Bytes=132960302)
   5    4           TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

 


Statistics
----------------------------------------------------------
          0  recursive calls
         50  db block gets
      96666  consistent gets
      96806  physical reads
          0  redo size
        553  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         10  rows processed

上面就是采用了错误的分页写法,使得Oracle排序了所有的数据,所需的时间是正确分页写法的6倍以上。

最后将分页设置到最后的部分,检查两个SQL的性能:

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
  5     FROM
  6     (
  7             SELECT OBJECT_ID, OBJECT_NAME FROM T
  8             ORDER BY OBJECT_NAME
  9     )
 10     WHERE ROWNUM <= 4584820
 11  )
 12  WHERE RN >= 4584811;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T

已选择10行。

已用时间:  00: 00: 12.92

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584820 Bytes=421803440)
   1    0   VIEW (Cost=34093 Card=4584820 Bytes=421803440)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=34093 Card=4584838 Bytes=362202202)
   4    3         SORT (ORDER BY STOPKEY) (Cost=34093 Card=4584838 Bytes=132960302)
   5    4           TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

 


Statistics
----------------------------------------------------------
          0  recursive calls
         50  db block gets
      96666  consistent gets
      96810  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         10  rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 4584811 AND 4584820;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T

已选择10行。

已用时间:  00: 00: 18.78

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=34093 Card=4584838 Bytes=421805096)
   2    1     WINDOW (SORT PUSHED RANK) (Cost=34093 Card=4584838 Bytes=132960302)
   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

 


Statistics
----------------------------------------------------------
          0  recursive calls
         48  db block gets
      96666  consistent gets
      76497  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         10  rows processed

由于表中一个只有4584838条记录,因此这个分页基本上是最后一两页了,这可以说是一个极端的例子了,比较二者的效率发现,标准分页效率这次要比分析函数高将近50%。虽然例子比较极端,不过也可以说明一些问题。首先,通过ORDER BYROWNUM方式的排序算法,应该和分析函数的不同。其次,分析函数的功能很强大,ORDER BY只是其中一个功能,分析函数还能完成分区和窗口操作等更加复杂的操作,因此效率比单纯的排序要低也无可厚非。当然,为了和前面的测试保持版本的一致性,测试在920上进行,不排除10g11g对分析函数的分页做了进一步的优化。

最后还需要提一句,采用分析函数的方法,不能没有ORDER BY语句,而标准分页方式可以。当然没有ORDER BY能不能算一个真正的分页,就是见仁见智的事情了。

 

 

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

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

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10441112