Oracle中如何判断SQL是否真正做了排序
Jun 07, 2016 pm 04:42 PM在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]
本文永久更新链接地址:

熱門文章

熱門文章

熱門文章標籤

記事本++7.3.1
好用且免費的程式碼編輯器

SublimeText3漢化版
中文版,非常好用

禪工作室 13.0.1
強大的PHP整合開發環境

Dreamweaver CS6
視覺化網頁開發工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

在 Linux 中運行 MySQl(有/沒有帶有 phpmyadmin 的 podman 容器)

哪些流行的MySQL GUI工具(例如MySQL Workbench,PhpMyAdmin)是什麼?
