分页技术实验（二）

最新推荐文章于 2021-05-19 17:14:54 发布

转载最新推荐文章于 2021-05-19 17:14:54 发布 · 115 阅读

本文通过实验对比了不同方法在数据库查询中的性能差异，包括全表扫描、使用索引和利用ROWID进行排序分页查询。结果显示，通过合理利用索引可以显著减少数据检索的时间和资源消耗。

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

很多情况下的分页显示都是需要先排序的，例如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上创建索引，来实现这个查询

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;

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> /

6 rows selected.

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/