COUNT STOPKEY 及分页方式比较

先看下plan

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,0,'iostats'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------SQL_ID  f30c8gqzs8dc7, child number 0-------------------------------------SELECT /*+ gather_plan_statistics */ sal, ename   FROM (SELECT rownumAS rn, sal, ename           FROM (SELECT sal, ename FROM emp WHERE salIS NOT NULL ORDER BY sal) x          WHERE rownum <= 5)  WHERE rn >= 1Plan hash value: 3344584683--------------------------------------------------------------------------------------------------------| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |--------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |             |      1 |        |      5 |00:00:00.01 |       4 ||*  1 |  VIEW                          |             |      1 |      5 |      5 |00:00:00.01 |       4 ||*  2 |   COUNT STOPKEY                |             |      1 |        |      5 |00:00:00.01 |       4 ||   3 |    VIEW                        |             |      1 |     14 |      5 |00:00:00.01 |       4 ||   4 |     TABLE ACCESS BY INDEX ROWID| EMP         |      1 |     14 |      5 |00:00:00.01 |       4 ||*  5 |      INDEX FULL SCAN           | IDX_EMP_SAL |      1 |      5 |      5 |00:00:00.01 |       2 |--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=5)   5 - filter("SAL" IS NOT NULL)Note-----   - dynamic sampling used for this statement (level=2)30 rows selected.

分页语句的plan不要用autotrace看。要用dbms_xplan.display_cursor。

大家可以看以上PALN，对于只返回部分行的分页语句，并不象在AUTOTRACE里看到的那样要查询所有行后才返回数据。而是找到满足所给条件的行数据就直接返回。

在上面所示的PLAN中实际只读取了5行数据并返回。

下面我们看下另外两种分页方式及stopkey在其中的作用

先用row_number取出序号

SELECT rn AS 序号, ename AS 姓名, sal AS 工资  FROM (SELECT row_number() over(ORDER BY sal) AS rn, sal, ename          FROM emp         WHERE sal IS NOT NULL) x WHERE rn BETWEEN 1 AND 5;

plan如下

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,0,'iostats'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------SQL_ID  9704z6thn7jh9, child number 0-------------------------------------SELECT /*+ gather_plan_statistics */ rn AS 序号, ename AS 姓名, sal AS 工资FROM (SELECT row_number() over(ORDER BY sal) AS rn, sal, ename FROM emp          WHERE sal IS NOT NULL) x  WHERE rn BETWEEN 1 AND 5Plan hash value: 3296551354-------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |             |      1 |        |      5 |00:00:00.01 |       4 ||*  1 |  VIEW                         |             |      1 |     14 |      5 |00:00:00.01 |       4 ||*  2 |   WINDOW NOSORT STOPKEY       |             |      1 |     14 |      5 |00:00:00.01 |       4 ||   3 |    TABLE ACCESS BY INDEX ROWID| EMP         |      1 |     14 |      6 |00:00:00.01 |       4 ||*  4 |     INDEX FULL SCAN           | IDX_EMP_SAL |      1 |     14 |      6 |00:00:00.01 |       2 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(("RN">=1 AND "RN"<=5))   2 - filter(ROW_NUMBER() OVER ( ORDER BY "SAL")<=5)   4 - filter("SAL" IS NOT NULL)Note-----   - dynamic sampling used for this statement (level=2)

这个查询里，上限为5，而stopkey处为6，这个与第一个查询相差并不大。

第三种，取出序号后，再用序号过滤

SELECT rn AS 序号, ename AS 姓名, sal AS 工资  FROM (SELECT rownum AS rn, sal, ename          FROM (SELECT sal, ename FROM emp WHERE sal IS NOT NULL order by sal) x) WHERE rn <= 5   and rn >= 1;

plan如下

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,0,'iostats'));PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------SQL_ID  cwmp2vdjpb7a7, child number 0-------------------------------------SELECT /*+ gather_plan_statistics */rn AS 序号, ename AS 姓名, sal AS 工资FROM (SELECT rownum AS rn, sal, ename           FROM (SELECT sal, enameFROM emp WHERE sal IS NOT NULL order by sal) x)  WHERE rn <= 5    andrn >= 1Plan hash value: 540789165--------------------------------------------------------------------------------------------------------| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |--------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |             |      1 |        |      5 |00:00:00.01 |       4 ||*  1 |  VIEW                          |             |      1 |     14 |      5 |00:00:00.01 |       4 ||   2 |   COUNT                        |             |      1 |        |     14 |00:00:00.01 |       4 ||   3 |    VIEW                        |             |      1 |     14 |     14 |00:00:00.01 |       4 ||   4 |     TABLE ACCESS BY INDEX ROWID| EMP         |      1 |     14 |     14 |00:00:00.01 |       4 ||*  5 |      INDEX FULL SCAN           | IDX_EMP_SAL |      1 |     14 |     14 |00:00:00.01 |       2 |--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(("RN">=1 AND "RN"<=5))   5 - filter("SAL" IS NOT NULL)Note-----   - dynamic sampling used for this statement (level=2)

看id=2-5，在这儿访问了表中所有的行