-
Dynamic tables vs temp tables !!
Can someone tell me if the following code will work better if I create the 6 temp tables and do the join vs the way it is done in the followin query. If I create the temp table retrieve the data and then drop them would that work better ?
Code:
CREATE OR REPLACE PACKAGE xsell_prod_procs
IS
--DEFINE REFERENCE CURSORS TO BE RETURNED BY PROCEDURES.
TYPE prod_proc_ref_cur IS REF CURSOR;
PROCEDURE prod_proc( p_prod_desc VARCHAR2,
p_acct_pers_bus_cd VARCHAR2,
p_hh_pers_bus_cd VARCHAR2,
p_prod_proc_cur OUT prod_proc_ref_cur );
--
END xsell_prod_procs;
/
show errors
CREATE OR REPLACE PACKAGE BODY xsell_prod_procs
IS
PROCEDURE prod_proc( p_prod_desc VARCHAR2,
p_acct_pers_bus_cd VARCHAR2,
p_hh_pers_bus_cd VARCHAR2,
p_prod_proc_cur OUT prod_proc_ref_cur )
IS
v_sql VARCHAR2(4000) := NULL;
BEGIN
v_sql :=
'SELECT T1.prod_desc,
T1.mdm_yrmth_key,
COUNT(DISTINCT T1.hh_nbr) total_hhs,
COUNT(DISTINCT T1.rcrd_id) total_accts,
T2.parent_tot_hhs,
T2.parent_prod_desc,
T2.overall_hhs,
T2.overall_accts,
T3.overall_svcs,
T4.parent_svcs,
T4.parent_accts,
T5.parent_ss_hh,
T6.total_amt
FROM
(SELECT prod_desc,
mdm_yrmth_key,
hh_nbr,
rcrd_id
FROM cognos1.mv_xsell_rpt
WHERE prod_desc IN ( '|| p_prod_desc ||')
AND prsnl_biz_cd IN ( '|| p_acct_pers_bus_cd ||')
AND hh_prsnl_biz_cd IN ( '|| p_hh_pers_bus_cd ||')
) T1,
(SELECT hh_nbr,
COUNT(DISTINCT hh_nbr) OVER (PARTITION BY prod_desc) parent_tot_hhs,
COUNT(DISTINCT hh_nbr) OVER () overall_hhs,
COUNT(DISTINCT rcrd_id) OVER () AS overall_accts,
prod_desc parent_prod_desc
FROM cognos1.mv_xsell_rpt
WHERE prod_desc IN ( '|| p_prod_desc ||')
AND prsnl_biz_cd IN ( '|| p_acct_pers_bus_cd ||')
AND hh_prsnl_biz_cd IN ( '|| p_hh_pers_bus_cd ||')
GROUP BY hh_nbr, rcrd_id, prod_desc
) T2,
(SELECT hh_nbr,
SUM(parent_svcs) OVER () overall_svcs
FROM
(SELECT hh_nbr,
COUNT(DISTINCT svc_cat_desc) parent_svcs
FROM cognos1.mv_xsell_rpt
WHERE prod_desc IN ( '|| p_prod_desc ||')
AND prsnl_biz_cd IN ( '|| p_acct_pers_bus_cd ||')
AND hh_prsnl_biz_cd IN ( '|| p_hh_pers_bus_cd ||')
GROUP BY hh_nbr
)
)T3,
(SELECT parent_prod_desc,
COUNT(DISTINCT svc_cat_desc) parent_svcs,
SUM(cnt) parent_accts
FROM
(SELECT a.hh_nbr,
parent_prod_desc,
svc_cat_desc,
COUNT(rcrd_id) cnt
FROM cognos1.mv_xsell_rpt a,
(SELECT DISTINCT hh_nbr,
prod_desc parent_prod_desc
FROM cognos1.mv_xsell_rpt
WHERE prod_desc IN ( '|| p_prod_desc ||')
AND prsnl_biz_cd IN ( '|| p_acct_pers_bus_cd ||')
AND hh_prsnl_biz_cd IN ( '|| p_hh_pers_bus_cd ||')
)b
WHERE a.hh_nbr=b.hh_nbr
AND a.prod_desc IN ( '|| p_prod_desc ||')
AND a.prsnl_biz_cd IN ( '|| p_acct_pers_bus_cd ||')
AND a.hh_prsnl_biz_cd IN ( '|| p_hh_pers_bus_cd ||')
GROUP BY parent_prod_desc, a.hh_nbr, svc_cat_desc)
GROUP BY parent_prod_desc
) T4,
(SELECT parent_prod_desc,
SUM(ss) parent_ss_hh
FROM
(SELECT hh_nbr,
prod_desc parent_prod_desc,
CASE WHEN COUNT(prod_desc) OVER (PARTITION BY hh_nbr)=1 THEN 1 ELSE 0 END ss
FROM cognos1.mv_xsell_rpt
WHERE prod_desc IN ( '|| p_prod_desc ||')
AND prsnl_biz_cd IN ( '|| p_acct_pers_bus_cd ||')
AND hh_prsnl_biz_cd IN ( '|| p_hh_pers_bus_cd ||')
GROUP BY hh_nbr, prod_desc
)
GROUP BY parent_prod_desc
) T5,
(SELECT parent_prod_desc,
prod_desc,
SUM(mth_avg_bal_amt) total_amt
FROM cognos1.mv_xsell_rpt a,
(SELECT DISTINCT hh_nbr,
prod_desc parent_prod_desc
FROM cognos1.mv_xsell_rpt
WHERE prod_desc IN ( '|| p_prod_desc ||')
AND prsnl_biz_cd IN ( '|| p_acct_pers_bus_cd ||')
AND hh_prsnl_biz_cd IN ( '|| p_hh_pers_bus_cd ||')
) b
WHERE a.hh_nbr=b.hh_nbr
AND a.prod_desc IN ( '|| p_prod_desc ||')
AND a.prsnl_biz_cd IN ( '|| p_acct_pers_bus_cd ||')
AND a.hh_prsnl_biz_cd IN ( '|| p_hh_pers_bus_cd ||')
GROUP BY parent_prod_desc, prod_desc
) T6
WHERE T1.hh_nbr = T2.hh_nbr
AND T1.hh_nbr = T3.hh_nbr
AND T2.parent_prod_desc=T4.parent_prod_desc
AND T2.parent_prod_desc=T5.parent_prod_desc
AND (T2.parent_prod_desc=T6.parent_prod_desc
AND T1.prod_desc=T6.prod_desc)
GROUP BY T2.parent_prod_desc, T1.prod_desc, overall_hhs, overall_accts, overall_svcs, parent_svcs, parent_accts, parent_tot_hhs, parent_ss_hh, mdm_yrmth_key, total_amt
ORDER BY T2.parent_prod_desc, T1.prod_desc';
OPEN p_prod_proc_cur FOR v_sql;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END prod_proc;
END xsell_prod_procs;
/
show errors
-
I don't know which will perform better, but creating and dropping tables are resource-intensive operations and large amounts of dynamic SQL are a headache to maintain. I would use proper static tables (temp or otherwise) whenever possible, and avoid creating or dropping tables at runtime.
-
"Amen" on Williams suggestion, although I'd emphasise that global temp tables are the apropriate storage mechnism here, and I'd also add that the key differentiator will be how well Oracle can estimate the statistics associated with the result sets of the materialized views. You'll only know this through running the code.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|