oracle bug 203,oracle之BUG 7497640

出现的问题：查询语句A与查询语句B,所查询的2个SQL语句内容条件都是一样的，只是把查询的条件改变了下，查询出来的结果就大相径庭。

查询SQL语句A

select  c.id,c.logid,c.event 事件名称,c.lon 经度,c.lat 纬度,c.testtime 测试时间,l.logname 文档名称 from cdma_evt partition (t5) c,logmessage l

where  c.logid=l.logid and c.id in ('80080','36822','86008','72605','72802','27048','27097','27124','27128','89754','41912')

union all   select  c.id,c.logid,c.event 事件名称,c.lon 经度,c.lat 纬度,c.testtime 测试时间,l.logname 文档名称 from cdma_evt partition (t8) c,logmessage l

where  c.logid=l.logid and c.id in ('80080','36822','86008','72605','72802','27048','27097','27124','27128','89754','41912')

union all  select  c.id,c.logid,c.event 事件名称,c.lon 经度,c.lat 纬度,c.testtime 测试时间,l.logname 文档名称 from cdma_evt partition (t7) c,logmessage l

where  c.logid=l.logid  and  c.id in ('80080','36822','86008','72605','72802','27048','27097','27124','27128','89754','41912')

union all  select  c.id,c.logid,c.event 事件名称,c.lon 经度,c.lat 纬度,c.testtime 测试时间,l.logname 文档名称 from cdma_evt partition (t6) c,logmessage l

where  c.logid=l.logid  and c.id in ('80080','36822','86008','72605','72802','27048','27097','27124','27128','89754','41912') 查询出来是11条记录

查询SQL语句B

select  c.id,c.logid,c.event 事件名称,c.lon 经度,c.lat 纬度,c.testtime 测试时间,l.logname 文档名称 from cdma_evt partition (t5) c,logmessage l

where  c.logid=l.logid and c.id in ('80080','36822','86008','72605','72802','27048','27097','27124','27128','89754','41912')

union all   select  c.id,c.logid,c.event 事件名称,c.lon 经度,c.lat 纬度,c.testtime 测试时间,l.logname 文档名称 from cdma_evt partition (t6) c,logmessage l

where  c.logid=l.logid and c.id in ('80080','36822','86008','72605','72802','27048','27097','27124','27128','89754','41912')

union all  select  c.id,c.logid,c.event 事件名称,c.lon 经度,c.lat 纬度,c.testtime 测试时间,l.logname 文档名称 from cdma_evt partition (t7) c,logmessage l

where  c.logid=l.logid  and  c.id in ('80080','36822','86008','72605','72802','27048','27097','27124','27128','89754','41912')

union all  select  c.id,c.logid,c.event 事件名称,c.lon 经度,c.lat 纬度,c.testtime 测试时间,l.logname 文档名称 from cdma_evt partition (t8) c,logmessage l

where  c.logid=l.logid  and c.id in ('80080','36822','86008','72605','72802','27048','27097','27124','27128','89754','41912') 查询出来是2条记录

解决方案：   分别查询每条SQL语句之和也是11条记录。通过各种尝试都是无法解决。通过查询官方文档：

Bug 7497640  Wrong results with 'UNION ALL PUSHED PREDICATE' and partition pruning

Bug 7497640 - Wrong results with 'UNION ALL PUSHED PREDICATE' and partition pruning [ID 7497640.8]

--------------------------------------------------------------------------------

修改时间 20-NOV-2010     类型 PATCH     状态 PUBLISHED

Bug 7497640  Wrong results with 'UNION ALL PUSHED PREDICATE' and partition pruning

This note gives a brief overview bug 7497640.

The content was last updated on: 18-NOV-2010

Click here for details of each of the sections below.

Affects:

Product (Component) Oracle Server (Rdbms)

Range of versions believed to be affected Versions BELOW 11.2

Versions confirmed as being affected 11.1.0.7

10.2.0.4

Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in 11.2.0.1 (Base Release)

11.1.0.7.1 (Patch Set Update)

10.2.0.5 (Server Patch Set)

11.1.0.7 Patch 18 on Windows Platforms

10.2.0.4 Patch 40 on Windows Platforms

Symptoms: Related To:

Wrong Results

Partitioned Tables

_PUSH_JOIN_UNION_VIEW

_OPTIMIZER_PUSH_PRED_COST_BASED

_OPTIMIZER_COST_BASED_TRANSFORMATION

Optimizer Join Push Predicate (JPPD)

Description

Wrong results can occur from SQL which uses UNION ALL pushed predicate

along with partition pruning.

Rediscovery notes:

- The pushed join predicate is missing from the final plan

- The column in the join predicate is part of partition or sub-partition key

Workaround

Set any of the following parameters:

"_push_join_union_view" = false

"_optimizer_push_pred_cost_based" = false

"_optimizer_cost_based_transformation" = off

HOOKS CBO:JPPD "PARAMETER:_PUSH_JOIN_UNION_VIEW" PARAMETER:_OPTIMIZER_PUSH_PRED_COST_BASED PARAMETER:_OPTIMIZER_COST_BASED_TRANSFORMATION LIKELYAFFECTS

XAFFECTS_10.2.0.1 XAFFECTS_V10020001 AFFECTS=10.2.0.1 XAFFECTS_10.2.0.2 XAFFECTS_V10020002 AFFECTS=10.2.0.2 XAFFECTS_10.2.0.3 XAFFECTS_V10020003 AFFECTS=10.2.0.3

XAFFECTS_10.2.0.4 XAFFECTS_V10020004 AFFECTS=10.2.0.4 XAFFECTS_11.1.0.6 XAFFECTS_V11010006 AFFECTS=11.1.0.6 XAFFECTS_11.1.0.7 XAFFECTS_V11010007 AFFECTS=11.1.0.7

XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_PART TAG_WRONGRES PART WRONGRES FIXED_10.2.0.5 FIXED_11.1.0.7.1 FIXED_11.2.0.1 FIXED_WIN:A204P40

FIXED_WIN:B107P18

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem.

Always consult with Oracle Support for advice.

References

Bug:7497640 (This link will only work for PUBLISHED bugs)

Note:245840.1 Information on the sections in this article

最后修改了参数：

SQL> show parameter op

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_optimizer_cost_based_transformation string      OFF

audit_sys_operations                 boolean     FALSE

filesystemio_options                 string

object_cache_optimal_size            integer     102400

open_cursors                         integer     300

open_links                           integer     4

open_links_per_instance              integer     4

optimizer_capture_sql_plan_baselines boolean     FALSE

optimizer_dynamic_sampling           integer     2

optimizer_features_enable            string      11.2.0.1

optimizer_index_caching              integer     0

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj             integer     100

optimizer_mode                       string      ALL_ROWS

optimizer_secure_view_merging        boolean     TRUE

optimizer_use_invisible_indexes      boolean     FALSE

optimizer_use_pending_statistics     boolean     FALSE

optimizer_use_sql_plan_baselines     boolean     TRUE

plscope_settings                     string      IDENTIFIERS:NONE

plsql_optimize_level                 integer     2

read_only_open_delayed               boolean     FALSE

session_max_open_files               integer     10

SQL> alter system set "_optimizer_cost_based_transformation"=off

同时也可以升级，bug影响11.2.0.1你可以升级到11.2.0.3

结果：再次查询结果完全正确