技巧3：分页语句优化
分页语句，一般都有order by column desc/asc
分页语句的优化技巧：
1.分页SQL要想快最好走索引，根据order by asc/desc ，用hint index_asc/index_desc
强制它走索引INDEX FULL SCAN DESCENDING/INDEX RANGE SCAN DESCENDING
2.不要让Oracle扫描整个index，确保只扫描一部分索引块，然后回表就取得数据，然后
就count stopkey
3.一定要确保索引回表的时候不要在表上再去过滤，也就是说TABLE ACCESS BY INDEX ROWID
上面没*,不然就可能扫描整个索引块，根据where条件可以创建组合索引
(分区表要创建global index)
4.看执行计划的时候不要被执行计划的基数给骗了----它可能显示是有误差的
分页方法：
1.rownum
2.row_number over(order by column) as 行号
例子：
create table page as select * from dba_objects;
create index idx_page on page(object_id);
create index idx_page_1 on page(owner,object_id);
create index idx_page_2 on page(owner);
create index idx_page_3 on page(object_id,owner);
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;
/
比如这个SQL
select * from
(
select * from
(
select a.*,rownum rn
from page a
where object_id >1000 and owner='SYS'
order by object_id desc
) where rownum<=20
) where rn>=0;
看下高级执行计划:
hr@JSSPDG> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6c4km249bgfc0, child number 1
-------------------------------------
select * from ( select * from ( select a.*,rownum rn from page a where object_id >1000 and owner='SYS'
order by object_id desc ) where rownum<=20 ) where rn>=0
Plan hash value: 824209635
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 20 | 20 |00:00:03.49 | 700 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:03.49 | 700 | | | |
| 3 | VIEW | | 1 | 22611 | 20 |00:00:03.49 | 700 | | | |
|* 4 | SORT ORDER BY STOPKEY| | 1 | 22611 | 20 |00:00:03.49 | 700 | 1207K| 570K| 1072K (0)|
| 5 | COUNT | | 1 | | 22144 |00:00:02.68 | 700 | | | |
|* 6 | TABLE ACCESS FULL | PAGE | 1 | 22611 | 22144 |00:00:01.15 | 700 | | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
6 - filter(("OWNER"='SYS' AND "OBJECT_ID">1000))
26 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
700 consistent gets
0 physical reads
0 redo size
2919 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
因为OWNER='SYS' 以及OBJECT_ID>1000 选择性都很低，ORACLE走的是全表扫描，分页语句绝对不能让它走全表扫描，现在加个HINT
hr@JSSPDG> select * from ( select * from ( select /*+ index(a) */ a.*,rownum rn from page a where object_id >1000 and owner='SYS'
order by object_id desc ) where rownum<=20 ) where rn>=0;
Plan hash value: 4010810952
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 20 | 20 |00:00:08.34 | 627 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:08.34 | 627 | | | |
| 3 | VIEW | | 1 | 22611 | 20 |00:00:08.33 | 627 | | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 22611 | 20 |00:00:08.33 | 627 | 1207K| 570K| 1072K (0)|
| 5 | COUNT | | 1 | | 22144 |00:00:07.47 | 627 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| PAGE | 1 | 22611 | 22144 |00:00:05.99 | 627 | | | |
|* 7 | INDEX RANGE SCAN | IDX_PAGE_2 | 1 | 23035 | 23035 |00:00:02.15 | 51 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
6 - filter("OBJECT_ID">1000)
7 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
627 consistent gets
0 physical reads
0 redo size
2919 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
加了INDEX HINT之后，ORACLE走的是INDEX RANGE SCAN，但是INDEX RANGE SCAN选了23035 条数据，而不是只选择20条就停止，显然不是最优化的
现在加上INDEX_DESC HINT
select * from ( select * from ( select /*+ index_desc(a idx_page_3) */ a.*,rownum rn from page
a where object_id >1000 and owner='SYS' order by object_id desc ) where rownum<=20 ) where
rn>=0
Plan hash value: 3526010999
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 20 | 20 |00:00:00.01 | 8 |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 8 |
| 3 | VIEW | | 1 | 22611 | 20 |00:00:00.01 | 8 |
| 4 | COUNT | | 1 | | 20 |00:00:00.01 | 8 |
| 5 | TABLE ACCESS BY INDEX ROWID | PAGE | 1 | 22611 | 20 |00:00:00.01 | 8 |
|* 6 | INDEX RANGE SCAN DESCENDING| IDX_PAGE_3 | 1 | 22611 | 20 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
6 - access("OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1000 AND "OWNER"='SYS')
filter("OWNER"='SYS')
create index idx_page_3 on page(object_id,owner);
create index idx_page on page(object_id);
27 rows selected.
再次查看逻辑读:
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
2879 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
select * from ( select * from ( select /*+ index_desc(a idx_page)*/ a.*,rownum rn from page
a where object_id >1000 and owner='SYS' order by object_id desc ) where rownum<=20 ) where
rn>=0