分页SQL模板

create table page as select * from dba_objects;




BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'PAGE',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size skewonly',
                                no_invalidate    => FALSE,
                                degree           => 4,
                                cascade          => TRUE);
END;


第1页:
SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 20  
)  
WHERE RN >= 0

第2页:

SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21

测试1,没有索引:




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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	3c80m99x845ct, child number 0
-------------------------------------
SELECT *FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM page where
object_id >1000 and owner='SYS'  order by object_id desc) A WHERE
ROWNUM <= 20 ) WHERE RN >= 0

Plan hash value: 3163554969

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation		 | Name | Starts | E-Rows | A-Rows |   A-Time	| Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	 |	|      1 |	  |	20 |00:00:00.02 |    1246 |	  |	  |	     |
|*  1 |  VIEW			 |	|      1 |     20 |	20 |00:00:00.02 |    1246 |	  |	  |	     |
|*  2 |   COUNT STOPKEY 	 |	|      1 |	  |	20 |00:00:00.02 |    1246 |	  |	  |	     |
|   3 |    VIEW 		 |	|      1 |  37380 |	20 |00:00:00.02 |    1246 |	  |	  |	     |
|*  4 |     SORT ORDER BY STOPKEY|	|      1 |  37380 |	20 |00:00:00.02 |    1246 |   619K|   472K|  550K (0)|
|*  5 |      TABLE ACCESS FULL	 | PAGE |      1 |  37380 |  36818 |00:00:00.01 |    1246 |	  |	  |	     |
----------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=0)
   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   5 - filter(("OWNER"='SYS' AND "OBJECT_ID">1000))


27 rows selected.

create index idx_page1 on page(object_id);


SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT   /*+ index(a idx_page_1) */  * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 20  
)  
WHERE RN >= 0

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	b1cv695sfwkzw, child number 0
-------------------------------------
SELECT *FROM ( SELECT A.*, ROWNUM RN FROM (SELECT   /*+ index(a
idx_page_1) */	* FROM page where object_id >1000 and owner='SYS'
order by object_id desc) A WHERE ROWNUM <= 20 ) WHERE RN >= 0

Plan hash value: 1455954716

-------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	    |	   1 |	      |     20 |00:00:00.01 |	    7 |
|*  1 |  VIEW				|	    |	   1 |	   20 |     20 |00:00:00.01 |	    7 |
|*  2 |   COUNT STOPKEY 		|	    |	   1 |	      |     20 |00:00:00.01 |	    7 |
|   3 |    VIEW 			|	    |	   1 |	   20 |     20 |00:00:00.01 |	    7 |
|*  4 |     TABLE ACCESS BY INDEX ROWID | PAGE	    |	   1 |	37380 |     20 |00:00:00.01 |	    7 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IDX_PAGE1 |	   1 |	   46 |     44 |00:00:00.01 |	    4 |
-------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=0)
   2 - filter(ROWNUM<=20)
   4 - filter("OWNER"='SYS')
   5 - access("OBJECT_ID">1000)


27 rows selected.


此时访问了44条,然后刹车


create index idx_page2 on page(object_id,owner);
强制走索引:
SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT   /*+ index(a idx_page_2) */  * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 20  
)  
WHERE RN >= 0


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	02ptg8m7jrc6g, child number 0
-------------------------------------
SELECT *FROM ( SELECT A.*, ROWNUM RN FROM (SELECT   /*+ index(a
idx_page_2) */	* FROM page where object_id >1000 and owner='SYS'
order by object_id desc) A WHERE ROWNUM <= 20 ) WHERE RN >= 0

Plan hash value: 2750738262

----------------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	    |	   1 |	      |     20 |00:00:00.01 |	    7 |      1 |
|*  1 |  VIEW				|	    |	   1 |	   20 |     20 |00:00:00.01 |	    7 |      1 |
|*  2 |   COUNT STOPKEY 		|	    |	   1 |	      |     20 |00:00:00.01 |	    7 |      1 |
|   3 |    VIEW 			|	    |	   1 |	   20 |     20 |00:00:00.01 |	    7 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID | PAGE	    |	   1 |	37380 |     20 |00:00:00.01 |	    7 |      1 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IDX_PAGE2 |	   1 |	   20 |     20 |00:00:00.01 |	    4 |      1 |
----------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=0)
   2 - filter(ROWNUM<=20)
   5 - access("OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1000 AND "OWNER"='SYS')
       filter("OWNER"='SYS')


27 rows selected.

这种情况下实际访问了20条 最优


取下一页:

SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT  /*+ index(a
idx_page_2) */ * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	4t09tzcfd89gm, child number 0
-------------------------------------
SELECT *FROM ( SELECT A.*, ROWNUM RN FROM (SELECT  /*+ index(a
idx_page_2) */ * FROM page where object_id >1000 and owner='SYS'  order
by object_id desc) A WHERE ROWNUM <= 40 ) WHERE RN >= 21

Plan hash value: 2750738262

----------------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	    |	   1 |	      |     20 |00:00:00.01 |	   12 |      2 |
|*  1 |  VIEW				|	    |	   1 |	   40 |     20 |00:00:00.01 |	   12 |      2 |
|*  2 |   COUNT STOPKEY 		|	    |	   1 |	      |     40 |00:00:00.01 |	   12 |      2 |
|   3 |    VIEW 			|	    |	   1 |	   40 |     40 |00:00:00.01 |	   12 |      2 |
|   4 |     TABLE ACCESS BY INDEX ROWID | PAGE	    |	   1 |	37380 |     40 |00:00:00.01 |	   12 |      2 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IDX_PAGE2 |	   1 |	   40 |     40 |00:00:00.01 |	    6 |      2 |
----------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=21)
   2 - filter(ROWNUM<=40)
   5 - access("OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1000 AND "OWNER"='SYS')
       filter("OWNER"='SYS')


27 rows selected.


第三页:

SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT /*+ index(a
idx_page_2) */ * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 60  
)  
WHERE RN >= 41

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	03sjqxpunmthb, child number 0
-------------------------------------
SELECT *FROM ( SELECT A.*, ROWNUM RN FROM (SELECT /*+ index(a
idx_page_2) */ * FROM page where object_id >1000 and owner='SYS'  order
by object_id desc) A WHERE ROWNUM <= 60 ) WHERE RN >= 41

Plan hash value: 2750738262

-------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	    |	   1 |	      |     20 |00:00:00.01 |	   15 |
|*  1 |  VIEW				|	    |	   1 |	   60 |     20 |00:00:00.01 |	   15 |
|*  2 |   COUNT STOPKEY 		|	    |	   1 |	      |     60 |00:00:00.01 |	   15 |
|   3 |    VIEW 			|	    |	   1 |	   60 |     60 |00:00:00.01 |	   15 |
|   4 |     TABLE ACCESS BY INDEX ROWID | PAGE	    |	   1 |	37380 |     60 |00:00:00.01 |	   15 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IDX_PAGE2 |	   1 |	   60 |     60 |00:00:00.01 |	    6 |
-------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=41)
   2 - filter(ROWNUM<=60)
   5 - access("OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1000 AND "OWNER"='SYS')
       filter("OWNER"='SYS')


27 rows selected.

取最后一页:

SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT /*+ index(a
idx_page_2) */ * FROM page where object_id >1000 and owner='SYS'
 order by object_id desc) A  
WHERE ROWNUM <= 36818  
)  
WHERE RN >= 36798

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	6vh4xftdt50jk, child number 0
-------------------------------------
SELECT *FROM ( SELECT A.*, ROWNUM RN FROM (SELECT /*+ index(a
idx_page_2) */ * FROM page where object_id >1000 and owner='SYS'  order
by object_id desc) A WHERE ROWNUM <= 36818 ) WHERE RN >= 36798

Plan hash value: 2750738262

-------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	    |	   1 |	      |     21 |00:00:00.04 |	 1267 |
|*  1 |  VIEW				|	    |	   1 |	36818 |     21 |00:00:00.04 |	 1267 |
|*  2 |   COUNT STOPKEY 		|	    |	   1 |	      |  36818 |00:00:00.03 |	 1267 |
|   3 |    VIEW 			|	    |	   1 |	36818 |  36818 |00:00:00.03 |	 1267 |
|   4 |     TABLE ACCESS BY INDEX ROWID | PAGE	    |	   1 |	37380 |  36818 |00:00:00.02 |	 1267 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IDX_PAGE2 |	   1 |	36818 |  36818 |00:00:00.01 |	  264 |
-------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=36798)
   2 - filter(ROWNUM<=36818)
   5 - access("OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1000 AND "OWNER"='SYS')
       filter("OWNER"='SYS')


27 rows selected.


SELECT *FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT /*+ index(a
idx_page_2) */ * FROM page where object_id >1000 and owner='SYS'
 order by object_id asc) A  
WHERE ROWNUM <= 20
)  
WHERE RN >= 0

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	gp877nr8m0psp, child number 0
-------------------------------------
SELECT *FROM ( SELECT A.*, ROWNUM RN FROM (SELECT /*+ index(a
idx_page_2) */ * FROM page where object_id >1000 and owner='SYS'  order
by object_id asc) A WHERE ROWNUM <= 20 ) WHERE RN >= 0

Plan hash value: 3059363140

------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	   |	  1 |	     |	   20 |00:00:00.01 |	   7 |
|*  1 |  VIEW			       |	   |	  1 |	  20 |	   20 |00:00:00.01 |	   7 |
|*  2 |   COUNT STOPKEY 	       |	   |	  1 |	     |	   20 |00:00:00.01 |	   7 |
|   3 |    VIEW 		       |	   |	  1 |	  20 |	   20 |00:00:00.01 |	   7 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PAGE	   |	  1 |	  20 |	   20 |00:00:00.01 |	   7 |
|*  5 |      INDEX RANGE SCAN	       | IDX_PAGE2 |	  1 |	     |	   20 |00:00:00.01 |	   4 |
------------------------------------------------------------------------------------------------------

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

   1 - filter("RN">=0)
   2 - filter(ROWNUM<=20)
   5 - access("OBJECT_ID">1000 AND "OWNER"='SYS' AND "OBJECT_ID" IS NOT NULL)
       filter("OWNER"='SYS')


27 rows selected.


所以 Oracle分页语句做的好的,应该是两头快 中间慢

本文地址：https://blog.csdn.net/zhaoyangjian724/article/details/107627738

如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！