Archives of the TeradataForum

Message Posted: Fri, 05 Apr 2013 @ 10:27:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Friday, April 05, 2013 12:24 -->

Hello All,

I have 2 large tables A and C as below

A has 100 mil rows of which BKG_BK has around 250K populations and the rest are null.

C has around 20 mil rows ? and BKG_BK has 10 mil populations

The PIs of both tables are not BKG_BK

Our reporting folks run this query?

     Sel * from
     V_FACT_EQUIPMENT_ACTIVITY A
     LEFT OUTER JOIN
     UD_GEM_AD.V_SHIPMENT C
     ON A.BKG_BK = C. BKG_BK;

This runs out of spool evidently because of a large number of nulls will hash to the same amp.

My solution is to do an inner join between A and C and union all;

     Sel A.columnlist, b.columnlist from
     V_FACT_EQUIPMENT_ACTIVITY A
     Inner join
     UD_GEM_AD.V_SHIPMENT C
     ON A.BKG_BK = C. BKG_BK

     Union all

     Sel A.columnlist,  ?from V_FACT_EQUIPMENT_ACTIVITY A
     Where A.BKG_BK is null



Is there something more elegant that could be done? Default values or coalecse for null would not work again for the reasons same as that
for null values.

Grateful for your advice.

 
 
 
 


 
 
 
 
 
 
 
 
 
 
 
 
 
<Prev
 
Next>
 
 
 
<<First
 
<Prev
 
Next>
 
Last>>