Oracle中如何判断SQL是否真正做了排序

在Oracle中，有时候在执行计划中看到sort关键字，就有人认为是做了排序，其实不一定。可以用下面的方法实验：drop table test purge;create table test as select * from dba_objects;
exec dbms_stats.gather_table_stats（user,"test"）;

select /*+gg_test*/* from test order by object_id;
select /*+gg_test*/* from test order by object_id,object_name;
select /*+gg_test*/* from test order by object_id,object_name,object_type;

--#keys表示返回的结果中排序的字段数量
SQL>select projection
from v$sql_plan_statistics_all
where sql_id in（select sql_id from v$sql where
sql_text like"select /*+gg_test*/* from test order by object_id "）
and operation="SORT" and options="ORDER BY";
PROJECTION
-----------------------------------------------------------------------
（#keys=1） "TEST"."OBJECT_ID"[NUMBER,22], "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "TEST"."EDITION_NAME"[VARCHAR2,30],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19], "TEST"."CREATED"[DATE,7],
"TEST"."LAST_DDL_TIME"[DATE,7], "TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1], "TEST"."SECONDARY"[VARCHAR2,1],
"TEST"."NAMESPACE"[NUMBER,22]

SQL>select projection
from v$sql_plan_statistics_all
where sql_id in（select sql_id from v$sql where
sql_text="select /*+gg_test*/* from test order by object_id,object_name "）
and operation="SORT" and options="ORDER BY";
PROJECTION
-----------------------------------------------------------------------
（#keys=2） "TEST"."OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_NAME"[VARCHAR2,128], "TEST"."OWNER"[VARCHAR2,30],
"TEST"."NAMESPACE"[NUMBER,22], "TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "TEST"."EDITION_NAME"[VARCHAR2,30],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19], "TEST"."CREATED"[DATE,7],
"TEST"."LAST_DDL_TIME"[DATE,7], "TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1], "TEST"."SECONDARY"[VARCHAR2,1]

SQL>select projection
from v$sql_plan_statistics_all
where sql_id in（select sql_id from v$sql where
sql_text="select /*+gg_test*/* from test order by object_id,object_name,object_type "）
and operation="SORT" and options="ORDER BY";
PROJECTION
-----------------------------------------------------------------------
（#keys=3） "TEST"."OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."OBJECT_TYPE"[VARCHAR2,19], "TEST"."OWNER"[VARCHAR2,30], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "TEST"."EDITION_NAME"[VARCHAR2,30],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."SECONDARY"[VARCHAR2,1], "TEST"."CREATED"[DATE,7],
"TEST"."LAST_DDL_TIME"[DATE,7], "TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1] <！-- Baidu Button BEGIN -->更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址