分頁查詢，CBO評估的行數有誤。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

SELECT /*+ gather_plan_statistics */ OBJECT_ID, OBJECT_NAME
FROM
(
SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM
(
SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
)
WHERE ROWNUM <= 3000
 )
 WHERE RN >= 2990

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
----------------------------
SQL_ID  gtm8xfbtqdwac, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ OBJECT_ID, OBJECT_NAME FROM ( SELECT ROWNUM
RN, OBJECT_ID, OBJECT_NAME FROM ( SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY
OBJECT_NAME ) WHERE ROWNUM <= 3000  )  WHERE RN >= 2990

Plan hash value: 882605040

-------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|*  1 |  VIEW                    |      |      1 |   3000 |     11 |00:00:00.02 |     696 |
|*  2 |   COUNT STOPKEY          |      |      1 |        |   3000 |00:00:00.03 |     696 |
|   3 |    VIEW                  |      |      1 |  50325 |   3000 |00:00:00.03 |     696 |
|*  4 |     SORT ORDER BY STOPKEY|      |      1 |  50325 |   3000 |00:00:00.03 |     696 |
|   5 |      TABLE ACCESS FULL   | T    |      1 |  50325 |  50325 |00:00:00.05 |     696 |
-------------------------------------------------------------------------------------------

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

   1 - filter("RN">=2990)
   2 - filter(ROWNUM<=3000)
   4 - filter(ROWNUM<=3000)

 

看ID=4的A-ROWS ,E-ROWS。