ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle分页查询格式(十三)

Oracle分页查询格式(十三)

原创 Linux操作系统 作者:yangtingkun 时间:2009-07-29 23:54:48 0 删除 编辑

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

这篇介绍HASH SORT CLUSTER表对分页查询的帮助。

Oracle分页查询格式(一):http://yangtingkun.itpub.net/post/468/100278

Oracle分页查询格式(二):http://yangtingkun.itpub.net/post/468/101703

Oracle分页查询格式(三):http://yangtingkun.itpub.net/post/468/104595

Oracle分页查询格式(四):http://yangtingkun.itpub.net/post/468/104867

Oracle分页查询格式(五):http://yangtingkun.itpub.net/post/468/107934

Oracle分页查询格式(六):http://yangtingkun.itpub.net/post/468/108677

Oracle分页查询格式(七):http://yangtingkun.itpub.net/post/468/109834

Oracle分页查询格式(八):http://yangtingkun.itpub.net/post/468/224557

Oracle分页查询格式(九):http://yangtingkun.itpub.net/post/468/224409

Oracle分页查询格式(十):http://yangtingkun.itpub.net/post/468/224823

Oracle分页查询格式(十一):http://yangtingkun.itpub.net/post/468/485481

Oracle分页查询格式(十二):http://yangtingkun.itpub.net/post/468/485496

 

 

前几天有个网友问到,为什么这个系列的大部分例子中都没有包括查询条件。

其实分页只是一种标准的写法,分页嵌套的业务SQL才是实现查询功能的关键,而这部分可能会很简单,也可能会复杂的很。因此很难模拟各种复杂的业务SQL在分页中的表现。如果要分析分页查询,只能依旧不同业务SQL的特性进行分类,比如:包括GROUP BY操作、包含UNION ALL查询、通过全表扫描获取记录、通过索引扫描获取记录等等。

而且要先假定一些前提,既然要讨论分页的效率,那么分页的SQL一般返回的数据量会比较大或者返回记录数未知。如果业务SQL只是返回10条以内的记录,那么根本没有分页的必要;如果业务SQL本身就可以在秒级以内获取到结果,那么也不用太关心分页后的性能问题。

因此这前面实际上讨论的是返回大数据量的情况下,分页所能带来的性能优势。

至于网友提到的,大部分SQL没有包含查询条件,其实这里是简化SQL后的结果。如果SQL的查询条件可以快速的定位结果,并返回少量的数据,那么这种情况本身效率就很高,使用分页并不会带来进一步的性能提高;如果索引返回大量的数据,或者由于数据量太大,根本不会使用索引,而采用全表扫描的情况,那么这种情况其实完全可以用单表的全表扫描来模拟。

不过Oracle确实有一种表结构非常适合满足索引查询条件的排序分页,这就是HASH排序聚簇表:

SQL> CREATE CLUSTER C_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT)
  3  HASHKEYS 100000 SIZE 1125;

Cluster created.

SQL> CREATE TABLE T_HASH_SORT
  2  (ID NUMBER,
  3  OWNER VARCHAR2(30),
  4  OBJECT_NAME VARCHAR2(30),
  5  OBJECT_TYPE VARCHAR2(30),
  6  CREATED DATE SORT)
  7  CLUSTER C_HASH_SORT (ID, CREATED);

Table created.

SQL> CREATE TABLE T_NORMAL
  2  (ID NUMBER,
  3  OWNER VARCHAR2(30),
  4  OBJECT_NAME VARCHAR2(30),
  5  OBJECT_TYPE VARCHAR2(30),
  6  CREATED DATE);

Table created.

SQL> INSERT INTO T_HASH_SORT
  2  SELECT *
  3  FROM
  4  (
  5  SELECT MOD(ROWNUM, 100000) ID,
  6  A.OWNER,
  7  OBJECT_NAME,
  8  OBJECT_TYPE,
  9  A.CREATED
 10  FROM DBA_OBJECTS A, DBA_DB_LINKS
 11  )
 12  ORDER BY ID, CREATED;

2476775 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO T_NORMAL
  2  SELECT *
  3  FROM
  4  (
  5  SELECT MOD(ROWNUM, 100000) ID,
  6  A.OWNER,
  7  OBJECT_NAME,
  8  OBJECT_TYPE,
  9  A.CREATED
 10  FROM DBA_OBJECTS A, DBA_DB_LINKS
 11  )
 12  ORDER BY ID, CREATED;

2476775 rows created.

SQL> COMMIT;

Commit complete.

SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT *
  2  FROM
  3  (
  4     SELECT ROWNUM RN, A.*
  5     FROM
  6     (
  7             SELECT ID, OWNER, OBJECT_TYPE, CREATED
  8             FROM T_NORMAL
  9             WHERE ID = 11232
 10             ORDER BY CREATED
 11     ) A
 12     WHERE ROWNUM <= 20
 13  )
 14  WHERE RN > 10;

        RN         ID OWNER                OBJECT_TYPE                    CREATED
---------- ---------- -------------------- ------------------------------ --------------
        11      11232 SYS                  JAVA CLASS                     11-6
-08
        12      11232 SYS                  JAVA CLASS                     11-6
-08
        13      11232 PUBLIC               SYNONYM                        11-6
-08
        14      11232 SYS                  JAVA CLASS                     11-6
-08
        15      11232 CTXSYS               TABLE                          11-6
-08
        16      11232 ORDSYS               JAVA RESOURCE                  11-6
-08
        17      11232 MDSYS                PACKAGE BODY                   11-6
-08
        18      11232 PUBLIC               SYNONYM                        11-6
-08
        19      11232 PUBLIC               SYNONYM                        11-6
-08
        20      11232 JIANGSU15            INDEX                          12-6
-08

10 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 1455441750

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |    20 |  1380 |  3213   (1)| 00:00:45 |
|*  1 |  VIEW                    |          |    20 |  1380 |  3213   (1)| 00:00:45 |
|*  2 |   COUNT STOPKEY          |          |       |       |            |          |
|   3 |    VIEW                  |          |   106 |  5936 |  3213   (1)| 00:00:45 |
|*  4 |     SORT ORDER BY STOPKEY|          |   106 |  5936 |  3213   (1)| 00:00:45 |
|*  5 |      TABLE ACCESS FULL   | T_NORMAL |   106 |  5936 |  3212   (1)| 00:00:45 |
-------------------------------------------------------------------------------------

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

   1 - filter("RN">10)
   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   5 - filter("ID"=11232)

Note
-----
   - dynamic sampling used for this statement


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

SQL> SELECT *
  2  FROM
  3  (
  4     SELECT ROWNUM RN, A.*
  5     FROM
  6     (
  7             SELECT ID, OWNER, OBJECT_TYPE, CREATED
  8             FROM T_HASH_SORT
  9             WHERE ID = 11232
 10             ORDER BY CREATED
 11     ) A
 12     WHERE ROWNUM <= 20
 13  )
 14  WHERE RN > 10;

        RN         ID OWNER                OBJECT_TYPE                    CREATED
---------- ---------- -------------------- ------------------------------ --------------
        11      11232 SYS                  JAVA CLASS                     11-6
-08
        12      11232 SYS                  JAVA CLASS                     11-6
-08
        13      11232 PUBLIC               SYNONYM                        11-6
-08
        14      11232 PUBLIC               SYNONYM                        11-6
-08
        15      11232 CTXSYS               TABLE                          11-6
-08
        16      11232 ORDSYS               PROCEDURE                      11-6
-08
        17      11232 MDSYS                TYPE                           11-6
-08
        18      11232 SYS                  JAVA CLASS                     11-6
-08
        19      11232 SYSMAN               TRIGGER                        11-6
-08
        20      11232 JIANGSU15            INDEX                          12-6
-08

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 156907859

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    20 |  1380 |     5 (100)| 00:00:01 |
|*  1 |  VIEW                |             |    20 |  1380 |     5 (100)| 00:00:01 |
|*  2 |   COUNT STOPKEY      |             |       |       |            |          |
|   3 |    VIEW              |             |  2293 |   125K|     5 (100)| 00:00:01 |
|*  4 |     TABLE ACCESS HASH| T_HASH_SORT |  2293 |   125K|            |          |
------------------------------------------------------------------------------------

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

   1 - filter("RN">10)
   2 - filter(ROWNUM<=20)
   4 - access("ID"=11232)

Note
-----
   - dynamic sampling used for this statement


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

为了避免OraclePARSE以及缓存对SQL的影响,上面的两个查询都是第二次执行的结果。

比较了一个普通表和HASH SORT聚簇表在分页情况下的性能差距,普通表需要9000多的逻辑读,而HASH SORT CLUSTER表仅仅需要5个逻辑读。显然后者更适合这种指定一个查询条件并排序的分页查询。

当然这是全表扫描的情况,如果普通表建立了复合索引,可以极大的提高查询的效率:

SQL> CREATE INDEX IND_T_NORMAL ON T_NORMAL (ID, CREATED);

Index created.

Elapsed: 00:00:04.18
SQL> SELECT *
  2  FROM
  3  (
  4     SELECT ROWNUM RN, A.*
  5     FROM
  6     (
  7             SELECT ID, OWNER, OBJECT_TYPE, CREATED
  8             FROM T_NORMAL
  9             WHERE ID = 11232
 10             ORDER BY CREATED
 11     ) A
 12     WHERE ROWNUM <= 20
 13  )
 14  WHERE RN > 10;

        RN         ID OWNER                OBJECT_TYPE                    CREATED
---------- ---------- -------------------- ------------------------------ --------------
        11      11232 SYS                  JAVA CLASS                     11-6
-08
        12      11232 SYS                  JAVA CLASS                     11-6
-08
        13      11232 PUBLIC               SYNONYM                        11-6
-08
        14      11232 SYS                  JAVA CLASS                     11-6
-08
        15      11232 CTXSYS               TABLE                          11-6
-08
        16      11232 ORDSYS               JAVA RESOURCE                  11-6
-08
        17      11232 MDSYS                PACKAGE BODY                   11-6
-08
        18      11232 PUBLIC               SYNONYM                        11-6
-08
        19      11232 PUBLIC               SYNONYM                        11-6
-08
        20      11232 JIANGSU15            INDEX                          12-6
-08

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1590327436

-------------------------------------------------------------------------------------------
|Id | Operation                      | Name         |Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|  0| SELECT STATEMENT               |              |   20|  1380 |     4   (0)| 00:00:01 |
|* 1|  VIEW                          |              |   20|  1380 |     4   (0)| 00:00:01 |
|* 2|   COUNT STOPKEY                |              |     |       |            |          |
|  3|    VIEW                        |              |   25|  1400 |     4   (0)| 00:00:01 |
|  4|     TABLE ACCESS BY INDEX ROWID| T_NORMAL     |   25|  1400 |     4   (0)| 00:00:01 |
|* 5|      INDEX RANGE SCAN          | IND_T_NORMAL |   25|       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter("RN">10)
   2 - filter(ROWNUM<=20)
   5 - access("ID"=11232)

Note
-----
   - dynamic sampling used for this statement


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

可以看到,通过建立复合索引可以显著的提高普通表针对分页查询的性能。但是性能仍然比HASH SORT CLUSTER要差。不过利用索引的方式灵活性更高,因为HASH SORT CLUSTER表只针对指定查询,而建立索引则可以根据查询的不同以不同的方式创建。

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10404854