I'm trying to create a flat table using few tables in my database. The primary purpose of the flat table is to function somewhat like a data warehouse fact table and speed up recovery of data for a report. I've written a "merge" query to insert/update data in the flat table and I'm trying to tune the query.
My query looks somewhat like this:
MERGE INTO MY_FACT_TABLE mf
USING
(
SELECT ed.app_date app_date,
fc.f_code f_code,
fc.description description,
ed.id_num id_num,
SUM(ed.amt_1) amt_1,
SUM(ed.amt_2) amt_2,
la.la_description la_description,
pa.pa_code pa_code,
FROM la, lp, pa,
(
SELECT id_num,
app_date,
0 amt_1,
0 amt_2,
FROM ft
WHERE ft_code = '001'
AND rs_code <> '100'
AND TO_CHAR(app_date, 'mmyyyy') = '012000'
AND EXISTS
(SELECT 1
FROM fa
WHERE fa.id_num = ft.id_num
AND ft.app_date BETWEEN TRUNC(fa.a_date,'MONTH') AND NVL(fa.b_date,SYSDATE)
)
GROUP BY id_num, app_date
UNION
SELECT /*+NOPARALLEL(sss)*/
id_num,
app_date,
0 amt_1,
0 amt_2,
0 other_amt,
FROM sss
WHERE TO_CHAR(app_date, 'mmyyyy') = '012000'
AND EXISTS
(SELECT 1
FROM fa
WHERE fa.id_num = sss.id_num
AND sss.mt_date BETWEEN TRUNC(fa.a_date,'MONTH') AND NVL(fa.b_date,SYSDATE)
)
GROUP BY id_num, app_date
UNION
SELECT /*+NOPARALLEL(sss)*/
id_num,
app_date,
SUM(DECODE(SUBSTR(p_code,1,1),'1',p_amt,0)) amt_1,
SUM(DECODE(SUBSTR(p_code,1,1),'2',p_amt,0)) amt_2,
FROM sss
WHERE ((d_code IS NULL) OR (d_code IN ('0','1','3','4')))
AND TO_CHAR(app_date, 'mmyyyy') = '012000'
AND EXISTS
(SELECT 1
FROM fa
WHERE fa.id_num = sss.id_num
AND sss.mt_date BETWEEN TRUNC(fa.a_date,'MONTH') AND NVL(fa.b_date,SYSDATE)
)
GROUP BY id_num, app_date) ed,
(SELECT f_code,
DECODE(f_code, '01', 'ABC Co.',
'02', 'PQR Co.',
'03', 'XYZ Co.',
) description,
id_num,
FROM fa
WHERE f_code IN ('01','02','03')
AND EXISTS
(SELECT /*+NOPARALLEL(sss)*/
1
FROM sss
WHERE sss.id_num = fa.id_num
AND TO_CHAR(app_date, 'mmyyyy') = '012000'
AND sss.mt_date BETWEEN TRUNC(fa.a_date,'MONTH') AND NVL(fa.b_date,SYSDATE)
)
UNION
SELECT
f_code,
DECODE(f_code, '01', 'ABC Co.',
'02', 'PQR Co.',
'03', 'XYZ Co.',
) description,
id_num,
FROM fa
WHERE f_code IN ('01','02','03')
AND EXISTS
(SELECT 1
FROM ft
WHERE ft.id_num = fa.id_num
AND TO_CHAR(app_date, 'mmyyyy') = '012000'
AND ft.app_date BETWEEN TRUNC(fa.a_date,'MONTH') AND NVL(fa.b_date,SYSDATE)
)) fc
WHERE fc.id_num = ed.id_num
AND la.id_num = ed.id_num
AND lp.id_num = ed.id_num
AND pa.pa_code = lp.pa_code
GROUP BY ed.id_num, ed.app_date, la.la_description, pa.pa_code,fc.description, fc.f_code
ORDER BY fc.f_code, ed.id_num
) MER
ON
(mf.id_NUM = MER.id_num
AND mf.f_CODE = MER.F_CODE
AND mf.app_DATE = MER.app_DATE
AND mf.pa_CODE = MER.PA_CODE
)
WHEN NOT MATCHED THEN
INSERT (mf.app_date,
mf.f_code,
mf.description,
mf.id_num,
mf.amt_1,
mf.amt_2,
mf.la_description,
mf.pa_code,
)
VALUES
( MER.app_date,
MER.f_code,
MER.description,
MER.id_num,
MER.amt_1,
MER.amt_2,
MER.la_description,
MER.pa_code,
)
WHEN MATCHED THEN
UPDATE SET
mf.description = MER.description,
mf.amt_1 = MER.amt_1,
mf.amt_2 = MER.amt_2,
mf.la_description = MER.la_description,
The "sss" table has more than a million records. The other tables have a few thousand records. "sss" doesn't allow parallel access, and hence i've put a "no parallel" hint.
Can someone help me tune the query for better performance? I'm using indexes already existing on the tables (not shown in above example). I'm getting confused with which hints to use to improve the performance! The total cost shown in the explain plan is "128", and it takes 6-7 minutes to run the query (usually 30-40 records get inserted at a time)
Any help is greatly appreciated.