执行计划导致错误的结果

最新推荐文章于 2022-08-03 22:23:28 发布

csdn201420142015

最新推荐文章于 2022-08-03 22:23:28 发布

阅读量116

点赞数

文章标签：数据库

执行计划导致不正确的结果．[@more@]

环境:
Oracle: 10.2.0.4
OS: Redhat Linux 2.6.9-42.ELsmp

构建测试环境:
CREATE TABLE AA

( A NUMBER(11,0),

B VARCHAR2(12),

C NUMBER(11,0)
) ;

CREATE TABLE AB
( D VARCHAR2(4) NOT NULL ENABLE,
ID NUMBER(11,0),
DATE1 DATE NOT NULL ENABLE,
B VARCHAR2(12) NOT NULL ENABLE,
C NUMBER(11,0) NOT NULL ENABLE
);

CREATE TABLE AC

( ID NUMBER(11,0) NOT NULL ENABLE,
E VARCHAR2(50)
) ;

CREATE TABLE AD
( F NUMBER(2,0) NOT NULL ENABLE,
E VARCHAR2(20) NOT NULL ENABLE
) ;

insert into AA values(2155,'000000000001',4617);

insert into AA values(2155,'000000000002',4617);

insert into AA values(2155,'000000000003',4618);
insert into AA values(2155,'000000000002',4618);
insert into ab values ('POS', NULL , to_date('2008-11-11 00:00:00','yyyy-mm-dd h24:mi:ss'), '000000000001', 4617);

insert into ab values ('POS', NULL , to_date('2008-11-11 00:00:00','yyyy-mm-dd

hh24:mi:ss'), '000000000001', 4617);

insert into ab values ('SL', 49425, to_date('2008-11-07 00:00:00','yyyy-mm-dd

hh24:mi:ss'), '000000000002', 4617);
insert into ab values ('POS', NULL , to_date('2008-11-11 00:00:00','yyyy-mm-dd

hh24:mi:ss'), '000000000003', 4618);

insert into ab values ('RC', 49426, to_date('2008-11-06 00:00:00','yyyy-mm-dd

hh24:mi:ss'), '000000000002', 4618);

insert into ac values(49425,'DAKVDEFF');

insert into ac values(49426,'DAKVDEFF');

insert into ad values(2,'DAKVDEFF');
insert into ad values(2,'DAKVDEFF');

测试的SQL：
SELECT
*
FROM(
select
CASE
WHEN t.COl2 > 0 THEN
sysdate
ELSE
t.Col3
END AS Col,
t.*
FROM (
SELECT
a.a AS Col1,
CASE
WHEN b.d = 'SL' THEN
CASE
WHEN b.ID IS NOT NULL AND c.e IS NOT NULL THEN
(SELECT NVL(MAX(f), 5) FROM ad WHERE e = c.e)
ELSE
5
END
WHEN b.d IN ('RSL', 'POS', 'DC', 'RC') THEN 0
ELSE 0
END AS Col2,
b.date1 as Col3,
b.b
FROM ab b,
aa a,
ac c
WHERE b.b = a.b
and b.c = a.c
and b.id is not null
and b.id = c.id ) t
)
where col1 = 2155 AND
TO_NUMBER(TO_CHAR(Col,'YYYYMMDD')) <= 20081105
AND Col1 IN (select 2155 from dual)
/
实际上是两条SQL，但为节省版面，只给出上面一条，另一条跟上面差不多，唯一的不同是：另一条把WHERE条件"AND Col1 IN (select 2155 from dual)"注释了.
从逻辑上分析,我们看不出什么异样来,也就是说,这两条SQL从逻辑上来讲,两者是一样的,执行这两条SQL得出的结果按理也是一样的,但其实不一样的:
没有注释掉"AND Col1 IN (select 2155 from dual)",得到的结果是：
2008-11-06 00:00:00 2155 0 2008-11-06 00:00:00 000000000002
2009-05-26 13:07:13 2155 2 2008-11-07 00:00:00 000000000002
注释掉刚才的"AND Col1 IN (select 2155 from dual)",则得到零条记录. 从表面上来看，应是dual在这里里惹事生非，这不能不说是Oracle的一个bug,查看各自己的执行计划：
*********************Plan with dual*********************************************
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 82 | 10 (20)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS FULL | AD | 2 | 24 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 12 | | |
|* 4 | TABLE ACCESS FULL | AD | 2 | 24 | 2 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 82 | 10 (20)| 00:00:01 |
| 6 | HASH UNIQUE | | 1 | 66 | 10 (20)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 66 | 9 (12)| 00:00:01 |
| 8 | MERGE JOIN CARTESIAN| | 7 | 245 | 6 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 2 | 28 | 4 (0)| 00:00:01 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | AC | 2 | 28 | 2 (0)| 00:00:01 |
| 12 | BUFFER SORT | | 4 | 84 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | AA | 4 | 84 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | AB | 2 | 62 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

2 - filter("E"=:B1)
4 - filter("E"=:B1)
7 - access("B"."B"="A"."B" AND "B"."C"="A"."C" AND "B"."ID"="C"."ID")
13 - filter("A"."A"=2155)
14 - filter("B"."ID" IS NOT NULL)

***********************Plan without dual***************************************
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS FULL | AD | 2 | 24 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 12 | | |
|* 4 | TABLE ACCESS FULL | AD | 2 | 24 | 2 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | HASH JOIN | | 1 | 66 | 7 (15)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN| | 7 | 245 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | AC | 2 | 28 | 2 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 4 | 84 | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | AA | 4 | 84 | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | AB | 2 | 62 | 2 (0)| 00:00:01 |
| 12 | SORT AGGREGATE | | 1 | 12 | | |
|* 13 | TABLE ACCESS FULL | AD | 2 | 24 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - filter("E"=:B1)
4 - filter("E"=:B1)
5 - filter(TO_NUMBER(TO_CHAR(CASE WHEN CASE "B"."D" WHEN 'SL' THEN
CASE WHEN ("B"."ID" IS NOT NULL AND "C"."E" IS NOT NULL) THEN (SELECT
NVL(MAX("F"),5) FROM "AD" "AD" WHERE "E"=:B1) ELSE 5 END WHEN 'RSL'
THEN 0 WHEN 'POS' THEN 0 WHEN 'DC' THEN 0 WHEN 'RC' THEN 0 ELSE 0 END
>0 THEN SYSDATE@! ELSE "B"."DATE1" END ,'YYYYMMDD'))<=20081105)
6 - access("B"."B"="A"."B" AND "B"."C"="A"."C" AND "B"."ID"="C"."ID")
10 - filter("A"."A"=2155)
11 - filter("B"."ID" IS NOT NULL)
13 - filter("E"=:B1)

从上面的执行计划来看，有dual的条件里，把下面条件漏了：
5 - filter(TO_NUMBER(TO_CHAR(CASE WHEN CASE "B"."D" WHEN 'SL' THEN
CASE WHEN ("B"."ID" IS NOT NULL AND "C"."E" IS NOT NULL) THEN (SELECT
NVL(MAX("F"),5) FROM "AD" "AD" WHERE "E"=:B1) ELSE 5 END WHEN 'RSL'
THEN 0 WHEN 'POS' THEN 0 WHEN 'DC' THEN 0 WHEN 'RC' THEN 0 ELSE 0 END
>0 THEN SYSDATE@! ELSE "B"."DATE1" END ,'YYYYMMDD'))<=20081105)

如果加上相应的提示no_unnest/no_merge 在dual那个了查询里，或者alter session set "_complex_view_merging"=false, 这样也会执行上面的过滤条件.
有几次10G上SQL运行碰到类似的问题，10G更倾向于把子查询通过相应的转换，跟主表做相应的join,达到优化，而把filter功能屏蔽。但在有些情况下filter效率还是比join高的.

后来在metalink上开了一个SR,Oracle也开发出了相应的补丁8240658。

这里有点还没明白的是，在执行计划里，为什么步骤2，4要扫描AD表.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45188/viewspace-1022515/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45188/viewspace-1022515/