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
]
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。