分页SQL模板

程序员文章站 2022-03-03 19:56:43
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模板

对angular 实时更新模板视图的方法$apply详解

详解js模板引擎art template数组渲染的方法

mongodb数据库及数据分页-个人文章-SegmentFault思否

sql server 40错误解决

《SQL Server》之表的创建和管理

sql语句查询之一个学生在某一年的所有成绩与查询某一个班的高等数学的成绩（代码实例）

SQL2008删除堵塞进程sp_who

Sql Server分区表，清空单个分区的快捷方法

SQL中两数相除保留两位小数的实现方法

SQL创建临时表
