Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Avoiding 2646 when joining with large null
 
From:   Anomy Anom

<-- 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, 
     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016