用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交叉表
- mysql 的数据库,有没有办法转到oracle中,大家谈谈思路吧
- error number 求教
- 咨询达人,SQLLOADER在linux下的tirm函数用法,去掉文本列的空格符。
- 存储过程处理销量数据速度快,数据越大,速度越慢,这是为什么。
- 按以下这种设计方式,如何用SQL提出,请高人指点?
- 紧急求助!!由于误操作使用了drop table 使oracle8i中的数据丢失无数据库倍份,是否有办法恢复?
- 有谁知道oracle的主键信息存在哪个表里了?急!!!!!!!!!!
- 请问win2000professional能安装8。16吗?
- 使"种子字段"比如ID自增的触发器怎么写?谢谢
- 存储过程字符串操作求教(急)
- 关于嵌套表查询的问题
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个排序。