Oracle 数据库分页四种写法及分析

最新推荐文章于 2021-04-15 02:54:44 发布

原创最新推荐文章于 2021-04-15 02:54:44 发布 · 247 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Oracle 开发专栏收录该内容

23 篇文章

订阅专栏

本文通过四个不同的分页查询方法对比，在Oracle数据库中探讨了不同SQL写法对于查询效率的影响。实验证明，使用ROW_NUMBER() OVER()方法进行分页查询在大多数情况下表现出了较好的性能。

总结:

在测试结果来看，采用第一种写法能比较获得较好执行的效率。

以下是测试的结果:

DROP TABLE TT PURGE;

CREATE TABLE TT AS SELECT * FROM DBA_OBJECTS;--创建表
DELETE TT WHERE OBJECT_ID IS NULL;
SELECT COUNT(*) FROM TT;--51784

INSERT INTO TT SELECT * FROM TT;
INSERT INTO TT SELECT * FROM TT;
INSERT INTO TT SELECT * FROM TT;
INSERT INTO TT SELECT * FROM TT;
INSERT INTO TT SELECT * FROM TT;
COMMIT;--共插入828544

CREATE INDEX IDX_TT_OBJECT_ID ON TT(OBJECT_ID);--创建索引

execute dbms_stats.gather_table_stats(ownname => 'ADMIN',tabname => 'TT');--收集统计信息
-----------------------------------------------------------------------------------------------------

以下是分页的几种方法
--1.
--执行时间: 00: 00: 05.02
/*

22857 consistent gets
10891 physical reads

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT * FROM TT ORDER BY OBJECT_ID) A
         WHERE ROWNUM <= 200000)
 WHERE RN >= 100000;

--2.
--执行时间: 00: 00: 17.90
/*

22857 consistent gets
34961 physical reads

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT * FROM TT ORDER BY OBJECT_ID) A
        )
 WHERE RN BETWEEN 100000 AND 200000;

--3.
--执行时间: 00: 00: 06.04
/*22857 consistent gets
12081 physical reads*/

SELECT *
   FROM (SELECT TT.*, ROW_NUMBER() OVER(ORDER BY OBJECT_ID) ROWINDEX FROM TT) A
  WHERE ROWINDEX BETWEEN 100000 AND 200000;

--4.
--执行时间: 00: 00: 06.08
/* 122858 consistent gets
11962 physical reads*/
-- /*+ ORDERED USE_NL(P S) */

SELECT *
  FROM TT
 WHERE ROWID IN
       (SELECT RID
          FROM (SELECT ROWNUM RN, RID
                  FROM (SELECT ROWID RID FROM TT ORDER BY OBJECT_ID)--ORDER BY SAL DESC
                 WHERE ROWNUM <= 200000)
         WHERE RN >= 100000);

另外一个对分页在10g,11g下执行效率的分析帖子:

http://www.itpub.net/thread-1603830-1-2.html