在oracle中，有时候在执行计划中看到sort关键字，就有人认为是做了排序，其实不一定。可以用下面的方法实验：
在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]
本文永久更新链接地址：