-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 3800 | 244 (4)| 00:00:03 |
|* 1 | VIEW | | 20 | 3800 | 244 (4)| 00:00:03 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 40 | 7080 | 244 (4)| 00:00:03 |
|* 4 | SORT ORDER BY STOPKEY | | 40 | 7080 | 244 (4)| 00:00:03 |
| 5 | VIEW | | 40 | 7080 | 243 (3)| 00:00:03 |
| 6 | UNION-ALL | | | | | |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | VIEW | | 1650 | 285K| 238 (3)| 00:00:03 |
| 9 | TABLE ACCESS BY INDEX ROWID| A | 1650 | 156K| 238 (3)| 00:00:03 |
|* 10 | INDEX FULL SCAN DESCENDING| IDX_A | 1650 | | 199 (4)| 00:00:03 |
|* 11 | COUNT STOPKEY | | | | | |
| 12 | VIEW | | 20 | 3540 | 5 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| B | 20 | 1940 | 5 (0)| 00:00:01 |
|* 14 | INDEX FULL SCAN DESCENDING| IDX_B | 1650 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
7 - filter(ROWNUM<=20)
10 - filter("CREATED">TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LAST_DDL_TIME" 11 - filter(ROWNUM<=20)
14 - filter("CREATED">TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LAST_DDL_TIME"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2457 bytes sent via SQL*Net to client
411 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
逻辑读整整下降了60倍。
现在根据这个案例来谈谈SQL分页的优化思路，SQL分页通常要进行排序，比如select xxxx from t where 条件order by ......
优化分页SQL可以重点关注order by 这个条件，写SQL的时候要让ORACLE 对order by 列 上的索引进行有序的扫描，然后根据stopkey 停止，也就是不要把索引的block全都给扫描了，应该扫描一部分block就停止
摘自 落落的专栏 专注SQL调优 性能调优
|