今天上午遇到一个问题,经百思而不得其解,只好向各位高手求助,先化繁为简,简单构造一个测试数据:
----------初始化------------
Create Table A (
DM Char(4),
Anum Integer
);
Insert Into A(dm,anum) Values ('0001', 1);
Insert Into A(dm,anum) Values ('0002', 2);
Insert Into A(dm,anum) Values ('0003', 3);
Insert Into A(dm,anum) Values ('5000', 5000);
Insert Into A(dm,anum) Values ('5001', 5001);
Create Table B (
DM Char(4),
BZ Integer
);
Insert Into B(dm,bz) Values ('0001', 1);
Insert Into B(dm,bz) Values ('0002', 2);
Insert Into B(dm,bz) Values ('0003', 3);
Insert Into B(dm,bz) Values ('0004', 4);
Insert Into B(dm,bz) Values ('0005', 5);
------------- 查询语句 一 ----------------
select a.dm, a.Anum, b.bz from
a, (select * from b where dm = '' ) b
where a.DM=b.DM(+)
----------- 查询语句 一 执行计划 -------------
1. 9i 无结果
SELECT STATEMENT, GOAL = ALL_ROWS Cost=0 Cardinality=1 Bytes=38
FILTER
HASH JOIN OUTER Cost=7 Cardinality=5 Bytes=190
TABLE ACCESS FULL Object owner=FMIS0001 Object name=A Cost=3 Cardinality=5 Bytes=95
TABLE ACCESS FULL Object owner=FMIS0001 Object name=B Cost=3 Cardinality=5 Bytes=95
2. 10G 有结果
SELECT STATEMENT, GOAL = CHOOSE
MERGE JOIN OUTER
SORT JOIN
TABLE ACCESS FULL Object owner=FMIS0002 Object name=A
SORT JOIN
TABLE ACCESS FULL Object owner=FMIS0002 Object name=B
------------- 查询语句 二 ----------------
select a.dm, a.Anum, b.bz from
a, (select * from b where dm = '0003' ) b
where a.DM=b.DM(+)
9i , 10G 的查询结果均有且正确。
望各位兄台能指点一二,以开茅塞。
----------初始化------------
Create Table A (
DM Char(4),
Anum Integer
);
Insert Into A(dm,anum) Values ('0001', 1);
Insert Into A(dm,anum) Values ('0002', 2);
Insert Into A(dm,anum) Values ('0003', 3);
Insert Into A(dm,anum) Values ('5000', 5000);
Insert Into A(dm,anum) Values ('5001', 5001);
Create Table B (
DM Char(4),
BZ Integer
);
Insert Into B(dm,bz) Values ('0001', 1);
Insert Into B(dm,bz) Values ('0002', 2);
Insert Into B(dm,bz) Values ('0003', 3);
Insert Into B(dm,bz) Values ('0004', 4);
Insert Into B(dm,bz) Values ('0005', 5);
------------- 查询语句 一 ----------------
select a.dm, a.Anum, b.bz from
a, (select * from b where dm = '' ) b
where a.DM=b.DM(+)
----------- 查询语句 一 执行计划 -------------
1. 9i 无结果
SELECT STATEMENT, GOAL = ALL_ROWS Cost=0 Cardinality=1 Bytes=38
FILTER
HASH JOIN OUTER Cost=7 Cardinality=5 Bytes=190
TABLE ACCESS FULL Object owner=FMIS0001 Object name=A Cost=3 Cardinality=5 Bytes=95
TABLE ACCESS FULL Object owner=FMIS0001 Object name=B Cost=3 Cardinality=5 Bytes=95
2. 10G 有结果
SELECT STATEMENT, GOAL = CHOOSE
MERGE JOIN OUTER
SORT JOIN
TABLE ACCESS FULL Object owner=FMIS0002 Object name=A
SORT JOIN
TABLE ACCESS FULL Object owner=FMIS0002 Object name=B
------------- 查询语句 二 ----------------
select a.dm, a.Anum, b.bz from
a, (select * from b where dm = '0003' ) b
where a.DM=b.DM(+)
9i , 10G 的查询结果均有且正确。
望各位兄台能指点一二,以开茅塞。
a, (select * from b where dm = '' ) b
where a.DM=b.DM(+)
计划如下:一.
SELECT STATEMENT, GOAL = CHOOSE
MERGE JOIN OUTER
SORT JOIN
TABLE ACCESS FULL Object owner=FMIS_0001 Object name=A
SORT JOIN
TABLE ACCESS FULL Object owner=FMIS_0001 Object name=B
二.
SELECT STATEMENT, GOAL = ALL_ROWS Cost=0 Cardinality=1 Bytes=38
FILTER
HASH JOIN OUTER Cost=7 Cardinality=5 Bytes=190
TABLE ACCESS FULL Object owner=FMIS0001 Object name=A Cost=3 Cardinality=5 Bytes=95
TABLE ACCESS FULL Object owner=FMIS0001 Object name=B Cost=3 Cardinality=5 Bytes=95 2 者均无结果。
a, (select * from b where dm is null ) b
where a.DM=b.DM(+)
db2inst2@HASL>select *from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Productiondb2inst2@HASL>edit
已写入文件 afiedt.buf 1 select /*+CHOOSE*/ a.dm, a.Anum, b.bz from
2 a, (select * from b where dm = '') b
3* where a.DM=b.DM(+)
db2inst2@HASL>/DM ANUM BZ
---- ---------- ----------
0001 1
0002 2
0003 3
5000 5000
5001 5001db2inst2@HASL>edit
已写入文件 afiedt.buf 1 select a.dm, a.Anum, b.bz from
2 a, (select * from b where dm = '') b
3* where a.DM=b.DM(+)
db2inst2@HASL>/未选定行
tt@XIAOXIAO>select *from v$version;BANNER
--------------------------------------------------------------------------------
--------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
tt@XIAOXIAO>edit
已写入文件 afiedt.buf 1 select /*+choose*/ a.dm, a.Anum, b.bz from
2 a, (select * from b where dm = '' ) b
3* where a.DM=b.DM(+)
tt@XIAOXIAO>/DM ANUM BZ
-------- ---------- ----------
0001 1
0002 2
0003 3
5000 5000
5001 5001tt@XIAOXIAO>set autot on
tt@XIAOXIAO>/DM ANUM BZ
-------- ---------- ----------
0001 1
0002 2
0003 3
5000 5000
5001 5001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: CHOOSE
1 0 MERGE JOIN (OUTER)
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'A'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'B'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
563 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processedtt@XIAOXIAO>edit
已写入文件 afiedt.buf 1 select /*+all_rows*/ a.dm, a.Anum, b.bz from
2 a, (select * from b where dm = '' ) b
3* where a.DM=b.DM(+)
tt@XIAOXIAO>/DM ANUM BZ
-------- ---------- ----------
5001 5001
5000 5000
0003 3
0002 2
0001 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=5 Card=5 Bytes=190)
1 0 HASH JOIN (OUTER) (Cost=5 Card=5 Bytes=190)
2 1 TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=5 Bytes=95)
3 1 TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=1 Bytes=19)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
563 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
偶更倾向于觉得是bug,查查先...
.....
楼主是否说反了,第一条SQL语句在10g中是没有查询结果的,但是在9i中存在查询结果的
.....你说得对,我说反了,谢谢你! : )