说明：1）表ac01不含有字段aab001，单独执行报错。
SQL> select aab001 from ac01 where aac003='朱剑'；
select aab001 from ac01
   *
第 1 行出现错误:
ORA-00904: "AAB001": invalid identifier
已用时间:  00: 00: 00.03

可是如果按照如下写，竟然能查询出数据来。
select * from ab01 where aab001 in ( select aab001 from ac01 where aac003='朱剑');
输出略。。。。。

下面是执行计划：
SQL> explain plan for select * from ab01 where aab001 in ( select aab001 from ac01 where aac003='朱剑');

已解释。

已用时间:  00: 00: 00.12
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
--------
Plan hash value: 1162673798

--------------------------------------------------------------------------------------
| Id  | Operation       | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                |    1 | 163 | 72264 (1)| 00:14:28 |
|*  1 |  FILTER          |                |    |    |          |       |
| 2 | TABLE ACCESS FULL| AB01          | 33370 |  5311K| 198 (3)| 00:00:03 |
|*  3 | FILTER          |                |    |    |          |       |
|*  4 | INDEX RANGE SCAN| IDX_AC01_AAC003 |    2 | 14 |    3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ */ 0 FROM "AC01" "AC01" WHERE :B1=:B2 AND
            "AAC003"='朱剑'))
3 - filter(:B1=:B2)
4 - access("AAC003"='朱剑')

已选择19行。

已用时间:  00: 00: 00.55

这是为何呢？貌似从执行计划的谓词部分能看出，ORACLE做了EXISTS转换。可是还是觉得这个是不是ORACLE的一个BUG.

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0    Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
已用时间:  00: 00: 00.03

[ 本帖最后由 wei-xh 于 2010-5-31 21:22 编辑 ]

ITPub博客

这条语句简直匪夷所思啊

最新文章