Dynamic tables vs temp tables !!

[dbasupuser]

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 ?

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

[WilliamR]

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.

[slimdave]

"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.