Oracle中如何判断SQL是否真正做了排序
来源:互联网 发布:mac怎么设置壁纸 编辑:程序博客网 时间:2024/05/16 07:25
在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]
0 0
- Oracle中如何判断SQL是否真正做了排序
- 如何判断是否实施了Oracle RAC?
- 如何判断团队是否真正实施Scrum?
- 如何判断系统中是否安装了某种字体
- Android开发中如何判断是否安装了某个程序
- js中如何判断按钮是否被点击了
- Oracle 中如何判断一个字符串是否为数字
- Oracle 中如何判断时间类型Date是否为空
- Oracle中如何判断是否为数字函数
- 如何判断Oracle中某个字段的值是否重复
- Oracle 中如何判断时间类型Date是否为空
- Oracle 中如何判断一个字符串是否为数字
- Oracle 中如何判断一个字符串是否为数字
- 如何判断存储过程中,T-SQL语句是否执行?
- VFP中如何判断某个用户是否连上SQL
- SQL——Sql_Server中如何判断表中某列是否存在
- sql server中如何判断游标是否存在
- SQL Server中如何判断一个数据库是否存在
- 学习与就业:深度分析IT行业前景
- 虚拟机复制以后eth变化的修改方法
- NYOJ - 716 - River Crossing --第六届河南省程序设计大赛 (简单DP!!)
- 连表查询和子查询
- 标准I/O库_兼_stringstream使用
- Oracle中如何判断SQL是否真正做了排序
- 14个Xcode中常用的快捷键操作
- Vmware 开机启动虚拟机系统
- connect fail
- LeetCode:Minimum Path Sum
- flex actionscript 监听浏览器 刷新 后退 关闭 并且给出提示,刷新 离开提示
- Android 升级版本 如何保存旧数据
- 用代码实现将activity标题设置成图片
- 工作流书籍