Oracle order by 排序优化

order by 排序对性能的影响-***********************************案例演示-***********************************alter system flush shared_pool;set autotrace traceonly explain stat;select * from t3 where sid>90 ;执行计划----------------------------------------------------------Plan hash value: 4161002650--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 330 | 2 （0）| 00:00:01 ||* 1 | TABLE ACCESS FULL| T3 | 10 | 330 | 2 （0）| 00:00:01 |--------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 1 - filter（"SID">90）Note----- - dynamic sampling used for this statement （level=2）统计信息---------------------------------------------------------- 10 recursive calls 4 db block gets 10 consistent gets 0 physical reads 496 redo size 818 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts （memory） 0 sorts （disk） 10 rows processedselect * from t3 where sid>90 order by sid desc;执行计划----------------------------------------------------------Plan hash value: 1749037557---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 330 | 3 （34）| 00:00:01 || 1 | SORT ORDER BY | | 10 | 330 | 3 （34）| 00:00:01 ||* 2 | TABLE ACCESS FULL| T3 | 10 | 330 | 2 （0）| 00:00:01 |---------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 2 - filter（"SID">90）Note----- - dynamic sampling used for this statement （level=2）统计信息---------------------------------------------------------- 9 recursive calls 4 db block gets 9 consistent gets 1 physical reads 540 redo size 818 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts （memory） --有排序 0 sorts （disk） 10 rows processed可以看出CPU发生变化,如果排序语句很多的情况下,性能影响更大.-***********************************解决办法-***********************************create index index_sid on t3（sid desc）;exec dbms_stats.gather_table_stats（"SYS","T3",cascade=>TRUE）;select * from t3 where sid>90 order by sid desc;执行计划---------------------------------------------------------lan hash value: 243714934---------------------------------------------------------------------------------------- Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |---------------------------------------------------------------------------------------- 0 | SELECT STATEMENT | | 10 | 140 | 2 （0）| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| T3 | 10 | 140 | 2 （0）| 00:00:01 |* 2 | INDEX RANGE SCAN | INDEX_SID | 1 | | 1 （0）| 00:00:01 |----------------------------------------------------------------------------------------redicate Information （identified by operation id）:-------------------------------------------------- 2 - access（SYS_OP_DESCEND（"SID"）<HEXTORAW（"3EA4FF"）） filter（SYS_OP_UNDESCEND（SYS_OP_DESCEND（"SID"））>90）ote---- - SQL plan baseline "SQL_PLAN_78qgapzz4mwhwd7223dec" used for this statement统计信息--------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 818 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts （memory） --无排序 0 sorts （disk） 10 rows processed更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12初始化Oracle用户以及表空间的bash shell脚本IMP/EXP数据迁移（二）相关资讯 Oracle排序 oracle order by

Oracle中如何判断SQL是否真正做了（12/26/2014 19:34:03）
Oracle 排序使用 nulls first 或（07/08/2013 14:07:03）
Oracle使用order by排序null值如何（05/26/2013 13:44:08）

Oracle数据库中的ROWNUM和ORDER BY （11/12/2014 19:15:14）
Oracle中去重复+排序（06/09/2013 09:46:03）
Oracle排序合并连接（02/13/2013 14:47:46）

本文评论查看全部评论（0）