原帖由 ZALBB 于 2010-7-1 10:48 发表
碰巧而已,ORDER BY 之后,主查询中头几条返回的值,在标量子查询里,若是一对一的关系,也不会出错。
我不这么认为,我的order by是写在最外面,ORACLE必须把这个结果集都获取到才能排序,而整个结果集在完整获取前,会报错。添加order by之后的执行计划。
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 268 | | 5598 (1)| 00:01:08 |
|* 1 | TABLE ACCESS FULL | ZB21 | 1 | 12 | | 9 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | ZB21 | 1 | 13 | | 9 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | ZB21 | 1 | 13 | | 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | ZB21 | 1 | 10 | | 9 (0)| 00:00:01 |
| 5 | SORT ORDER BY | | 1 | 268 | | 5598 (1)| 00:01:08 |
| 6 | NESTED LOOPS ANTI | | 1 | 268 | | 5597 (1)| 00:01:08 |
|* 7 | HASH JOIN SEMI | | 1 | 251 | | 5594 (1)| 00:01:08 |
|* 8 | HASH JOIN RIGHT SEMI | | 123 | 29520 | | 5414 (1)| 00:01:05 |
|* 9 | TABLE ACCESS FULL | ZB21 | 68 | 1020 | | 9 (0)| 00:00:01 |
| 10 | VIEW | AE10 | 146K| 31M| | 5403 (1)| 00:01:05 |
| 11 | SORT UNIQUE | | 146K| 19M| 49M| 5403 (75)| 00:01:05 |
| 12 | UNION-ALL | | | | | | |
| 13 | TABLE ACCESS FULL | AB01 | 33370 | 5311K| | 198 (3)| 00:00:03 |
| 14 | TABLE ACCESS FULL | AB60 | 172 | 12728 | | 3 (0)| 00:00:01 |
|* 15 | HASH JOIN | | 12658 | 2336K| | 171 (2)| 00:00:03 |
| 16 | TABLE ACCESS FULL | AB56 | 12769 | 723K| | 85 (2)| 00:00:02 |
| 17 | TABLE ACCESS FULL | AB56 | 12769 | 1633K| | 85 (2)| 00:00:02 |
| 18 | TABLE ACCESS FULL | AB54 | 100K| 11M| | 561 (2)| 00:00:07 |
| 19 | TABLE ACCESS FULL | AB58 | 1 | 224 | | 2 (0)| 00:00:01 |
|* 20 | INDEX FAST FULL SCAN | INDEX_AB02_AAE140_AAB051 | 5693 | 62623 | | 179 (3)| 00:00:03 |
|* 21 | TABLE ACCESS BY INDEX ROWID| AB07 | 1 | 17 | | 3 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX_AB07_AAZ010 | 1 | | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("AAB001"=:B1 AND "AAE100"='1')
2 - filter("AAB001"=:B1 AND "AAE100"='1')
3 - filter("AAB001"=:B1 AND "AAE100"='1')
4 - filter("AAB001"=:B1 AND "AAE100"='1')
7 - access("AAB001"="AE10"."AAB001")
8 - access("AAB001"="AE10"."AAB001")
9 - filter("AAE100"='1' AND NVL("AAE042",201007)>=201007 AND "AAE041"<=201007)
15 - access("A"."BAB001"="B"."AAB001")
20 - filter("AAE140"='21' AND "AAB051"='1')
21 - filter("AAA115"='0')
22 - access("AB07"."AAZ010"="AE10"."AAB001" AND "AAE002"=201007 AND "AAE140"='21')
filter("AAE140"='21' AND SUBSTR("AB07"."AAE140",1,1)='2')
从执行计划也可以看出来的。
id从1到4是不可能报错的(根据过滤的谓词判断)。
ID=6是ID=5的子,且ID=5只有一个子,这是一个典型的单独型的操作,子操作ID=6必须返回所有记录后才能向父操作ID=5提供数据。
单独型操作特点:1)父只有一个子 2)子只执行一次 3)子向父提供输出。
[本帖最后由 wei-xh 于 2010-7-1 12:55 编辑]