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

古城微笑少年丶 2022-08-06 05:26 151阅读 0赞

在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\]