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

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

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

转载于:http://blog.itpub.net/20976446/viewspace-722265/