SORT ORDER BY STOPKEY
来源：互联网 发布：淘宝网瞄准镜 编辑：程序博客网 时间：2024/05/22 16:58
select * from ( select rownum as rn ,a.* from ( select * from t100 a order by object_id desc )a where rownum<=40 ) a where rn>=1SQL> SQL> select count(*) from t100; COUNT(*)---------- 3020896 BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SYSTEM', tabname => 'T100', estimate_percent => 100, method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree => 8, cascade => TRUE);END;---高级执行计划:11G：alter session set statistics_level=all; ---再运行SQLselect * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));select * from (select rownum as rn, a.* from (select * from t100 a order by object_id desc) a where rownum <= 40) a where rn >= 1SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID1h6g4u7aa6sqd, child number 1-------------------------------------select * from (select rownum as rn, a.* from (select * fromt100 a order by object_id desc) a where rownum <= 40) a wherern >= 1Plan hash value: 3078193190-------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers | Reads | OMem | 1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 1 | |40 |00:00:01.44 | 42846 | 111 | | | ||* 1 | VIEW || 1 | 40 |40 |00:00:01.44 | 42846 | 111 | | | ||* 2 | COUNT STOPKEY || 1 | |40 |00:00:01.44 | 42846 | 111 | | | || 3 | VIEW || 1 | 3020K|40 |00:00:01.44 | 42846 | 111 | | | ||* 4 | SORT ORDER BY STOPKEY|| 1 | 3020K|40 |00:00:01.44 | 42846 | 111 | 1186K| 567K| 1054K (0)|| 5 | TABLE ACCESS FULL | T100 | 1 | 3020K| 3020K|00:00:00.46 | 42846 | 111 | | | |-------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter(ROWNUM<=40)已选择26行。分页SQL返回40条记录,实际确需要访问 3020K 记录,显然不合理创建索引: create index t100_idx1 on t100(object_id);select * from ( select rownum as rn ,a.* from ( select * from t100 a order by object_id desc )a where rownum<=40 ) a where rn>=1SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID2umgv7yv9y4vu, child number 0-------------------------------------select * from (select rownum as rn, a.* from (select * fromt100 a order by object_id desc) a where rownum <= 40) a wherern >= 1Plan hash value: 3078193190----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers | OMem | 1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 1 | |40 |00:00:01.54 | 42846 | | | ||* 1 | VIEW || 1 | 40 |40 |00:00:01.54 | 42846 | | | ||* 2 | COUNT STOPKEY || 1 | |40 |00:00:01.54 | 42846 | | | || 3 | VIEW || 1 | 3020K|40 |00:00:01.54 | 42846 | | | ||* 4 | SORT ORDER BY STOPKEY|| 1 | 3020K|40 |00:00:01.54 | 42846 | 1186K| 567K| 1054K (0)|| 5 | TABLE ACCESS FULL | T100 | 1 | 3020K| 3020K|00:00:00.51 | 42846 | | | |----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter(ROWNUM<=40)已选择26行。没有走索引SQL> select * from (select rownum as rn, a.* from (select /*+ index(a t100_idx1)*/ * from t100 a order by object_id desc) a where rownum <= 40) a where rn >= 1 2 3 4 5 6 ;已选择40行。执行计划----------------------------------------------------------Plan hash value: 3078193190-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time|-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 40 | 8800 || 78467 (1)| 00:15:42 ||* 1 | VIEW || 40 | 8800 || 78467 (1)| 00:15:42 ||* 2 | COUNT STOPKEY ||||| ||| 3 | VIEW || 3020K| 596M|| 78467 (1)| 00:15:42 ||* 4 | SORT ORDER BY STOPKEY|| 3020K| 279M| 386M| 78467 (1)| 00:15:42 || 5 | TABLE ACCESS FULL | T100 | 3020K| 279M|| 11645 (1)| 00:02:20 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter(ROWNUM<=40)SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID1jx0gdw60vc3n, child number 1-------------------------------------select * from (select rownum as rn, a.* from (select /*+index(a t100_idx1)*/ * from t100 a where a.object_id is not null orderby object_id desc) a where rownum <= 40) a where rn >= 1Plan hash value: 2711774789----------------------------------------------------------------------------------------------------------------------------------| Id | Operation| Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 1 | | 40 |00:00:06.25 | 3027K| | | ||* 1 | VIEW| | 1 | 40 | 40 |00:00:06.25 | 3027K| | | ||* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:06.25 | 3027K| | | || 3 | VIEW | | 1 | 3020K| 40 |00:00:06.25 | 3027K| | | ||* 4 | SORT ORDER BY STOPKEY| | 1 | 3020K| 40 |00:00:06.25 | 3027K| 149M| 4122K| 132M (0)|| 5 | TABLE ACCESS BY INDEX ROWID| T100 | 1 | 3020K| 3020K|00:00:04.33 | 3027K| | | ||* 6 | INDEX FULL SCAN| T100_IDX1 | 1 | 3020K| 3020K|00:00:00.78 | 6697 | | | |----------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter(ROWNUM<=40) 6 - filter("A"."OBJECT_ID" IS NOT NULL)已选择28行。此时走索引了,但是仍旧访问了3020K ,显然不够优化SQL> select * from (select rownum as rn, a.* from (select /*+ index_desc(a t100_idx1)*/ * from t100 a where a.object_id is not null order by object_id desc) a where rownum <= 40) a where rn >= 1 2 3 4 5 6 ;SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID9pv0wyn2mk187, child number 0-------------------------------------select * from (select rownum as rn, a.* from (select /*+index_desc(a t100_idx1)*/ * from t100 a where a.object_id is not nullorder by object_id desc) a where rownum <= 40) a where rn >= 1Plan hash value: 4052825412---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |---------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 40 |00:00:00.01 | 46 | 1 ||* 1 | VIEW | | 1 | 40 | 40 |00:00:00.01 | 46 | 1 ||* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:00.01 | 46 | 1 || 3 | VIEW | | 1 | 40 | 40 |00:00:00.01 | 46 | 1 || 4 | TABLE ACCESS BY INDEX ROWID| T100 | 1 | 40 | 40 |00:00:00.01 | 46 | 1 ||* 5 | INDEX FULL SCAN DESCENDING| T100_IDX1 | 1 |3020K| 40 |00:00:00.01 | 6 | 1 |---------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 5 - filter("A"."OBJECT_ID" IS NOT NULL)已选择26行。此时访问了40条记录,是最优化的SORT ORDER BY STOPKEY Sorts a subset of the result according to the order by clause. Used for top-N queries if pipelined execution is not possible.排序结果的子集根据order by子句SORT ORDER BY STOPKEY 是指:排序过后取数据 走索引后返回 3020K|条 是要对3020K|排序,所以效率低 0 0
- SORT ORDER BY STOPKEY
- SORT ORDER BY STOPKEY
- 分页技术COUNT STOPKEY和SORT ORDER BY
- sql调优之执行计划之排序————SORT ORDER BY STOPKEY
- Order By与Sort
- hive sort by|order by
- hive order by，sort by，distributed by
- hive sql order by 与sort by
- hive Sort By/Order By/Cluster By/Distribute By
- order by sort by distribute by cluster by
- hive中的order by+sort by+distribute by+cluster by
- Hive Sort by/Order By/Cluster By/Distribute By
- hive sort by,order by ,distribute by,cluster by
- order by,sort by,distribute by,cluster by详解
- Hive order by/sort by/distribute by/cluster by作用
- hive中的order by , sort by, distribute by, cluster by
- Hive中order by，sort by，distribute by，cluster by
- hive中order by,distribute by,sort by,cluster by
- gulp 安装
- android五大布局之AbsoluteLayout(绝对布局)
- REACH
- FastDFS安装教程
- Catch That Cow
- SORT ORDER BY STOPKEY
- android五大布局之FrameLayout(框架布局)
- Redis的快照功能
- Blender Python 自学指导 视频教程
- android 机制之handler机制
- linux用户和组的基础概念
- fastdfs接入手册
- memset()函数及其作用
- 来自腾讯的高性能服务器架构思路
