性能调优

patrickpan 2010-04-21 03:15:11

以下是存储过程的一部分，共涉及到四个表，
表名 -- 数据量
HISTOMVTS_REPORTING -- 6215520
SPB_SWAP -- 1743
SPBR_EQUITY_SCHEDULE -- 36885
SPB_INTEREST_PERIOD -- 43753
执行完要十几分钟，请问如何优化？我对Oracle确实不熟。多谢各位给出建议

[Code=sql]
SELECT
S.ID AS Swap_Id,
(CASE WHEN h.TYPE IN (25,102,7)
THEN
ses.RESET_DATE
WHEN h.TYPE = 101 AND H.DATENEG = ses.RESET_DATE
THEN
ses.RESET_DATE
WHEN h.TYPE = 101 AND H.DATENEG <> ses.RESET_DATE
THEN
H.DATENEG
ELSE
NULL END) AS Equity_Reset_Date,
(CASE WHEN h.TYPE = 101 AND H.DATENEG <> ses.RESET_DATE
THEN
'Y'
ELSE
'N' END) AS Trade_Level_Reset,
NULL AS INTEREST_Reset_Date,
H.DATEVAL AS Pay_Date,
-- Equity PnL
SUM(CASE WHEN h.TYPE = 101
THEN
-Montant_Swap_Ccy
ELSE
0 END) AS Equity_Pnl,
-- Realised PnL
SUM(CASE WHEN h.TYPE = 25
AND (h.dateneg BETWEEN ses.start_date AND ses.reset_date )
AND ses.Trading_Gain_Pay_Rule = 'NEQR'
THEN
-Montant_Swap_Ccy
ELSE
0 END) AS Realised_Pnl,
-- Dividend
SUM(CASE WHEN h.TYPE = 102
AND (h.DATEVAL = nvl(ses.reset_pay_date, ses.end_date))
AND (ses.DIV_PAY_RULE = 'EQRMP')
THEN
Montant_Swap_Ccy
ELSE
0 END ) AS Dividend_Pnl,
-- Commission
SUM(CASE WHEN h.TYPE = 7
AND (h.dateneg BETWEEN ses.start_date AND ses.reset_date)
THEN
-Montant_Swap_Ccy
ELSE
0 END) AS Commission_Pnl,
-- Interest Reset Amount

0 AS Equity_Reset_Amount,

0 AS Div_Re_Reset_Pnl,
0 AS Rld_Re_Reset_Pnl,
0 AS Interest_Reset_Amount
FROM
HISTOMVTS_REPORTING H, SPB_SWAP S, SPBR_EQUITY_SCHEDULE SES
WHERE
H.SWAP_ID = S.ID
AND H.TYPE IN (101,25,102,7)
AND S.ID = SES.Series_Id
AND ((H.DateNeg BETWEEN SES.start_date AND SES.reset_date)
OR (H.DATEVAL = nvl(ses.reset_pay_date, ses.end_date) AND H.TYPE = 102))
AND NVL(P_SERIES_ID, S.ID) = S.ID -- Filter Related Clauses Below
AND NVL(P_FUND_ID, S.FUND_ID) = S.FUND_ID
AND NVL(P_INSTRUMENT_ID,H.INSTRUMENT_ID) = H.INSTRUMENT_ID
AND NVL(P_TRADE_DATE_FROM, ses.RESET_DATE) <= ses.RESET_DATE
AND NVL(P_TRADE_DATE_TO,ses.RESET_DATE) >= ses.RESET_DATE
AND NVL(P_VALUE_DATE_FROM, H.DATEVAL) <= H.DATEVAL
AND NVL(P_VALUE_DATE_TO, H.DATEVAL) >= H.DATEVAL
AND decode(P_CASHFLOW_TYPE, '0', Series_Id, 'EQ', Series_Id, 'INT', -9999, NULL, Series_Id, -9999) = Series_Id
GROUP BY
S.ID ,
SES.RESET_DATE ,
H.DATENEG ,
H.DATEVAL ,
H.TYPE

UNION ALL

SELECT
S.ID AS Swap_Id,
NULL AS Equity_Reset_Date,
'N' AS Trade_Level_Reset,
(CASE WHEN h.TYPE IN (100, 180, 200, 220, 221, 260, 280, 281)
THEN
H.DATENEG
ELSE
NULL END) AS INTEREST_Reset_Date,
H.DATEVAL AS Pay_Date,
0 AS Equity_Pnl,
0 AS Realised_Pnl,
0 AS Dividend_Pnl,
0 AS Commission_Pnl,
0 AS Equity_Reset_Amount,
SUM(CASE WHEN h.TYPE = 220
-- AND h.dateneg = ip.reset_date
THEN
h.Montant_Swap_Ccy
ELSE
0 END) AS Div_Re_Reset_Pnl,
SUM(CASE WHEN h.TYPE = 221
-- AND h.dateneg = ip.reset_date
THEN
h.Montant_Swap_Ccy
ELSE
0 END) AS Rld_Re_Reset_Pnl,
-- Interest Reset Amount
SUM(CASE WHEN h.TYPE IN (100, 180, 200, 220, 221, 260, 280, 281)
AND h.dateneg = ip.reset_date
THEN
h.Montant_Swap_Ccy
ELSE
0 END) AS Interest_Reset_Amount
FROM
HISTOMVTS_REPORTING H, SPB_SWAP S, SPB_INTEREST_PERIOD ip
WHERE
H.SWAP_ID = S.ID
AND H.TYPE IN (100, 180, 200, 220, 221, 260, 280, 281)
AND NVL(P_SERIES_ID, S.ID) = S.ID -- Filter Related Clauses Below
AND NVL(P_FUND_ID, S.FUND_ID) = S.FUND_ID
AND NVL(P_INSTRUMENT_ID,H.INSTRUMENT_ID) = H.INSTRUMENT_ID
AND NVL(P_VALUE_DATE_FROM, H.DATEVAL) <= H.DATEVAL
AND NVL(P_VALUE_DATE_TO, H.DATEVAL) >= H.DATEVAL
AND NVL(P_TRADE_DATE_FROM, ip.reset_date)<= ip.reset_date
AND NVL(P_TRADE_DATE_TO, ip.reset_date) >= ip.reset_date
AND H.DATENEG >= ip.START_DATE
AND H.DATENEG < ip.END_DATE
AND ip.swap_id = s.ID
AND decode(P_CASHFLOW_TYPE, '0', ip.Swap_ID, 'INT', ip.Swap_ID, 'EQ', -9999, NULL, ip.Swap_ID, -9999) = ip.Swap_ID
GROUP BY
S.ID ,
H.DATENEG ,
H.DATEVAL ,
H.TYPE
[/Code]

...全文