用PL/SQL developer计划解释sql语句,请教一下分析结果里面SORT AGGREGATE,NESTED LOOPS等的含义
SQL语句:
select COUNT(*) from Trajector_schedule tss, Trajector_schedule tse,aftn_flightschedule
where
tse.flight_schedule_id = aftn_flightschedule.id
and tss.flight_schedule_id = aftn_flightschedule.id
and tss.routeseq<tse.routeseq
and tss.legseq=tse.legseq
and tse.istoday='0'
and tss.pointid='2051'
and tse.pointid='2100'
and aftn_flightschedule.days like '%6%'
and ((aftn_flightschedule.validperiodbegin<'2008-12-27' and
aftn_flightschedule.validperiodend>='2008-12-27')
or(aftn_flightschedule.validperiodbegin is null)
or( aftn_flightschedule.validperiodbegin is null))
order by tss.pasttime asc分析结果:
SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID OMC TRAJECTOR_SCHEDULE
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID OMC TRAJECTOR_SCHEDULE
INDEX RANGE SCAN OMC TRJECTOR_SCHEDULE_POINTID
TABLE ACCESS BY INDEX ROWID OMC AFTN_FLIGHTSCHEDULE
INDEX RANGE SCAN OMC AFTN_FLIGHTSCHEDULE_ID
INDEX RANGE SCAN OMC TRJECTOR_SCHEDULE_POINTID
SQL语句:
select COUNT(*) from Trajector_schedule tss, Trajector_schedule tse,aftn_flightschedule
where
tse.flight_schedule_id = aftn_flightschedule.id
and tss.flight_schedule_id = aftn_flightschedule.id
and tss.routeseq<tse.routeseq
and tss.legseq=tse.legseq
and tse.istoday='0'
and tss.pointid='2051'
and tse.pointid='2100'
and aftn_flightschedule.days like '%6%'
and ((aftn_flightschedule.validperiodbegin<'2008-12-27' and
aftn_flightschedule.validperiodend>='2008-12-27')
or(aftn_flightschedule.validperiodbegin is null)
or( aftn_flightschedule.validperiodbegin is null))
order by tss.pasttime asc分析结果:
SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID OMC TRAJECTOR_SCHEDULE
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID OMC TRAJECTOR_SCHEDULE
INDEX RANGE SCAN OMC TRJECTOR_SCHEDULE_POINTID
TABLE ACCESS BY INDEX ROWID OMC AFTN_FLIGHTSCHEDULE
INDEX RANGE SCAN OMC AFTN_FLIGHTSCHEDULE_ID
INDEX RANGE SCAN OMC TRJECTOR_SCHEDULE_POINTID
解决方案 »
- 输入命令卸载oracle,出现莫名其妙的问题
- 要往ORACLE DBA方向发展的应该如何开始
- Orcle查询问题
- app_folder.define_folder_block 的问题
- 请问oracle 10g 如何实现 9i 里面的 Enterprise Manager Console 功能
- PL/SQL中如何把数据集导出成固定的文本格式,谁有实例,帮帮我!!!
- 请教班主:proc程序(在win2000环境下VC++6.0编译)在连接远程ORACLE服务器时报错:ORA-01012:未登陆!
- ora-01114 将块写入文件 %s 出现IO错误 (块 # %S) SOS!!!
- 用sql语句如何把表中的一个字段从不能为空改为可为空?在线等。谢谢!
- 建好数库后服务中看不到OracleOraHome81ManagementServer
- 存储过程字符串操作求教(急)
- 关于嵌套表查询的问题
NESTED LOOPS是嵌套循环因为你的表有3个其中有一个先遍历再跟另外一个表查找是否匹配
1.首先LZ这个SQL就有问题。查询又没有group by,结果只会有1条记录,那么加个order by有什么意义呢?
2.看执行计划,这个SQL执行时其实没有排序的。SORT AGGREGATE是指出现聚合函数,比如sum(),count(),avg()等等。并不排序。
举个例子:
OPER@tl> select * from test; AAA
----------
1
2
3
4
5OPER@tl> set autot on
OPER@tl> select count(*) from test
2 order by aaa desc; COUNT(*)
----------
5
执行计划
----------------------------------------------------------
Plan hash value: 1950795681---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| TEST | 5 | 65 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从上面统计信息可知sorts (memory),sorts (disk)均为0.OPER@tl> select aaa,count(*) from test
2 group by aaa
3 order by aaa desc; AAA COUNT(*)
---------- ----------
5 1
4 1
3 1
2 1
1 1
执行计划
----------------------------------------------------------
Plan hash value: 2603667166---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 65 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST | 5 | 65 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processedOPER@tl>
这里出现了1个排序。