分页查询初接触

最新推荐文章于 2021-04-20 16:44:13 发布

转载最新推荐文章于 2021-04-20 16:44:13 发布 · 60 阅读

晚上无聊，看到了一个有趣的帖子，可惜帖子到最后也没给出个答案，实验的冲动来自于这个帖子。
http://www.itpub.net/viewthread.php?tid=1266783&extra=&page=1

需求：对表ss按照object_id降序排序，取出第3000条记录。提供了两种语句的写法，看看哪种是最优的。

语句1

语句2

SELECT *
  FROM (SELECT A.*, ROWNUM RN
      FROM (SELECT /*+ index_desc(ss ind_id) */*
               FROM SS
               ORDER BY OBJECT_ID DESC) A
)
WHERE RN =3000;

SELECT *
  FROM (SELECT A.*, ROWNUM RN
      FROM (SELECT
               *
               FROM SS
               ORDER BY OBJECT_ID DESC) A
      WHERE ROWNUM <= 3000)
WHERE RN > 2999;

SQL> create table ss as select * from dba_objects;
SQL>alter table ss modify object_id not null;（注意这一步对于实验来说是必须的）
SQL> insert /*+ append */ into ss select * from ss;
执行N次。-------语句略

SQL> create index ind_id on ss(object_id);

分析表:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME       => 'SCOTT',
                              TABNAME       => 'SS',
                              ESTIMATE_PERCENT => 100,
                              METHOD_OPT    => 'for all indexed columns',
                              CASCADE       => TRUE);
END;

语句1，不加rownum的，执行时间： 07.64
SELECT *
  FROM (SELECT A.*, ROWNUM RN
      FROM (SELECT /*+ index_desc(ss ind_id) */*
               FROM SS
               ORDER BY OBJECT_ID DESC) A
)
WHERE RN =3000;

执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
Plan hash value: 3794800296

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  VIEW                         |       |    1 | 397K|    1 |00:00:07.60 |    398K|
| 2 | COUNT                      |       |    1 |       | 397K|00:00:06.77 |    398K|
| 3 | VIEW                      |       |    1 | 397K| 397K|00:00:05.17 |    398K|
| 4 |    TABLE ACCESS BY INDEX ROWID| SS    |    1 | 397K| 397K|00:00:03.58 |    398K|
| 5 |    INDEX FULL SCAN DESCENDING| IND_ID |    1 | 397K| 397K|00:00:01.19 |    902 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN"=3000)

语句2，加rownum的：

SELECT *
  FROM (SELECT A.*, ROWNUM RN
      FROM (SELECT
               *
               FROM SS
               ORDER BY OBJECT_ID DESC) A
      WHERE ROWNUM <= 3000)
WHERE RN > 2999;

执行时间：00.17秒，比第一个的时间大大减小。

执行计划：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 2998430533

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  VIEW                         |       |    1 | 3000 |    1 |00:00:00.11 | 3010 |
|*  2 | COUNT STOPKEY             |       |    1 |       | 3000 |00:00:00.10 | 3010 |
| 3 | VIEW                      |       |    1 | 3000 | 3000 |00:00:00.08 | 3010 |
| 4 |    TABLE ACCESS BY INDEX ROWID| SS    |    1 | 397K| 3000 |00:00:00.05 | 3010 |
| 5 |    INDEX FULL SCAN DESCENDING| IND_ID |    1 | 3000 | 3000 |00:00:00.02 |    10 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">2999)
2 - filter(ROWNUM<=3000)

对比两个语句执行计划的A-ROWS列,BUFFERS列，能清晰的看出第二个语句的性能明显优于第一个。之所以这样是因为第二个语句增加了rownum<=3000的条件，导致执行计划的ID=2多出了一个count stopkey操作，正是它控制着它的子操作可以提前被终止，如果没有这个count stopkey操作，index full scan descending（id=5）操作需要从右向左扫描所有的索引块，再根据索引块读取leaf节点指向的所有数据块。由于count stopkey的存在，就可以控制它的子操作（id=5）扫描所需数据块后就终止操作。（其实5不是2的子，而是重孙，姑且让我叫子吧）

初学者可能有疑问，需求的内容是按照object_id降序排序取第3000条，如果增加rownum<=3000后，得到的结果会不会不正确，因为其余的记录并没有被排序。这个不会的，因为索引本来就是有序存储的，我们取的3000条记录肯定包含了降序排列后的第3000条记录。

为了初学者更容易理解count stopkey操作，举个简单的例子：

SQL> create table test as select * from dba_objects;

表已创建。

SQL> set autotrace traceonly
SQL> select * from test where rownum<2;

执行计划
----------------------------------------------------------
Plan hash value: 3931117773

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 | 177 |    4  (50)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |    |    |    |          |       |
| 2 | TABLE ACCESS FULL| TEST | 51101 |  8832K|    4  (50)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<2)

统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      4  consistent gets
      6  physical reads
      0  redo size

我们可以看到这个执行计划部分也有一个count stopkey操作。注意观察id=2的操作是一个全表扫描的操作，但是看到这个查询的逻辑读只有4，远远小于表的数据块。之所以会这样，就是因为count stopkey控制着它的子操作（table access full），在获得所需记录数后，父操作就终止了子操作。

如果不提前终止，需要的逻辑读是4037：

SQL> select * from test;

已选择49745行。

执行计划
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 51101 |  8832K| 196 (3)| 00:00:03 |
| 1 |  TABLE ACCESS FULL| TEST | 51101 |  8832K| 196 (3)| 00:00:03 |
--------------------------------------------------------------------------

统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   4037  consistent gets
      392  physical reads

再多说几句，除了count stopkey可以控制子操作的执行程度，有一些操作还可以控制子操作不执行。比如：

SQL> select * from ss where 1=0;

未选定行

已用时间: 00: 00: 00.36
SQL> select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));

Plan hash value: 1965655566

---------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|* 1 | FILTER            |      |      1 |        |      0 |00:00:00.01 |
|   2 |   TABLE ACCESS FULL| SS   |      0 |    397K|      0 |00:00:00.01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NULL IS NOT NULL)

看到ID=2的starts列的值为0，即这个table access full操作根本没有执行。

还需要说明一点，object_id列的非空限制，在本实验里是必须的，因为索引中不存储空值，如果没这个非空的条件，执行计划就不可能采取索引扫描，不过在查询里增加object_id is not null也是可以的。

我们可以看看没有这个非空的限制条件会怎么样。

SQL> alter table ss modify object_id  null;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 1785559084

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
|*  1 |  VIEW                   |    |    1 | 3000 |    1 |00:00:02.14 | 5476 |
|*  2 | COUNT STOPKEY       |    |    1 |       | 3000 |00:00:02.14 | 5476 |
| 3 | VIEW                |    |    1 | 397K| 3000 |00:00:02.13 | 5476 |
|*  4 |    SORT ORDER BY STOPKEY|    |    1 | 397K| 3000 |00:00:02.11 | 5476 |
| 5 |    TABLE ACCESS FULL | SS |    1 | 397K| 397K|00:00:00.80 | 5476 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">2999)
2 - filter(ROWNUM<=3000)
4 - filter(ROWNUM<=3000)

我们看到执行计划里看不到了count stopkey操作，但是ID=4行出现了一个sort order by stopkey操作，从ID=5的A-ROWS来看，操作执行了全表扫描（因为索引中不存储空值，即使增加索引HINT也会全表扫描）。但是ID=3的排序却由于sort order by stopkey的存在得到了优化，只需要排序出前三千条记录就可以，而不需要把数据全部排序。在这种情况下，rownum的存在，优化的只是排序操作，而全表扫描不可避免。因此这种情况下，如果条件允许最好增加一个非空的约束，或者在SQL语句增加条件object_id is not null。

SQL> SELECT *
  2 FROM (SELECT A.*, ROWNUM RN
  3          FROM (SELECT
  4                *
  5                   FROM SS WHEREobject_id IS NOT NULL
  6                ORDER BY OBJECT_ID DESC) A
  7          WHERE ROWNUM <= 3000)
  8 WHERE RN > 2999;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 2998430533

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW                         |       |    1 | 3000 |    1 |00:00:00.17 | 3010 | 7 |
|*  2 | COUNT STOPKEY             |       |    1 |       | 3000 |00:00:00.16 | 3010 | 7 |
| 3 | VIEW                      |       |    1 | 3000 | 3000 |00:00:00.14 | 3010 | 7 |
| 4 |    TABLE ACCESS BY INDEX ROWID| SS    |    1 | 397K| 3000 |00:00:00.12 | 3010 | 7 |
|*  5 |    INDEX FULL SCAN DESCENDING| IND_ID |    1 | 3000 | 3000 |00:00:00.08 |    10 | 7 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">2999)
2 - filter(ROWNUM<=3000)
5 - filter("OBJECT_ID" IS NOT NULL)

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

转载于:http://blog.itpub.net/22034023/viewspace-667725/