`
lious_xun
  • 浏览: 6068 次
最近访客 更多访客>>
gzb001
社区版块
存档分类
最新评论

Oracle分页查询格式（十一）

 
阅读更多
Oracle分页查询格式（十一） 2009-06-05 22:51:36

分类： Linux

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





Oracle从8i推出了分析函数，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 BY加ROWNUM方式的排序算法，应该和分析函数的不同。其次，分析函数的功能很强大，ORDER BY只是其中一个功能，分析函数还能完成分区和窗口操作等更加复杂的操作，因此效率比单纯的排序要低也无可厚非。当然，为了和前面的测试保持版本的一致性，测试在920上进行，不排除10g或11g对分析函数的分页做了进一步的优化。

最后还需要提一句，采用分析函数的方法，不能没有ORDER BY语句，而标准分页方式可以。当然没有ORDER BY能不能算一个真正的分页，就是见仁见智的事情了。
分享到：
| Activemq安全性配置
评论
发表评论

文章已被作者锁定，不允许评论。

相关推荐

    oracle分页查询sql

    ### Oracle分页查询详解 #### 一、分页查询的重要性 在数据库操作中，分页查询是一项非常重要的技术。尤其当处理大量数据时，一次性获取所有数据不仅会消耗大量的网络带宽，还可能导致前端页面加载缓慢，用户体验...

    oracle分页查询并返回总记录数据存储过程

    ### Oracle 分页查询并返回总记录数据存储过程 在数据库应用开发中，为了提高用户体验以及减少服务器负担，分页查询成为了一种常见的技术手段。Oracle 数据库提供了多种方法来实现分页查询，其中使用存储过程是一种...

    ORACLE分页查询效率

    根据所提供的信息，我们可以提炼出以下关于“ORACLE分页查询效率”的相关知识点： ### 一、Oracle中的分页查询 #### 1.1 分页查询的重要性 - **背景介绍**：在实际应用中，数据库查询结果往往非常庞大，一次性加载...

    如何解决Oracle分页查询中排序与效率问题

    Oracle 分页查询优化与排序解决方案 本文主要解决 Oracle 分页查询中排序与效率问题，通过实践和分析，提供了两种解决方案，并对比了两种方法的优缺点。 知识点 1: Oracle 分页查询的基本概念 Oracle 分页查询是...

    oracle分页查询

    Oracle 分页查询详解 Oracle 分页查询是指从大型数据表中提取指定范围的记录，以便提高查询效率和减少数据传输量。常用的 Oracle 分页查询方法有三种：使用 ROWNUM、使用 ROW_NUMBER() 和使用子查询。 第一种方法...

    msql和oracle分页查询语句笔记

    msql和oracle分页查询语句笔记

    解决Oracle分页查询中排序与效率问题

    ### 解决Oracle分页查询中排序与效率问题 在Oracle数据库中进行分页查询时，经常会出现性能瓶颈，尤其是在处理大数据量的情况下。本篇文章将详细探讨如何优化Oracle分页查询中的排序与效率问题。 #### 一、理解...

    oracle的分页查询

    Oracle 的分页查询 在 Oracle 中，分页查询是非常常见的需求，但是在使用查询条件时又不能使用大于号（&gt;）。本文将讲解 Oracle 中的分页查询，包括使用 ROWNUM 伪列和 ORDER BY 子句对查询结果进行排序和分页。 一...

    Oracle分页查询（很不错的介绍）

    首先，理解Oracle分页查询的基本结构。通常，Oracle的分页查询采用嵌套查询的方式实现。以下是一个典型的分页查询模板： ```sql SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( SELECT * FROM TABLE_NAME ) A ...

    Oracle的分页查询语句 Oracle分页的存储过程

    首先，Oracle的分页查询语句通常遵循一个标准格式，如下所示： ```sql SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( SELECT * FROM TABLE_NAME ) A WHERE ROWNUM ) WHERE RN &gt;= 21 ``` 在这个例子中，最内层...

    java web与Oracle数据的分页功能

    2. **构造SQL查询语句**：Oracle数据库支持使用`ROWNUM`进行分页查询。基本的SQL模板为： ```sql SELECT * FROM (SELECT t.*, ROWNUM rnum FROM (YOUR_SELECT_QUERY) t WHERE ROWNUM ) WHERE rnum &gt;= :startRow `...

    Oracle数据库中很实用的分页查询代码

    分页查询是Oracle数据库比较重要的一个知识点！希望这个对你有参照作用。

    Oracle分页(limit方式的运用)

    ### Oracle分页(LIMIT方式的运用) 在数据库查询操作中，分页是非常常见的需求之一，尤其是在数据量较大的情况下，为了提高用户体验以及减轻服务器压力，...希望本文能为你理解和掌握Oracle分页查询提供一定的帮助。

    Oracle的分页查询语句.docx

    "Oracle分页查询语句详解" Oracle 的分页查询语句是数据库查询中的一种优化手段，用于控制查询结果集的大小，以提高查询效率。在 Oracle 中，分页查询语句可以使用 ROWNUM 和子查询来实现。本文将详细介绍 Oracle ...

    Oracle&JSP分页和Oracle分页

    总结来说，Oracle和JSP结合实现分页涉及以下几个步骤：在Oracle中构造分页查询，使用JDBC在Servlet中执行查询，然后在JSP页面中展示数据。理解并熟练掌握这些步骤对于开发高效、用户友好的数据驱动网站至关重要。在...

    JAVA_JDBC面向对象分页(初步设计二之oracle)

    本篇文章将重点讨论如何使用Java JDBC和Oracle数据库实现面向对象的分页查询。 首先，我们需要了解面向对象编程的基本概念。面向对象编程（Object-Oriented Programming, OOP）是一种编程范式，强调用对象来表示...

    Oracle的SQL分页实践

    在Oracle数据库中，SQL分页是一种非常常见的查询技术，它允许我们从大量数据中按需获取一部分结果，而不是一次性加载所有记录。这对于提高用户体验和优化系统性能至关重要，尤其是在处理大数据量的Web应用中。本实践...

Magicbox
Global site tag (gtag.js) - Google Analytics