分页技术实验（一）

今天看了《数据库性能优化》中的分页技术，最好的理解是自动动手做实验，下面就做实验测试一下

首先创建一个大表

SQL> create table t as select * from all_objects;

Table created.

SQL> insert into t select * from t;

49892 rows created.

SQL> /

99784 rows created.

SQL> select count(*) from t;

  COUNT(*)
----------
    199568
   
SQL> begin
  2     dbms_stats.gather_table_stats(user,'T');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.24

下面使用分页技术查询4991-5000行的数据

SQL> select a.owner,a.object_name,a.object_type,a.status
  2  from ( select rownum rn,t.owner,t.object_name,t.object_type,t.status
  3                      from t
  4                      where rownum <= 5000
  5       ) a
  6  where a.rn >=4991;

OWNER                          OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------------------ ------------------- -------
SYS                            SCHEDULER$_JOB_RUN_DETAILS     TABLE               VALID
SYS                            SCHEDULER$_WINDOW_DETAILS      TABLE               VALID
SYS                            SCHEDULER$_WINDOW_GROUP        TABLE               VALID
SYS                            SCHEDULER$_WINDOW_GROUP_PK     INDEX               VALID
SYS                            SCHEDULER$_WINGRP_MEMBER       TABLE               VALID
SYS                            SCHEDULER$_WINGRP_MEMBER_PK    INDEX               VALID
SYS                            I_SCHEDULER_WINGRP_MEMBER1     INDEX               VALID
SYS                            I_SCHEDULER_WINGRP_MEMBER2     INDEX               VALID
SYS                            SCHEDULER$_SCHEDULE            TABLE               VALID
SYS                            SCHEDULER$_SCHEDULE_PK         INDEX               VALID

10 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3593519476

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  5000 |   307K|    21   (0)| 00:00:01 |
|*  1 |  VIEW               |      |  5000 |   307K|    21   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T    |  5000 |   229K|    21   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("A"."RN">=4991)
   2 - filter(ROWNUM<=5000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         65  consistent gets
          0  physical reads
          0  redo size
        959  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
        
        
从上面的执行计划来看，第3行只读了5000行，consistent gets这75，查询的数据越往后读取的数据越多。

下面查询19001-19010的数据

      
SQL> select a.owner,a.object_name,a.object_type,a.status
  2  from ( select rownum rn,t.owner,t.object_name,t.object_type,t.status
  3                      from t
  4                      where rownum <= 190010
  5       ) a
  6  where a.rn >=190001;

OWNER                          OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------------------ ------------------- -------
ORDSYS                         /ddcef5d3_RemoteJAI            JAVA CLASS          VALID
ORDSYS                         /dfc9b2c7_RemoteRIF            JAVA CLASS          VALID
ORDSYS                         /e810c474_RemoteRenderableOp   JAVA CLASS          VALID
ORDSYS                         /7f067760_RemoteRenderedImage  JAVA CLASS          VALID
ORDSYS                         /d2714411_RemoteRenderedOp     JAVA CLASS          VALID
ORDSYS                         /a2e8db4b_SerSerializer        JAVA CLASS          VALID
ORDSYS                         /6d1127f0_SerState             JAVA CLASS          VALID
PUBLIC                         /9b16710b_TranslatePropertyGen SYNONYM             VALID
PUBLIC                         javax/xml/namespace/QName      SYNONYM             VALID
PUBLIC                         /d6221a34_NamespaceContext     SYNONYM             VALID

10 rows selected.

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 3593519476

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   190K|    11M|   725   (1)| 00:00:09 |
|*  1 |  VIEW               |      |   190K|    11M|   725   (1)| 00:00:09 |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T    |   190K|  8721K|   725   (1)| 00:00:09 |
----------------------------------------------------------------------------

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

   1 - filter("A"."RN">=190001)
   2 - filter(ROWNUM<=190010)

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

上面执行计划中第3行显示读取了190K数据量，和全表扫描一样，consistent gets
为2612
但幸运的是一般用户只读取5%的数据量，像google查询一样，我很少翻到10页以后

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

转载于:http://blog.itpub.net/8745319/viewspace-582700/