Oracle分页优化
来源:互联网 发布:c语言中char 编辑:程序博客网 时间:2024/05/21 09:39
分页语句优化
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;
/
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;
优化前:
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9w6p4hu1q1z4n, child number 0
-------------------------------------
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: 3486388599
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 20 | 20 |00:00:01.28 | 739 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:01.28 | 739 | | | |
| 3 | VIEW | | 1 | 22595 | 20 |00:00:01.28 | 739 | | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 22595 | 20 |00:00:01.28 | 739 | 1234K| 574K| 1096K (0)|
| 5 | COUNT | | 1 | | 22130 |00:00:01.22 | 739 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| PAGE | 1 | 22595 | 22130 |00:00:01.22 | 739 | | | |
|* 7 | INDEX RANGE SCAN | IDX_PAGE_3 | 1 | 22595 | 22130 |00:00:01.20 | 150 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
7 - access("OBJECT_ID">1000 AND "OWNER"='SYS' AND "OBJECT_ID" IS NOT NULL)
filter("OWNER"='SYS')
优化后:
现在加上INDEX_DESC HINT
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 5tqfh5zknzyfh, child number 0
-------------------------------------
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 | 7 |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 7 |
| 3 | VIEW | | 1 | 22595 | 20 |00:00:00.01 | 7 |
| 4 | COUNT | | 1 | | 20 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS BY INDEX ROWID | PAGE | 1 | 22595 | 20 |00:00:00.01 | 7 |
|* 6 | INDEX RANGE SCAN DESCENDING| IDX_PAGE_3 | 1 | 22595 | 20 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------
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')
现在ORACLE降序扫描了索引,扫描20条记录就停止了,这次消耗的逻辑读比之前消耗的都小100倍,所以分页最好用HINT INDEX_DESC/INDEX_ASC
ORDER BY 多个表 的列 ---分页语句 无法 优化
select * from a, b where ..... order by a.xx ;
这种 分页语句 优化 就让 A 作为驱动表
走NL 去驱动B
select ... from a left join b where .... order by b
这个分页 咋搞
外连接 B 无法 作为驱动表
只能改SQL
改成 ORDER BY A 不要ORDER BY B
执行计划出现 SORT ORDER BY STOPKEY 绝对不行 分页绝对慢
总结:
1、要看 分页语句 写法 有问题没
分页语句一定要包一层(套两层),否则无法STOP,意思是只扫描一页就停止,否则会有 STOPKEY
2、看ORDER BY 的 列
要看 ORDER BY 的 列 出现在 1个表 还是多个表
出现在 1个表上面 要看 是不是 有外连接
Order BY 的表要作为驱动表,要在Order BY列上建立组合索引,
3. ORDER BY 的列 全都 包含在 索引里面 并且 列顺序不能颠倒
where 选择性 很低的 就他放后面
where 列 放前面 走的是 ?
index range scan .......
where 列放后面 走的是 ?
INDEX FULL SCAN ....
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;
/
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;
优化前:
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9w6p4hu1q1z4n, child number 0
-------------------------------------
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: 3486388599
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 20 | 20 |00:00:01.28 | 739 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:01.28 | 739 | | | |
| 3 | VIEW | | 1 | 22595 | 20 |00:00:01.28 | 739 | | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 22595 | 20 |00:00:01.28 | 739 | 1234K| 574K| 1096K (0)|
| 5 | COUNT | | 1 | | 22130 |00:00:01.22 | 739 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| PAGE | 1 | 22595 | 22130 |00:00:01.22 | 739 | | | |
|* 7 | INDEX RANGE SCAN | IDX_PAGE_3 | 1 | 22595 | 22130 |00:00:01.20 | 150 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
7 - access("OBJECT_ID">1000 AND "OWNER"='SYS' AND "OBJECT_ID" IS NOT NULL)
filter("OWNER"='SYS')
优化后:
现在加上INDEX_DESC HINT
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 5tqfh5zknzyfh, child number 0
-------------------------------------
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 | 7 |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 7 |
| 3 | VIEW | | 1 | 22595 | 20 |00:00:00.01 | 7 |
| 4 | COUNT | | 1 | | 20 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS BY INDEX ROWID | PAGE | 1 | 22595 | 20 |00:00:00.01 | 7 |
|* 6 | INDEX RANGE SCAN DESCENDING| IDX_PAGE_3 | 1 | 22595 | 20 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------
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')
现在ORACLE降序扫描了索引,扫描20条记录就停止了,这次消耗的逻辑读比之前消耗的都小100倍,所以分页最好用HINT INDEX_DESC/INDEX_ASC
ORDER BY 多个表 的列 ---分页语句 无法 优化
select * from a, b where ..... order by a.xx ;
这种 分页语句 优化 就让 A 作为驱动表
走NL 去驱动B
select ... from a left join b where .... order by b
这个分页 咋搞
外连接 B 无法 作为驱动表
只能改SQL
改成 ORDER BY A 不要ORDER BY B
执行计划出现 SORT ORDER BY STOPKEY 绝对不行 分页绝对慢
总结:
1、要看 分页语句 写法 有问题没
分页语句一定要包一层(套两层),否则无法STOP,意思是只扫描一页就停止,否则会有 STOPKEY
2、看ORDER BY 的 列
要看 ORDER BY 的 列 出现在 1个表 还是多个表
出现在 1个表上面 要看 是不是 有外连接
Order BY 的表要作为驱动表,要在Order BY列上建立组合索引,
3. ORDER BY 的列 全都 包含在 索引里面 并且 列顺序不能颠倒
where 选择性 很低的 就他放后面
where 列 放前面 走的是 ?
index range scan .......
where 列放后面 走的是 ?
INDEX FULL SCAN ....
- 分页查询优化--oracle
- Oracle分页优化
- oracle 分页优化
- oracle分页优化 ---- rowid方法
- 优化oracle内存分页脚本
- oracle分页sql优化语句
- Oracle分页查询性能优化
- oracle 分页优化(stopkey)
- 优化让Oracle分页的加速
- Oracle的分页查询语句优化
- oracle百万级数据分页优化
- oracle分页查询千万级优化
- Oracle数据库分页查询语句与优化
- 使用oracle的olap函数优化分页查询
- 使用oracle的olap函数优化分页查询
- 今天看到一份资料上有讲到Oracle分页优化
- oracle count 百万级 分页查询记录总数、总条数优化
- 分页优化
- qmake简介
- 配置vim编译c/c++
- Cocos2d-x——场景切换的例子
- 周计划安排表
- smarty 学习
- Oracle分页优化
- cocos2dx 使用Texturepacker制作纹理相册以及使用
- MySql 数据库导入"Unknown command '\n'."错误解决办法
- python简单学习 if else
- schdule的工作流程
- 【Android】apk破解之“异常”破解
- cocos2d-x——CCLabelTTF(基本文本)
- linux下ifcfg-ethx的配置
- struts配置文件l中使用include 标签 引入另外一个在jar中的struts文件