分页技术实验（二）

接着上一篇继续做实验来加深理解

很多情况下的分页显示都是需要先排序的，例如BBS中索引结果都是根据时间排序的，那怎样提高排序分页的效率呢？
方向之一就是把原来要全表扫描读取的数据都放在索引中（因为索引中的数据已经排序），就是把where条件中的字段与排序字段放入索引（这种方法比较适合AND条件）。通过仅访问索引来取得ROWID，再通过ROWID来访问原表，这样效率会很高

下面做实验验证一下

找出all_objects中owner='SYSTEM'和object_type='TABLE'中以object_id排序的5-10行

创建T表

SQL> create table t as select * from all_objects;

Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

 

先不创建索引，进行分页查询

SQL> select b.object_id,b.object_name from
  2             (select rownum rn,a.* from
  3                                     (
  4                                     select owner,object_type,object_id,object_name
  5                                     from t
  6                                     where wner = 'SYSTEM' and object_type = 'TABLE'
  7                                     order by object_id
  8                                     ) a
  9              where rownum <=10
 10             ) b
 11  where rn>=5;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
      2904 MVIEW$_ADV_TEMP
      2906 MVIEW$_ADV_FILTER
      2908 MVIEW$_ADV_LOG
      2910 MVIEW$_ADV_FILTERINSTANCE
      2911 MVIEW$_ADV_LEVEL
      2913 MVIEW$_ADV_ROLLUP

6 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=131 Card=10 Bytes=430)
   1    0   VIEW (Cost=131 Card=10 Bytes=430)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=131 Card=30 Bytes=900)
   4    3         SORT (ORDER BY STOPKEY) (Cost=131 Card=30 Bytes=1320)
   5    4           TABLE ACCESS (FULL) OF 'T' (Cost=127 Card=30 Bytes=1320)

 

Statistics
----------------------------------------------------------
        340  recursive calls
          0  db block gets
        894  consistent gets
          0  physical reads
          0  redo size
        622  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          6  rows processed

这里consistent gets为894

在owner、object_type、object_id上创建索引，来实现这个查询

SQL> select b.object_id,b.object_name from
  2             (select rownum rn,a.* from
  3                                     (
  4                                     select owner,object_type,object_id,object_name
  5                                     from t
  6                                     where wner = 'SYSTEM' and object_type = 'TABLE'
  7                                     order by object_id
  8                                     ) a
  9              where rownum <=10
 10             ) b
 11  where rn>=5;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
      2904 MVIEW$_ADV_TEMP
      2906 MVIEW$_ADV_FILTER
      2908 MVIEW$_ADV_LOG
      2910 MVIEW$_ADV_FILTERINSTANCE
      2911 MVIEW$_ADV_LEVEL
      2913 MVIEW$_ADV_ROLLUP

6 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=30 Card=10 Bytes=430)
   1    0   VIEW (Cost=30 Card=10 Bytes=430)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=30 Card=30 Bytes=900)
   4    3         SORT (ORDER BY STOPKEY) (Cost=30 Card=30 Bytes=1320)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=26 Card=30 Bytes=1320)
   6    5             INDEX (RANGE SCAN) OF 'T_I1' (NON-UNIQUE) (Cost=3 Card=30)

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         68  consistent gets
          0  physical reads
          0  redo size
        622  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)
          6  rows processed

consistent gets为68

使用ROWID来实现这个查询

SQL> create index t_i1 on t(owner,object_type,object_id);

Index created.

SQL> exec dbms_stats.gather_index_stats(user,'T_I1');

PL/SQL procedure successfully completed.

SQL> select t.object_id,t.object_name from t,   
  2  (
  3  select b.rn,b.rid from
  4     (
  5     select rownum rn,a.rid from
  6      (
  7             select rowid rid
  8             from t
  9             where wner = 'SYSTEM' and object_type = 'TABLE'
 10             order by object_id
 11             ) a
 12     where rownum <=10
 13     ) b
 14  where b.rn >=5
 15  ) c
 16  where t.rowid = c.rid;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
      2904 MVIEW$_ADV_TEMP
      2906 MVIEW$_ADV_FILTER
      2908 MVIEW$_ADV_LOG
      2910 MVIEW$_ADV_FILTERINSTANCE
      2911 MVIEW$_ADV_LEVEL
      2913 MVIEW$_ADV_ROLLUP

6 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=17 Card=10 Bytes=560)
   1    0   NESTED LOOPS (Cost=17 Card=10 Bytes=560)
   2    1     VIEW (Cost=7 Card=10 Bytes=200)
   3    2       COUNT (STOPKEY)
   4    3         VIEW (Cost=7 Card=30 Bytes=210)
   5    4           SORT (ORDER BY STOPKEY) (Cost=7 Card=30 Bytes=810)
   6    5             INDEX (RANGE SCAN) OF 'T_I1' (NON-UNIQUE) (Cost=3 Card=30 Bytes=810)
   7    1     TABLE ACCESS (BY USER ROWID) OF 'T' (Cost=1 Card=1 Bytes=36)

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          1  physical reads
          0  redo size
        622  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)
          6  rows processed

SQL> /

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
      2904 MVIEW$_ADV_TEMP
      2906 MVIEW$_ADV_FILTER
      2908 MVIEW$_ADV_LOG
      2910 MVIEW$_ADV_FILTERINSTANCE
      2911 MVIEW$_ADV_LEVEL
      2913 MVIEW$_ADV_ROLLUP

6 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=17 Card=10 Bytes=560)
   1    0   NESTED LOOPS (Cost=17 Card=10 Bytes=560)
   2    1     VIEW (Cost=7 Card=10 Bytes=200)
   3    2       COUNT (STOPKEY)
   4    3         VIEW (Cost=7 Card=30 Bytes=210)
   5    4           SORT (ORDER BY STOPKEY) (Cost=7 Card=30 Bytes=810)
   6    5             INDEX (RANGE SCAN) OF 'T_I1' (NON-UNIQUE) (Cost=3 Card=30 Bytes=810)
   7    1     TABLE ACCESS (BY USER ROWID) OF 'T' (Cost=1 Card=1 Bytes=36)

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        622  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)
          6  rows processed

 

且consistent gets为10，小于68，远远小于894

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

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