分页语句优化
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 ....