SQL性能問題.

SELECT
0 primary_quantity,
0 transaction_quantity,
(SUM(NVL(DECODE(TRUNC(MONTHS_BETWEEN(trunc(吐舌_as_of_date),TRUNC(MMT1.TRANSACTION_DATE))),
0,MMT1.SP_QTY,
1,MMT1.SP_QTY,
2,MMT1.SP_QTY,0),0))) F1T3_QTY,
(SUM(NVL(DECODE(TRUNC(MONTHS_BETWEEN(trunc(吐舌_as_of_date),TRUNC(MMT1.TRANSACTION_DATE))),
3,MMT1.SP_QTY,
4,MMT1.SP_QTY,
5,MMT1.SP_QTY,0),0))) F4T6_QTY,
(SUM(NVL(DECODE(TRUNC(MONTHS_BETWEEN(trunc(吐舌_as_of_date),TRUNC(MMT1.TRANSACTION_DATE))),
6,MMT1.SP_QTY,
7,MMT1.SP_QTY,
8,MMT1.SP_QTY,0),0))) F7T9_QTY,
(SUM(NVL(DECODE(TRUNC(MONTHS_BETWEEN(trunc(吐舌_as_of_date),TRUNC(MMT1.TRANSACTION_DATE))),
9,MMT1.SP_QTY,
10,MMT1.SP_QTY,
11,MMT1.SP_QTY,0),0))) F10T12_QTY,
(SUM(NVL(DECODE(TRUNC(MONTHS_BETWEEN(trunc(吐舌_as_of_date),TRUNC(MMT1.TRANSACTION_DATE))),
0,0,
1,0,
2,0,
3,0,
4,0,
5,0,
6,0,
7,0,
8,0,
9,0,
10,0,
11,0,MMT1.SP_QTY),0))) OVR12_QTY,
organization_id organization_id,
inventory_item_id inventory_item_id,
subinventory_code subinventory_code
FROM
(
SELECT MMT.ORGANIZATION_ID ORGANIZATION_ID,
MMT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
mmt.subinventory_code subinventory_code,
TRUNC(MMT.TRANSACTION_DATE) TRANSACTION_DATE,
SUM(DECODE(MMT.TRANSACTION_TYPE_ID,
2,MMT.PRIMARY_QUANTITY,
3,MMT.PRIMARY_QUANTITY,
4,MMT.PRIMARY_QUANTITY,
8,MMT.PRIMARY_QUANTITY,
12,MMT.PRIMARY_QUANTITY,
15,MMT.PRIMARY_QUANTITY,
17,MMT.PRIMARY_QUANTITY,
18,MMT.PRIMARY_QUANTITY,
36,MMT.PRIMARY_QUANTITY,
37,MMT.PRIMARY_QUANTITY,
38,MMT.PRIMARY_QUANTITY,
40,MMT.PRIMARY_QUANTITY,
41,MMT.PRIMARY_QUANTITY,
42,MMT.PRIMARY_QUANTITY,
44,MMT.PRIMARY_QUANTITY,
51,MMT.PRIMARY_QUANTITY,
70,MMT.PRIMARY_QUANTITY,
71,MMT.PRIMARY_QUANTITY,0)) SP_QTY
FROM
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS MSI
WHERE MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = 吐舌_org_id
AND TRUNC(MMT.TRANSACTION_DATE) <= trunc(吐舌_as_of_date)
AND TO_CHAR(MMT.TRANSACTION_DATE,'YYYYMMDD'>='20070401'
and (mmt.transaction_type_id in (12,15,17,18,36,37,38,40,41,42,44,70,71)
or
(mmt.transaction_type_id in (2,3,4,8,51) and mmt.primary_quantity > 0))
&lp_item
GROUP BY
MMT.ORGANIZATION_ID,
MMT.INVENTORY_ITEM_ID,
mmt.subinventory_code,
MMT.TRANSACTION_DATE
UNION ALL
SELECT MMT.ORGANIZATION_ID ORGANIZATION_ID,
MMT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
mmt.subinventory_code subinventory_code,
TRUNC(MMT.TRANSACTION_DATE) TRANSACTION_DATE,
SUM(DECODE(MMT.TRANSACTION_TYPE_ID,
2,MMT.PRIMARY_QUANTITY,
3,MMT.PRIMARY_QUANTITY,
4,MMT.PRIMARY_QUANTITY,
8,MMT.PRIMARY_QUANTITY,
12,MMT.PRIMARY_QUANTITY,
15,MMT.PRIMARY_QUANTITY,
17,MMT.PRIMARY_QUANTITY,
18,MMT.PRIMARY_QUANTITY,
36,MMT.PRIMARY_QUANTITY,
37,MMT.PRIMARY_QUANTITY,
38,MMT.PRIMARY_QUANTITY,
40,MMT.PRIMARY_QUANTITY,
41,MMT.PRIMARY_QUANTITY,
42,MMT.PRIMARY_QUANTITY,
44,MMT.PRIMARY_QUANTITY,
51,MMT.PRIMARY_QUANTITY,
70,MMT.PRIMARY_QUANTITY,
71,MMT.PRIMARY_QUANTITY,0)) SP_QTY
FROM
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS MSI
WHERE MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = 3 --get the data from the a ORG
AND TRUNC(MMT.TRANSACTION_DATE) <= trunc(吐舌_as_of_date)
AND TO_CHAR(MMT.TRANSACTION_DATE,'YYYYMMDD'<'20070401'
and (mmt.transaction_type_id in (12,15,17,18,36,37,38,40,41,42,44,70,71)
or
(mmt.transaction_type_id in (2,3,4,8,51) and mmt.primary_quantity > 0))
&lp_item
GROUP BY
MMT.ORGANIZATION_ID,
MMT.INVENTORY_ITEM_ID,
mmt.subinventory_code,
MMT.TRANSACTION_DATE
) MMT1
GROUP BY
MMT1.ORGANIZATION_ID,
MMT1.INVENTORY_ITEM_ID,
mmt1.subinventory_code
UNION
SELECT sum(mmt.primary_quantity) primary_quantity,
0 transaction_quantity,
0 f1t3_qty,
0 f4t6_qty,
0 f7t9_qty,
0 f10t12_qty,
0 ovr12_qty,
mmt.organization_id organization_id,
mmt.inventory_item_id inventory_item_id,
mmt.subinventory_code subinventory_code
FROM
mtl_material_transactions mmt,
mtl_system_items msi
WHERE
mmt.subinventory_code is not null
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND trunc(mmt.transaction_date) > trunc(吐舌_as_of_date)
AND mmt.organization_id = 吐舌_org_id
AND TO_CHAR(MMT.TRANSACTION_DATE,'YYYYMMDD'>='20070401'
and mmt.transaction_type_id not in (24,90)
&lp_item
GROUP BY
mmt.organization_id,
mmt.inventory_item_id,
mmt.subinventory_code
UNION ALL
SELECT sum(mmt.primary_quantity) primary_quantity,
0 transaction_quantity,
0 f1t3_qty,
0 f4t6_qty,
0 f7t9_qty,
0 f10t12_qty,
0 ovr12_qty,
mmt.organization_id organization_id,
mmt.inventory_item_id inventory_item_id,
mmt.subinventory_code subinventory_code
FROM
mtl_material_transactions mmt,
mtl_system_items msi
WHERE
mmt.subinventory_code is not null
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND trunc(mmt.transaction_date) > trunc(吐舌_as_of_date)
AND mmt.organization_id = 3
AND TO_CHAR(MMT.TRANSACTION_DATE,'YYYYMMDD'<'20070401'
and mmt.transaction_type_id not in (24,90)
&lp_item
GROUP BY
mmt.organization_id,
mmt.inventory_item_id,
mmt.subinventory_code
UNION
SELECT 0 primary_quantity,
SUM(MOQ.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,
0 f1t3_qty,
0 f4t6_qty,
0 f7t9_qty,
0 f10t12_qty,
0 ovr12_qty,
MOQ.ORGANIZATION_ID organization_id,
MOQ.INVENTORY_ITEM_ID inventory_item_id,
moq.subinventory_code subinventory_codeFROM
MTL_ONHAND_QUANTITIES MOQ,
MTL_SYSTEM_ITEMS MSI
WHERE MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = 吐舌_org_id
&lp_item
GROUP BY
MOQ.ORGANIZATION_ID,
MOQ.INVENTORY_ITEM_ID,
moq.subinventory_codeadd the union all cause the performance issue