请看看：这是不是10201 上的一个BUG？

ZALBB · 发表于 2006-8-30 11:09

最初由 atgc 发布
[B]我认为，相同的数据(源)，相同的SQL，应返回相同的结果集，从情理上讲也是这样
除非出错了，抑或是BUG
就像是一个脑筋急转弯的题目，问：1+1什么时候不等于2，答：做错的时候(BUG)
楼主没有提供from子句的表结构，也没有from子句的表的实际内容（没法提供)
所以也没法分析，光看这几条语句，我觉得没法下结论，因为我们都没调查过from子句后面的表的结构和实际的内容
这有个链接，也是讨论类似话题
http://www.itpub.net/621324.html

这样看看那？
select count(*) from
(); [/B]

在同一个用户下，一个是以inser开头，另外一个是select count(*)
开头。后面的查询语句select都一样。

在这种情况下，已经足够说明问题了。是否提供表结构，我觉得已经不重要。

再看看这个情况：
[php]
1 插入结果：
NCBI@BI>connect bi_data2/1@bi
Connected.
BI_DATA2@bi>truncate table fact_dailysale;

Table truncated.

BI_DATA2@bi>insert into fact_dailysale(csalestruid,pk_invcl,dbilldate,productline,nnumber)
  2  select stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline,
  3       sum(nvl(d.noutnum,0.0)) as nnumber
  4  from (select cgeneralhid,dbilldate
  5  from stg_ic_general_h,stg_fi_bd_cubasdoc,stg_fi_bd_cumandoc
  6  where stg_ic_general_h.ccustomerid = stg_fi_bd_cumandoc.pk_cumandoc
  7 and stg_fi_bd_cumandoc.pk_cubasdoc = stg_fi_bd_cubasdoc.pk_cubasdoc
  8 and stg_fi_bd_cubasdoc.drpnodeflag='N'
  9 and stg_ic_general_h.dr = 0
10 and stg_ic_general_h.fbillflag = '3'
11 and stg_ic_general_h.cbilltypecode = '4C'
12 and stg_ic_general_h.dbilldate >= '2006-03-01') e,
13 stg_ic_general_b d,stg_so_apply ,stg_so_apply_b ,stg_fi_bd_invbasdoc m
14 where e.cgeneralhid = d.cgeneralhid and d.cfirstbillbid = stg_so_apply_b.pk_apply_b
15    and stg_so_apply.pk_apply = stg_so_apply_b.pk_apply
16    and m.pk_invbasdoc = d.cinvbasid
17    and stg_so_apply.dr = 0 and stg_so_apply_b.dr = 0 and d.dr = 0
18    and d.flargess = 'N'
19  group by stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline;

3532 rows created.

2、升级到10202
BI_DATA2@bi> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0    Production
TNS for 64-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

3、查询结果
BI_DATA2@bi>select count(*) from
  2  (select stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline,
  3       sum(nvl(d.noutnum,0.0)) as nnumber
  4  from (select cgeneralhid,dbilldate
  5  from stg_ic_general_h,stg_fi_bd_cubasdoc,stg_fi_bd_cumandoc
  6  where stg_ic_general_h.ccustomerid = stg_fi_bd_cumandoc.pk_cumandoc
  7 and stg_fi_bd_cumandoc.pk_cubasdoc = stg_fi_bd_cubasdoc.pk_cubasdoc
  8 and stg_fi_bd_cubasdoc.drpnodeflag='N'
  9 and stg_ic_general_h.dr = 0
10 and stg_ic_general_h.fbillflag = '3'
11 and stg_ic_general_h.cbilltypecode = '4C'
12 and stg_ic_general_h.dbilldate >= '2006-03-01') e,
13 stg_ic_general_b d,stg_so_apply ,stg_so_apply_b ,stg_fi_bd_invbasdoc m
14 where e.cgeneralhid = d.cgeneralhid and d.cfirstbillbid = stg_so_apply_b.pk_apply_b
15    and stg_so_apply.pk_apply = stg_so_apply_b.pk_apply
16    and m.pk_invbasdoc = d.cinvbasid
17    and stg_so_apply.dr = 0 and stg_so_apply_b.dr = 0 and d.dr = 0
18    and d.flargess = 'N'
19  group by stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline);

  COUNT(*)
----------
   12370

4、加了hint /*+ rule  */ 之后，插入结果就与查询结果一致了。
------------------------------------------------------------------------------
BI_DATA2@bi>insert into fact_dailysale(csalestruid,pk_invcl,dbilldate,productline,nnumber)
  2  select /*+ RULE */stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline,
  3       sum(nvl(d.noutnum,0.0)) as nnumber
  4  from (select cgeneralhid,dbilldate
  5  from stg_ic_general_h2,stg_fi_bd_cubasdoc,stg_fi_bd_cumandoc
  6  where stg_ic_general_h2.ccustomerid = stg_fi_bd_cumandoc.pk_cumandoc
  7 and stg_fi_bd_cumandoc.pk_cubasdoc = stg_fi_bd_cubasdoc.pk_cubasdoc
  8 and stg_fi_bd_cubasdoc.drpnodeflag='N'
  9 and stg_ic_general_h2.dr = 0
10 and stg_ic_general_h2.fbillflag = '3'
11 and stg_ic_general_h2.cbilltypecode = '4C'
12 and stg_ic_general_h2.dbilldate >= to_date('2006-03-01','yyyy-mm-dd')) e,
13 stg_ic_general_b2 d,stg_so_apply ,stg_so_apply_b ,stg_fi_bd_invbasdoc m
14 where e.cgeneralhid = d.cgeneralhid and d.cfirstbillbid = stg_so_apply_b.pk_apply_b
15    and stg_so_apply.pk_apply = stg_so_apply_b.pk_apply
16    and m.pk_invbasdoc = d.cinvbasid
17    and stg_so_apply.dr = 0 and stg_so_apply_b.dr = 0 and d.dr = 0
18    and d.flargess = 'N'
19  group by stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline;

12370 rows created.

[/php]

注意，3个语句的select 部分都是一样的。

husthxd · 发表于 2006-8-30 13:05

bug也,太多了点把?
没有相关的补丁是最要命的.

ZALBB · 发表于 2006-9-7 11:09

今天ORACLE的技术人员让我测试了某个参数。下面是测试结果，根据此结果，结合在网上看到
与此BUG的类似的案例(http://www.freelists.org/archives/oracle-l/06-2005/msg00412.html) ，
在使用此参数后，结果都正确。

1、错误出现
SYS@bi>connect bi_data2/1@bi
Connected.
BI_DATA2@bi>truncate table fact_dailysale;

Table truncated.

BI_DATA2@bi>insert into fact_dailysale(csalestruid,pk_invcl,dbilldate,productline,nnumber)
  2  select stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline,
  3       sum(nvl(d.noutnum,0.0)) as nnumber
  4  from (select cgeneralhid,dbilldate
  5  from stg_ic_general_h,stg_fi_bd_cubasdoc,stg_fi_bd_cumandoc
  6  where stg_ic_general_h.ccustomerid = stg_fi_bd_cumandoc.pk_cumandoc
  7 and stg_fi_bd_cumandoc.pk_cubasdoc = stg_fi_bd_cubasdoc.pk_cubasdoc
  8 and stg_fi_bd_cubasdoc.drpnodeflag='N'
  9 and stg_ic_general_h.dr = 0
10 and stg_ic_general_h.fbillflag = '3'
11 and stg_ic_general_h.cbilltypecode = '4C'
12 and stg_ic_general_h.dbilldate >= '2006-03-01') e,
13 stg_ic_general_b d,stg_so_apply ,stg_so_apply_b ,stg_fi_bd_invbasdoc m
14 where e.cgeneralhid = d.cgeneralhid and d.cfirstbillbid = stg_so_apply_b.pk_apply_b
15    and stg_so_apply.pk_apply = stg_so_apply_b.pk_apply
16    and m.pk_invbasdoc = d.cinvbasid
17    and stg_so_apply.dr = 0 and stg_so_apply_b.dr = 0 and d.dr = 0
18    and d.flargess = 'N'
19  group by stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline;

3532 rows created.

2、错误被压制
BI_DATA2@bi>alter system set "_table_lookup_prefetch_size"=0 scope=spfile;

System altered.

BI_DATA2@bi>connect sys@bi as sysdba
Enter password: *
Connected.
SYS@bi>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

在本机启动数据库
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Sep 7 10:14:23 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SYS@NCBI>startup
ORACLE instance started.

Total System Global Area 4194304000 bytes
Fixed Size                2255464 bytes
Variable Size          312186264 bytes
Database Buffers       3875536896 bytes
Redo Buffers             4325376 bytes
Database mounted.
Database opened.
SYS@NCBI>

回到我开始测试的窗口
SYS@bi>connect bi_data2/1@bi
Connected.
BI_DATA2@bi>insert into fact_dailysale(csalestruid,pk_invcl,dbilldate,productline,nnumber)
  2  select stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline,
  3       sum(nvl(d.noutnum,0.0)) as nnumber
  4  from (select cgeneralhid,dbilldate
  5  from stg_ic_general_h,stg_fi_bd_cubasdoc,stg_fi_bd_cumandoc
  6  where stg_ic_general_h.ccustomerid = stg_fi_bd_cumandoc.pk_cumandoc
  7 and stg_fi_bd_cumandoc.pk_cubasdoc = stg_fi_bd_cubasdoc.pk_cubasdoc
  8 and stg_fi_bd_cubasdoc.drpnodeflag='N'
  9 and stg_ic_general_h.dr = 0
10 and stg_ic_general_h.fbillflag = '3'
11 and stg_ic_general_h.cbilltypecode = '4C'
12 and stg_ic_general_h.dbilldate >= '2006-03-01') e,
13 stg_ic_general_b d,stg_so_apply ,stg_so_apply_b ,stg_fi_bd_invbasdoc m
14 where e.cgeneralhid = d.cgeneralhid and d.cfirstbillbid = stg_so_apply_b.pk_apply_b
15    and stg_so_apply.pk_apply = stg_so_apply_b.pk_apply
16    and m.pk_invbasdoc = d.cinvbasid
17    and stg_so_apply.dr = 0 and stg_so_apply_b.dr = 0 and d.dr = 0
18    and d.flargess = 'N'
19  group by stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline;

12370 rows created.

3、错误再现
再把参数改回去。
BI_DATA2@bi>alter system set "_table_lookup_prefetch_size"=40 scope=spfile; -- 改回默认值

System altered.

退出数据库。
BI_DATA2@bi>disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

此处在另外窗口重启数据库。

回到最初的窗口，继续测试，发现该参数该回默认值后，错误出现
@>connect bi_data2/1@bi
Connected.
BI_DATA2@bi>insert into fact_dailysale(csalestruid,pk_invcl,dbilldate,productline,nnumber)
  2  select stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline,
  3       sum(nvl(d.noutnum,0.0)) as nnumber
  4  from (select cgeneralhid,dbilldate
  5  from stg_ic_general_h,stg_fi_bd_cubasdoc,stg_fi_bd_cumandoc
  6  where stg_ic_general_h.ccustomerid = stg_fi_bd_cumandoc.pk_cumandoc
  7 and stg_fi_bd_cumandoc.pk_cubasdoc = stg_fi_bd_cubasdoc.pk_cubasdoc
  8 and stg_fi_bd_cubasdoc.drpnodeflag='N'
  9 and stg_ic_general_h.dr = 0
10 and stg_ic_general_h.fbillflag = '3'
11 and stg_ic_general_h.cbilltypecode = '4C'
12 and stg_ic_general_h.dbilldate >= '2006-03-01') e,
13 stg_ic_general_b d,stg_so_apply ,stg_so_apply_b ,stg_fi_bd_invbasdoc m
14 where e.cgeneralhid = d.cgeneralhid and d.cfirstbillbid = stg_so_apply_b.pk_apply_b
15    and stg_so_apply.pk_apply = stg_so_apply_b.pk_apply
16    and m.pk_invbasdoc = d.cinvbasid
17    and stg_so_apply.dr = 0 and stg_so_apply_b.dr = 0 and d.dr = 0
18    and d.flargess = 'N'
19  group by stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline;

3532 rows created.

BI_DATA2@bi>

4、再修改参数，类似第1步的测试
BI_DATA2@bi>alter system set "_table_lookup_prefetch_size"=0 scope=spfile;

System altered.

BI_DATA2@bi>disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

此处在另外窗口重启数据库。

@>connect bi_data2/1@bi
Connected.
BI_DATA2@bi>insert into fact_dailysale(csalestruid,pk_invcl,dbilldate,productline,nnumber)
  2 select stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline,
  3       sum(nvl(d.noutnum,0.0)) as nnumber
  4 from (select cgeneralhid,dbilldate
  5 from stg_ic_general_h,stg_fi_bd_cubasdoc,stg_fi_bd_cumandoc
  6 where stg_ic_general_h.ccustomerid = stg_fi_bd_cumandoc.pk_cumandoc
  7    and stg_fi_bd_cumandoc.pk_cubasdoc = stg_fi_bd_cubasdoc.pk_cubasdoc
  8    and stg_fi_bd_cubasdoc.drpnodeflag='N'
  9    and stg_ic_general_h.dr = 0
10    and stg_ic_general_h.fbillflag = '3'
11    and stg_ic_general_h.cbilltypecode = '4C'
12    and stg_ic_general_h.dbilldate >= '2006-03-01') e,
13    stg_ic_general_b d,stg_so_apply ,stg_so_apply_b ,stg_fi_bd_invbasdoc m
14    where e.cgeneralhid = d.cgeneralhid and d.cfirstbillbid = stg_so_apply_b.pk_apply_b
15    and stg_so_apply.pk_apply = stg_so_apply_b.pk_apply
16    and m.pk_invbasdoc = d.cinvbasid
17    and stg_so_apply.dr = 0 and stg_so_apply_b.dr = 0 and d.dr = 0
18    and d.flargess = 'N'
19 group by stg_so_apply.csalecorpid,m.pk_invcl,d.dbizdate,d.productline;

12370 rows created.

BI_DATA2@bi>

说明，此参数的值直接影响到语句的查询结果，系统确实存在问题。

husthxd · 发表于 2006-9-7 14:06

汗.

ZALBB · 发表于 2006-9-7 15:03

最初由 husthxd 发布
[B]汗. [/B]

哈哈，长一下经验，以后碰上这种查询结果不一致的，
估计十有八九与此参数有关。

alanyang001 · 发表于 2007-1-17 17:00

Fenng · 发表于 2007-1-18 14:39

每次先对比一下 1202 修复的Bug列表吧

ZALBB · 发表于 2007-1-18 14:58

上两个礼拜发现此BUG也在AIX53、10202上出现，用同样的方法克服。
后据METALINK上的技术人员答复，在BUG将在10204版本上修复。

xwangxwang · 发表于 2007-1-21 07:05

Even 10g r2 still has bugs(defects) on optimizer. The execution plan would change even statistics is same between databases(same database for test,production, qa, developer).

ClearWind · 发表于 2007-6-9 15:47

我也遇到了这个BUG，但我是在10.2.0.2.0版中遇到的，相同的数据放到10.2.0.1.0中就没有

[php]
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as yykf

SQL>
SQL> --1、首先进行查询
SQL>    select '04' yhxh, TRIM(A.pay_serial_nbr) Pay_Serial_Nbr,B.DFID, B.FYBDID,
  2             DECODE(A.state, 1, A.PAY_AMOUNT, B.POWER_FEE) JE, B.LATE_FEE wyj,
  3             DECODE(A.State, 0, DEcode(nvl(B.FYBDID, '1'), '1', '2', '0'),A.state) TYPE,
  4             '0' AS STATE,'电力已记账银行未记账' AS SM,
  5             to_date('2007-06-04', 'YYYY-MM-DD') AS CHK_DATE,
  6             A.khbh, '未处理' clbz, A.YHWDH, A.YHGYH
  7       FROM (SELECT zwrq, Pay_Serial_Nbr, KHBH, STATE, PAY_AMOUNT, YHWDH, YHGYH
  8                FROM bank_serial C
  9                WHERE c.Bank_Serial = '04' AND c.zwrq = '2007-06-04'
10                AND c.PAY_SERIAL_NBR_OLD IS NULL AND c.pay_amount <> 0
11                AND NOT EXISTS
12                (SELECT 1 FROM bank_CHECK_SERIAL_DETAIL D
13                           WHERE D.Bank_Serial = '04'
14                              AND TRUNC(D.chk_date) = to_date('2007-06-04', 'YYYY-MM-DD')
15                              AND TRIM(D.Pay_Serial_Nbr) = TRIM(C.Pay_Serial_Nbr))) A,
16             bank_serial_detail B
17       WHERE A.Pay_Serial_Nbr = B.Pay_Serial_Nbr and a.zwrq = b.jzrq
18          and A.zwrq = '2007-06-04' and b.Bank_Serial = '04';

YHXH                            PAY_SERIAL_NBR          DFID          FYBDID                JE             WYJ TYPE                            STATE                         SM                            CHK_DATE KHBH       CLBZ                            YHWDH    YHGYH
-------------------------------- ------------------------- --------------- --------------- ---------- ----------------- -------------------------------- -------------------------------- -------------------------------- ----------- ------------ -------------------------------- ---------- ----------
04                            000015434                020000066274265 020000037982645    149.3          0.0000 0                               0                               电力已记账银行未记账          2007-6-4 0201744281 未处理                         371986284  A02

SQL> --2、将刚才的查询出来的结果插入到某个数据表中
SQL> insert into BANK_CHECK_RESULT(bank_serial,Pay_Serial_Nbr,dfid,fybdid,power_fee,late_fee,TYPE,
  2                                  state,sm,chk_date,khbh,clbz,yhwdh,yhgyh)
  3       select '04' yhxh, TRIM(A.pay_serial_nbr) Pay_Serial_Nbr,B.DFID, B.FYBDID,
  4             DECODE(A.state, 1, A.PAY_AMOUNT, B.POWER_FEE) JE, B.LATE_FEE wyj,
  5             DECODE(A.State, 0, DEcode(nvl(B.FYBDID, '1'), '1', '2', '0'),A.state) TYPE,
  6             '0' AS STATE,'电力已记账银行未记账' AS SM,
  7             to_date('2007-06-04', 'YYYY-MM-DD') AS CHK_DATE,
  8             A.khbh, '未处理' clbz, A.YHWDH, A.YHGYH
  9       FROM (SELECT zwrq, Pay_Serial_Nbr, KHBH, STATE, PAY_AMOUNT, YHWDH, YHGYH
10                FROM bank_serial C
11                WHERE c.Bank_Serial = '04' AND c.zwrq = '2007-06-04'
12                AND c.PAY_SERIAL_NBR_OLD IS NULL AND c.pay_amount <> 0
13                AND NOT EXISTS
14                (SELECT 1 FROM bank_CHECK_SERIAL_DETAIL D
15                           WHERE D.Bank_Serial = '04'
16                              AND TRUNC(D.chk_date) = to_date('2007-06-04', 'YYYY-MM-DD')
17                              AND TRIM(D.Pay_Serial_Nbr) = TRIM(C.Pay_Serial_Nbr))) A,
18             bank_serial_detail B
19       WHERE A.Pay_Serial_Nbr = B.Pay_Serial_Nbr and a.zwrq = b.jzrq
20          and A.zwrq = '2007-06-04' and b.Bank_Serial = '04';

31 rows inserted

SQL> --此时却出现了31条记录插入
SQL> rollback;

Rollback complete

SQL> --3、用相同的SELECT语句去创建一个临时表
SQL> create table tmp as
  2       select '04' yhxh, TRIM(A.pay_serial_nbr) Pay_Serial_Nbr,B.DFID, B.FYBDID,
  3             DECODE(A.state, 1, A.PAY_AMOUNT, B.POWER_FEE) JE, B.LATE_FEE wyj,
  4             DECODE(A.State, 0, DEcode(nvl(B.FYBDID, '1'), '1', '2', '0'),A.state) TYPE,
  5             '0' AS STATE,'电力已记账银行未记账' AS SM,
  6             to_date('2007-06-04', 'YYYY-MM-DD') AS CHK_DATE,
  7             A.khbh, '未处理' clbz, A.YHWDH, A.YHGYH
  8       FROM (SELECT zwrq, Pay_Serial_Nbr, KHBH, STATE, PAY_AMOUNT, YHWDH, YHGYH
  9                FROM bank_serial C
10                WHERE c.Bank_Serial = '04' AND c.zwrq = '2007-06-04'
11                AND c.PAY_SERIAL_NBR_OLD IS NULL AND c.pay_amount <> 0
12                AND NOT EXISTS
13                (SELECT 1 FROM bank_CHECK_SERIAL_DETAIL D
14                           WHERE D.Bank_Serial = '04'
15                              AND TRUNC(D.chk_date) = to_date('2007-06-04', 'YYYY-MM-DD')
16                              AND TRIM(D.Pay_Serial_Nbr) = TRIM(C.Pay_Serial_Nbr))) A,
17             bank_serial_detail B
18       WHERE A.Pay_Serial_Nbr = B.Pay_Serial_Nbr and a.zwrq = b.jzrq
19          and A.zwrq = '2007-06-04' and b.Bank_Serial = '04';

Table created

SQL> select count(1) from tmp;

  COUNT(1)
----------
      1

SQL> truncate table tmp;

Table truncated

SQL>
SQL> --4、还是往刚才创建的并TRUNCATE TABLE后的临时表中插入
SQL> insert into tmp
  2       select '04' yhxh, TRIM(A.pay_serial_nbr) Pay_Serial_Nbr,B.DFID, B.FYBDID,
  3             DECODE(A.state, 1, A.PAY_AMOUNT, B.POWER_FEE) JE, B.LATE_FEE wyj,
  4             DECODE(A.State, 0, DEcode(nvl(B.FYBDID, '1'), '1', '2', '0'),A.state) TYPE,
  5             '0' AS STATE,'电力已记账银行未记账' AS SM,
  6             to_date('2007-06-04', 'YYYY-MM-DD') AS CHK_DATE,
  7             A.khbh, '未处理' clbz, A.YHWDH, A.YHGYH
  8       FROM (SELECT zwrq, Pay_Serial_Nbr, KHBH, STATE, PAY_AMOUNT, YHWDH, YHGYH
  9                FROM bank_serial C
10                WHERE c.Bank_Serial = '04' AND c.zwrq = '2007-06-04'
11                AND c.PAY_SERIAL_NBR_OLD IS NULL AND c.pay_amount <> 0
12                AND NOT EXISTS
13                (SELECT 1 FROM bank_CHECK_SERIAL_DETAIL D
14                           WHERE D.Bank_Serial = '04'
15                              AND TRUNC(D.chk_date) = to_date('2007-06-04', 'YYYY-MM-DD')
16                              AND TRIM(D.Pay_Serial_Nbr) = TRIM(C.Pay_Serial_Nbr))) A,
17             bank_serial_detail B
18       WHERE A.Pay_Serial_Nbr = B.Pay_Serial_Nbr and a.zwrq = b.jzrq
19          and A.zwrq = '2007-06-04' and b.Bank_Serial = '04';

31 rows inserted

SQL> --依然是31条记录
SQL>
SQL> --5、再次单独执行
SQL>    select '04' yhxh, TRIM(A.pay_serial_nbr) Pay_Serial_Nbr,B.DFID, B.FYBDID,
  2             DECODE(A.state, 1, A.PAY_AMOUNT, B.POWER_FEE) JE, B.LATE_FEE wyj,
  3             DECODE(A.State, 0, DEcode(nvl(B.FYBDID, '1'), '1', '2', '0'),A.state) TYPE,
  4             '0' AS STATE,'电力已记账银行未记账' AS SM,
  5             to_date('2007-06-04', 'YYYY-MM-DD') AS CHK_DATE,
  6             A.khbh, '未处理' clbz, A.YHWDH, A.YHGYH
  7       FROM (SELECT zwrq, Pay_Serial_Nbr, KHBH, STATE, PAY_AMOUNT, YHWDH, YHGYH
  8                FROM bank_serial C
  9                WHERE c.Bank_Serial = '04' AND c.zwrq = '2007-06-04'
10                AND c.PAY_SERIAL_NBR_OLD IS NULL AND c.pay_amount <> 0
11                AND NOT EXISTS
12                (SELECT 1 FROM bank_CHECK_SERIAL_DETAIL D
13                           WHERE D.Bank_Serial = '04'
14                              AND TRUNC(D.chk_date) = to_date('2007-06-04', 'YYYY-MM-DD')
15                              AND TRIM(D.Pay_Serial_Nbr) = TRIM(C.Pay_Serial_Nbr))) A,
16             bank_serial_detail B
17       WHERE A.Pay_Serial_Nbr = B.Pay_Serial_Nbr and a.zwrq = b.jzrq
18          and A.zwrq = '2007-06-04' and b.Bank_Serial = '04';

YHXH                            PAY_SERIAL_NBR          DFID          FYBDID                JE             WYJ TYPE                            STATE                         SM                            CHK_DATE KHBH       CLBZ                            YHWDH    YHGYH
-------------------------------- ------------------------- --------------- --------------- ---------- ----------------- -------------------------------- -------------------------------- -------------------------------- ----------- ------------ -------------------------------- ---------- ----------
04                            000015434                020000066274265 020000037982645    149.3          0.0000 0                               0                               电力已记账银行未记账          2007-6-4 0201744281 未处理                         371986284  A02

SQL> --单独执行还是1条记录
SQL> rollback;

Rollback complete

SQL>
SQL> --6、修改一下NOT EXISTS中的语句再单独执行
SQL>    select '04' yhxh, TRIM(A.pay_serial_nbr) Pay_Serial_Nbr,B.DFID, B.FYBDID,
  2             DECODE(A.state, 1, A.PAY_AMOUNT, B.POWER_FEE) JE, B.LATE_FEE wyj,
  3             DECODE(A.State, 0, DEcode(nvl(B.FYBDID, '1'), '1', '2', '0'),A.state) TYPE,
  4             '0' AS STATE,'电力已记账银行未记账' AS SM,
  5             to_date('2007-06-04', 'YYYY-MM-DD') AS CHK_DATE,
  6             A.khbh, '未处理' clbz, A.YHWDH, A.YHGYH
  7       FROM (SELECT zwrq, Pay_Serial_Nbr, KHBH, STATE, PAY_AMOUNT, YHWDH, YHGYH
  8                FROM bank_serial C
  9                WHERE c.Bank_Serial = '04' AND c.zwrq = '2007-06-04'
10                AND c.PAY_SERIAL_NBR_OLD IS NULL AND c.pay_amount <> 0
11                AND NOT EXISTS
12                (SELECT 1 FROM bank_CHECK_SERIAL_DETAIL D
13                           WHERE D.Bank_Serial = '04'
14                              AND TRUNC(D.chk_date) =
15                              /*to_date('2007-06-04', 'YYYY-MM-DD') 将这个值改为与C表关联*/
16                                 to_date(c.zwrq, 'YYYY-MM-DD')
17                              AND TRIM(D.Pay_Serial_Nbr) = TRIM(C.Pay_Serial_Nbr))) A,
18             bank_serial_detail B
19       WHERE A.Pay_Serial_Nbr = B.Pay_Serial_Nbr and a.zwrq = b.jzrq
20          and A.zwrq = '2007-06-04' and b.Bank_Serial = '04';

YHXH                            PAY_SERIAL_NBR          DFID          FYBDID                JE             WYJ TYPE                            STATE                         SM                            CHK_DATE KHBH       CLBZ                            YHWDH    YHGYH
-------------------------------- ------------------------- --------------- --------------- ---------- ----------------- -------------------------------- -------------------------------- -------------------------------- ----------- ------------ -------------------------------- ---------- ----------
04                            000015434                020000066274265 020000037982645    149.3          0.0000 0                               0                               电力已记账银行未记账          2007-6-4 0201744281 未处理                         371986284  A02

SQL>
SQL> --7、修改一下NOT EXISTS中的语句再插入试试
SQL> insert into tmp
  2       select '04' yhxh, TRIM(A.pay_serial_nbr) Pay_Serial_Nbr,B.DFID, B.FYBDID,
  3             DECODE(A.state, 1, A.PAY_AMOUNT, B.POWER_FEE) JE, B.LATE_FEE wyj,
  4             DECODE(A.State, 0, DEcode(nvl(B.FYBDID, '1'), '1', '2', '0'),A.state) TYPE,
  5             '0' AS STATE,'电力已记账银行未记账' AS SM,
  6             to_date('2007-06-04', 'YYYY-MM-DD') AS CHK_DATE,
  7             A.khbh, '未处理' clbz, A.YHWDH, A.YHGYH
  8       FROM (SELECT zwrq, Pay_Serial_Nbr, KHBH, STATE, PAY_AMOUNT, YHWDH, YHGYH
  9                FROM bank_serial C
10                WHERE c.Bank_Serial = '04' AND c.zwrq = '2007-06-04'
11                AND c.PAY_SERIAL_NBR_OLD IS NULL AND c.pay_amount <> 0
12                AND NOT EXISTS
13                (SELECT 1 FROM bank_CHECK_SERIAL_DETAIL D
14                           WHERE D.Bank_Serial = '04'
15                              AND TRUNC(D.chk_date) =
16                              /*to_date('2007-06-04', 'YYYY-MM-DD') 将这个值改为与C表关联*/
17                                 to_date(c.zwrq, 'YYYY-MM-DD')
18                              AND TRIM(D.Pay_Serial_Nbr) = TRIM(C.Pay_Serial_Nbr))) A,
19             bank_serial_detail B
20       WHERE A.Pay_Serial_Nbr = B.Pay_Serial_Nbr and a.zwrq = b.jzrq
21          and A.zwrq = '2007-06-04' and b.Bank_Serial = '04';

1 row inserted

SQL> --只有一条了，正常
SQL> rollback;

Rollback complete

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production

SQL> --注意：这SELECT语句是某个过程中的一句，'2007-06-04'和'01'是在变量vDate和iYhdm
SQL>

下面是过程中的原始语句
insert into BANK_CHECK_RESULT(bank_serial,Pay_Serial_Nbr,dfid,fybdid,power_fee,late_fee,TYPE,
                              state,sm,chk_date,khbh,clbz,yhwdh,yhgyh)
select iYHDM, TRIM(A.pay_serial_nbr) Pay_Serial_Nbr,B.DFID, B.FYBDID, DECODE(A.state,1,
         A.PAY_AMOUNT,B.POWER_FEE) JE,B.LATE_FEE wyj,
         DECODE(A.State,0,DEcode(nvl(B.FYBDID,'1'),'1','2','0'),A.state) TYPE,
         '0' AS STATE, '电力已记账银行未记账' AS SM,
         to_date(vDate,'YYYY-MM-DD') AS CHK_DATE,A.khbh,'未处理',A.YHWDH,A.YHGYH
FROM
   (SELECT zwrq,Pay_Serial_Nbr,KHBH,STATE,PAY_AMOUNT,YHWDH,YHGYH FROM bank_serial C
      WHERE c.Bank_Serial=iYHDM
            AND c.zwrq=vDate
            AND c.PAY_SERIAL_NBR_OLD  IS  NULL
            AND c.pay_amount <> 0
            AND NOT EXISTS
               (SELECT 1 FROM bank_CHECK_SERIAL_DETAIL D
                  WHERE D.Bank_Serial=iYHDM AND TRUNC(D.chk_date)=to_date(vDate,'YYYY-MM-DD')
                  AND TRIM(D.Pay_Serial_Nbr)=TRIM(C.Pay_Serial_Nbr) )
   )A,
   bank_serial_detail B
WHERE  A.Pay_Serial_Nbr=B.Pay_Serial_Nbr and a.zwrq = b.jzrq
and A.zwrq=vDate and b.Bank_Serial=iYHDM;[/php]

[笔记] 请看看：这是不是10201 上的一个BUG？