Dynamic tables vs temp tables !!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Dynamic tables vs temp tables !!

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    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

  2. #2
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    "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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width