分页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