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

結果：再次查詢結果完全正確