有應用人員反映某套Linux上的11.2.0.1資料庫系統中出現了UNION ALL後傳回的結果集不正确的問題,我們具體分析下出現問題的其中一條語句:
<a href="http://blog.51cto.com/maclean/1277523#">?</a>
<code>SELECT</code> <code>MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.DESCRIPTION,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.AVAILABILITY_TYPE,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.MATERIAL_ACCOUNT,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.MATERIAL_OVERHEAD_ACCOUNT,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.RESOURCE_ACCOUNT,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.OVERHEAD_ACCOUNT,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.OUTSIDE_PROCESSING_ACCOUNT,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.ASSET_INVENTORY,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.EXPENSE_ACCOUNT,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.ENCUMBRANCE_ACCOUNT,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.ATTRIBUTE3,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.ATTRIBUTE5,</code>
<code> </code><code>WORKFLOW_START_TIMES.WORKFLOW_START_TIME</code>
<code> </code><code>FROM</code> <code>REPEMEAERP.MTL_SECONDARY_INVENTORIES,</code>
<code> </code><code>REPEMEAERP.WORKFLOW_START_TIMES</code>
<code> </code><code>WHERE</code> <code>MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT ></code>
<code> </code><code>TO_DATE(</code><code>'01/01/1900 00:00:00'</code><code>, </code><code>'MM/DD/YYYY HH24:MI:SS'</code><code>)</code>
<code> </code><code>AND</code> <code>MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=</code>
<code> </code><code>AND</code> <code>WORKFLOW_START_TIMES.WORKFLOW_NAME =</code>
<code> </code><code>LTRIM(RTRIM(</code><code>'w_int_FreqBatch_EMEA'</code><code>))</code>
<code>/*以上是QUERY A*/</code>
<code>UNION</code> <code>ALL</code>
<code>/*以下是QUERY B*/</code>
<code>SELECT</code> <code>DISTINCT</code> <code>'WORKORDERS'</code><code>,</code>
<code> </code><code>MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,</code>
<code> </code><code>'WORK ORDERS WITH WIP AS CATEGORY VALUE'</code><code>,</code>
<code> </code><code>1,</code>
<code> </code><code>0,</code>
<code> </code><code>'MOI'</code><code>,</code>
<code> </code><code>'0'</code><code>,</code>
<code> </code><code>WORKFLOW_START_TIMES.WORKFLOW_START_TIME</code>
<code> </code><code>FROM</code> <code>REPEMEAERP.MTL_SECONDARY_INVENTORIES, EIMMAINT.WORKFLOW_START_TIMES</code>
<code>/</code>
<code>138 </code><code>rows</code> <code>selected.</code>
以上查詢語句中，QUERY A部分(也就是UNION ALL之前的SELECT語句)單獨查詢時傳回傳回69條記錄，QUERY B部分單獨查詢時傳回15記錄，UNION ALL後傳回的結果卻是138條記錄，而非84條記錄。實際上這套系統也是最近才從10g遷移到11gr2上，之前在10g中同樣的應用沒有出過類似的問題，可以猜測是11g中新引入的某種特性存在可能引發wrong result的Bug。 具體思路雖然有了，但仍無法确定問題的關鍵所在；我們來看看該SQL的執行計劃:
<code>-----------------------------------------------------------------------------------------------------------------</code>
<code>| Id | Operation | </code><code>Name</code> <code>| </code><code>Rows</code> <code>| Bytes | Cost (%CPU)| </code><code>Time</code> <code>|</code>
<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 7 | 2443 | 52 (0)| 00:00:01 |</code>
<code>| 1 | NESTED LOOPS | | 7 | 2443 | 52 (0)| 00:00:01 |</code>
<code>|* 2 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| WORKFLOW_START_TIMES | 1 | 29 | 48 (0)| 00:00:01 |</code>
<code>| 3 | </code><code>VIEW</code> <code>| VW_JF_SET$9BAED2EA | 1 | 320 | 4 (0)| 00:00:01 |</code>
<code>| 4 | </code><code>UNION</code> <code>ALL</code> <code>PUSHED PREDICATE | | | | | |</code>
<code>|* 5 | FILTER | | | | | |</code>
<code>| 6 | </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| MTL_SECONDARY_INVENTORIES | 3 | 336 | 2 (0)| 00:00:01 |</code>
<code>|* 7 | </code><code>INDEX</code> <code>RANGE SCAN | IDX_MTL_SECONDARY_INVENTORIES | 1 | | 1 (0)| 00:00:01 |</code>
<code>|* 8 | FILTER | | | | | |</code>
<code>| 9 | </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| MTL_SECONDARY_INVENTORIES | 3 | 36 | 2 (0)| 00:00:01 |</code>
<code>|* 10 | </code><code>INDEX</code> <code>RANGE SCAN | IDX_MTL_SECONDARY_INVENTORIES | 1 | | 1 (0)| 00:00:01 |</code>
<code>Predicate Information (identified </code><code>by</code> <code>operation id):</code>
<code>---------------------------------------------------</code>
<code>2 - filter(</code><code>"WORKFLOW_START_TIMES"</code><code>.</code><code>"WORKFLOW_NAME"</code><code>=</code><code>'w_int_FreqBatch_EMEA'</code><code>)</code>
<code>5 - filter(TO_DATE(</code><code>' 1900-01-01 00:00:00'</code><code>, </code><code>'syyyy-mm-dd</code>
<code>hh24:mi:ss'</code><code>)<</code><code>"WORKFLOW_START_TIMES"</code><code>.</code><code>"WORKFLOW_START_TIME"</code><code>) 7 - access(</code><code>"MTL_SECONDARY_INVENTORIES"</code><code>.</code><code>"DW_UPDATE_DT"</code><code>>TO_DATE(</code><code>' 1900-01-01 00:00:00'</code><code>, </code><code>'syyyy-mm-dd</code>
<code>hh24:mi:ss'</code><code>) </code><code>AND</code> <code>"MTL_SECONDARY_INVENTORIES"</code><code>.</code><code>"DW_UPDATE_DT"</code><code><=</code><code>"WORKFLOW_START_TIMES"</code><code>.</code><code>"WORKFLOW_START_TIME"</code>
<code>)</code>
<code>8 - filter(TO_DATE(</code><code>' 1900-01-01 00:00:00'</code><code>, </code><code>'syyyy-mm-dd</code>
<code>hh24:mi:ss'</code><code>)<</code><code>"WORKFLOW_START_TIMES"</code><code>.</code><code>"WORKFLOW_START_TIME"</code><code>) 10 - access(</code><code>"MTL_SECONDARY_INVENTORIES"</code><code>.</code><code>"DW_UPDATE_DT"</code><code>>TO_DATE(</code><code>' 1900-01-01 00:00:00'</code><code>, </code><code>'syyyy-mm-dd</code>
你可能從以上執行計劃中發現了兩處十分陌生的字眼:UNION ALL
本文轉自maclean_007 51CTO部落格，原文連結:http://blog.51cto.com/maclean/1277523
![Oracle索引内部結構研究[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)