有应用人员反映某套Linux上的11.2.0.1数据库系统中出现了UNION ALL后返回的结果集不正确的问题,我们具体分析下出现问题的其中一条语句:
以上查询语句中，QUERY A部分(也就是UNION ALL之前的SELECT语句)单独查询时返回返回69条记录，QUERY B部分单独查询时返回15记录，UNION ALL后返回的结果却是138条记录，而非84条记录。实际上这套系统也是最近才从10g迁移到11gr2上，之前在10g中同样的应用没有出过类似的问题，可以猜测是11g中新引入的某种特性存在可能引发wrong result的Bug。 具体思路虽然有了，但仍无法确定问题的关键所在；我们来看看该SQL的执行计划:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
SELECT
MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME,
MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
MTL_SECONDARY_INVENTORIES.DESCRIPTION,
MTL_SECONDARY_INVENTORIES.AVAILABILITY_TYPE,
MTL_SECONDARY_INVENTORIES.MATERIAL_ACCOUNT,
MTL_SECONDARY_INVENTORIES.MATERIAL_OVERHEAD_ACCOUNT,
MTL_SECONDARY_INVENTORIES.RESOURCE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.OVERHEAD_ACCOUNT,
MTL_SECONDARY_INVENTORIES.OUTSIDE_PROCESSING_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ASSET_INVENTORY,
MTL_SECONDARY_INVENTORIES.EXPENSE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ENCUMBRANCE_ACCOUNT,
MTL_SECONDARY_INVENTORIES.ATTRIBUTE3,
MTL_SECONDARY_INVENTORIES.ATTRIBUTE5,
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
FROM
REPEMEAERP.MTL_SECONDARY_INVENTORIES,
REPEMEAERP.WORKFLOW_START_TIMES
WHERE
MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
TO_DATE(
'01/01/1900 00:00:00'
,
'MM/DD/YYYY HH24:MI:SS'
)
AND
MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
AND
WORKFLOW_START_TIMES.WORKFLOW_NAME =
LTRIM(RTRIM(
'w_int_FreqBatch_EMEA'
))
/*以上是QUERY A*/
UNION
ALL
/*以下是QUERY B*/
SELECT
DISTINCT
'WORKORDERS'
,
MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
'WORK ORDERS WITH WIP AS CATEGORY VALUE'
,
1,
0,
0,
0,
0,
0,
1,
0,
0,
'MOI'
,
'0'
,
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
FROM
REPEMEAERP.MTL_SECONDARY_INVENTORIES, EIMMAINT.WORKFLOW_START_TIMES
WHERE
MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
TO_DATE(
'01/01/1900 00:00:00'
,
'MM/DD/YYYY HH24:MI:SS'
)
AND
MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
WORKFLOW_START_TIMES.WORKFLOW_START_TIME
AND
WORKFLOW_START_TIMES.WORKFLOW_NAME =
LTRIM(RTRIM(
'w_int_FreqBatch_EMEA'
))
/
138
rows
selected.
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
-----------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
-----------------------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 7 | 2443 | 52 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 7 | 2443 | 52 (0)| 00:00:01 |
|* 2 |
TABLE
ACCESS
FULL
| WORKFLOW_START_TIMES | 1 | 29 | 48 (0)| 00:00:01 |
| 3 |
VIEW
| VW_JF_SET$9BAED2EA | 1 | 320 | 4 (0)| 00:00:01 |
| 4 |
UNION
ALL
PUSHED PREDICATE | | | | | |
|* 5 | FILTER | | | | | |
| 6 |
TABLE
ACCESS
BY
INDEX
ROWID| MTL_SECONDARY_INVENTORIES | 3 | 336 | 2 (0)| 00:00:01 |
|* 7 |
INDEX
RANGE SCAN | IDX_MTL_SECONDARY_INVENTORIES | 1 | | 1 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
| 9 |
TABLE
ACCESS
BY
INDEX
ROWID| MTL_SECONDARY_INVENTORIES | 3 | 36 | 2 (0)| 00:00:01 |
|* 10 |
INDEX
RANGE SCAN | IDX_MTL_SECONDARY_INVENTORIES | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - filter(
"WORKFLOW_START_TIMES"
.
"WORKFLOW_NAME"
=
'w_int_FreqBatch_EMEA'
)
5 - filter(TO_DATE(
' 1900-01-01 00:00:00'
,
'syyyy-mm-dd
hh24:mi:ss'
)<
"WORKFLOW_START_TIMES"
.
"WORKFLOW_START_TIME"
) 7 - access(
"MTL_SECONDARY_INVENTORIES"
.
"DW_UPDATE_DT"
>TO_DATE(
' 1900-01-01 00:00:00'
,
'syyyy-mm-dd
hh24:mi:ss'
)
AND
"MTL_SECONDARY_INVENTORIES"
.
"DW_UPDATE_DT"
<=
"WORKFLOW_START_TIMES"
.
"WORKFLOW_START_TIME"
)
8 - filter(TO_DATE(
' 1900-01-01 00:00:00'
,
'syyyy-mm-dd
hh24:mi:ss'
)<
"WORKFLOW_START_TIMES"
.
"WORKFLOW_START_TIME"
) 10 - access(
"MTL_SECONDARY_INVENTORIES"
.
"DW_UPDATE_DT"
>TO_DATE(
' 1900-01-01 00:00:00'
,
'syyyy-mm-dd
hh24:mi:ss'
)
AND
"MTL_SECONDARY_INVENTORIES"
.
"DW_UPDATE_DT"
<=
"WORKFLOW_START_TIMES"
.
"WORKFLOW_START_TIME"
)
|
你可能从以上执行计划中发现了两处十分陌生的字眼:UNION ALL
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277523
