欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

分页SQL模板

程序员文章站 2022-06-22 20:35:31
create table page as select * from dba_objects;BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'PAGE', estimate_percent => 100, ......



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

相关标签: sql 查询优化