complicated page procedure query

CREATE OR REPLACE PACKAGE "TYPES"
AS
 TYPE cursor_type IS REF CURSOR;
END;

 

CREATE OR REPLACE FUNCTION "USF_CALC_ROW_START"
/*
    Copyright(c) 2005 SpringTang 

   Remark:
    First creation (SpringTang20050902)
*/
(
 PG_NUM_IN NUMBER,
 TOTAL_REC_PER_PG_IN NUMBER
)
RETURN NUMBER
AS

BEGIN

    RETURN ((PG_NUM_IN - 1) * TOTAL_REC_PER_PG_IN) + 1;

END;

CREATE OR REPLACE FUNCTION "USF_CALC_ROW_END"
/*
    Copyright(c) 2005 SpringTang
    Remark:
    First creation (SpringTang 20050902)
*/
(
 PG_NUM_IN NUMBER,
 TOTAL_REC_PER_PG_IN NUMBER
)
RETURN NUMBER
AS

BEGIN

 -- The result is increased by 1 intentionally in order to facilitate
 -- proper setting of an attribute in Transaction Context object (morePagesAvailable)
    RETURN (PG_NUM_IN * TOTAL_REC_PER_PG_IN) + 1;

END;

 

 

 

CREATE OR REPLACE PROCEDURE P_CMS_CORP_ACCT_S#CORP_ID#PG
/*
     COPYRIGHT(C) 2005 SpringTang
     FIRST CREATION (Thomas  20051208)

*/
(
    CD_IN          IN      TBL_CMS_CORP.CD%TYPE,
    REF_CURSOR     OUT     TYPES.CURSOR_TYPE   ,
    PAGE_NUMBER_IN      IN      NUMBER         ,
   TOTAL_RECORD_IN    IN      NUMBER,
    REQUEST_ID_IN   IN VARCHAR2
)
AS
      V_ROWSTART          NUMBER;
     V_ROWEND            NUMBER;
BEGIN

     V_ROWSTART := USF_CALC_ROW_START(PAGE_NUMBER_IN, TOTAL_RECORD_IN);
     V_ROWEND   := USF_CALC_ROW_END(PAGE_NUMBER_IN, TOTAL_RECORD_IN);

   OPEN REF_CURSOR  FOR
       SELECT
               CD                  ,
               NM                  ,
               ID                  ,
               CMS_MT_ACCT_TYP_CD  ,
               SYS_ACCT_SUB_TYP_CD ,
               ACCT_IDX            ,
               ACCT_NO             ,
               ACCT_NM             ,
               ACCT_ALIAS          ,
               MT_BR_CD            ,
               MT_CURR_CD          ,
               ACCT_OWNER_CIF_NO   ,
               ACCT_OWNER_CIF_NM   ,
               IS_DBT              ,
               IS_CDT              ,
               SYS_ACTN_TYP_CD     ,
               UPDATED_BY_ID       ,
               UPDATED_BY_CD       ,
               UPDATED_BY_NM       ,
               VERSION             ,
               TOTAL_PAGE
         FROM
             (
             SELECT ROWNUM ROWNUMBER, VW.*
               FROM (
                      SELECT
                             COUNT(*) OVER () TOTAL_PAGE       ,
                             CC.CD                             ,
                             CC.NM                             ,
                             CORP_ACCT.ID                      ,
                             CORP_ACCT.CMS_MT_ACCT_TYP_CD      ,
                             CORP_ACCT.SYS_ACCT_SUB_TYP_CD     ,
                             CORP_ACCT.ACCT_IDX                ,
                             CORP_ACCT.ACCT_NO                 ,
                             CORP_ACCT.ACCT_NM                 ,
                             CORP_ACCT.ACCT_ALIAS              ,
                             CORP_ACCT.MT_BR_CD                ,
                             CORP_ACCT.MT_CURR_CD              ,
                             CORP_ACCT.ACCT_OWNER_CIF_NO       ,
                             CORP_ACCT.ACCT_OWNER_CIF_NM       ,
                             CORP_ACCT.IS_DBT                  ,
                             CORP_ACCT.IS_CDT                  ,
                             CORP_ACCT.SYS_ACTN_TYP_CD         ,
                             CORP_ACCT.UPDATED_BY_ID           ,
                             CORP_ACCT.UPDATED_BY_CD           ,
                             CORP_ACCT.UPDATED_BY_NM           ,
                             CORP_ACCT.VERSION
                        FROM
                             (
                               SELECT
                                      ID                            ,
                                      CMS_CORP_CD                   ,
                                      CMS_MT_ACCT_TYP_CD            ,
                                      SYS_ACCT_SUB_TYP_CD           ,
                                      ACCT_IDX                      ,
                                      ACCT_NO                       ,
                                      ACCT_NM                       ,
                                      ACCT_ALIAS                    ,
                                      MT_BR_CD                      ,
                                      MT_CURR_CD                    ,
                                      ACCT_OWNER_CIF_NO             ,
                                      ACCT_OWNER_CIF_NM             ,
                                      IS_DBT                        ,
                                      IS_CDT                        , 
                                      SYS_ACTN_TYP_CD               ,         
                                      UPDATED_BY_ID                 ,
                                      UPDATED_BY_CD                 ,
                                      UPDATED_BY_NM                 ,
                                      VERSION
                                 FROM TBL_CMS_CORP_ACCT
                                WHERE IS_DEL = 0
                              ) CORP_ACCT
                       RIGHT OUTER JOIN TBL_CMS_CORP CC ON
                          CC.CD = CORP_ACCT.CMS_CORP_CD
                       WHERE CC.CD = CD_IN
                         AND CC.IS_DEL = 0
                     )VW
              WHERE ROWNUM < V_ROWEND
             )
       WHERE ROWNUMBER >= V_ROWSTART;

END ;

Reference:

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

分页查询格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

这篇文章用几个例子来说明分页查询的效率。首先构造一个比较大的表作为测试表：

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES;

表已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
457992

首先比较两种分页方法的区别：

SQL> SET AUTOT ON
SQL> COL OBJECT_NAME FORMAT A30
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
5807 ALL_APPLY_PROGRESS
1769 ALL_ARGUMENTS
2085 ALL_ASSOCIATIONS
4997 ALL_AUDIT_POLICIES
4005 ALL_BASE_TABLE_MVIEWS
5753 ALL_CAPTURE
5757 ALL_CAPTURE_PARAMETERS
5761 ALL_CAPTURE_PREPARED_DATABASE
5765 ALL_CAPTURE_PREPARED_SCHEMAS
5769 ALL_CAPTURE_PREPARED_TABLES

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457992 Bytes=42135264)
1 0 VIEW (Cost=864 Card=457992 Bytes=42135264)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

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

SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
5807 ALL_APPLY_PROGRESS
1769 ALL_ARGUMENTS
2085 ALL_ASSOCIATIONS
4997 ALL_AUDIT_POLICIES
4005 ALL_BASE_TABLE_MVIEWS
5753 ALL_CAPTURE
5757 ALL_CAPTURE_PARAMETERS
5761 ALL_CAPTURE_PREPARED_DATABASE
5765 ALL_CAPTURE_PREPARED_SCHEMAS
5769 ALL_CAPTURE_PREPARED_TABLES

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)
1 0 VIEW (Cost=864 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

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

二者执行效率相差很大，一个需要8000多逻辑读，而另一个只需要5个逻辑读。观察二者的执行计划可以发现，两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY，也就是说，Oracle将ROWNUM <= 20推入到查询内层，当符合查询的条件的记录达到STOPKEY的值，则Oracle结束查询。

因此，可以预见，采用第二种方式，在翻页的开始部分查询速度很快，越到后面，效率越低，当翻到最后一页，效率应该和第一种方式接近。

SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 WHERE ROWNUM <= 457990
10 )
11 WHERE RN >= 457980;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
7128 XCF_I_HANDLE_STATUS
7126 XCF_P
7127 XCF_U1
7142 XDF
7145 XDF_I_DF_KEY
7146 XDF_I_HANDLE_STATUS
7143 XDF_P
7144 XDF_U1
TEST.YANGTINGKUN
TEST4.YANGTINGKUN
YANGTK.YANGTINGKUN

已选择11行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457990 Bytes=42135080)
1 0 VIEW (Cost=864 Card=457990 Bytes=42135080)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7423 physical reads
0 redo size
680 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed