有应用人员反映某套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=)