Archives of the TeradataForum
Message Posted: Fri, 05 Apr 2013 @ 10:27:22 GMT
<-- Anonymously Posted: Friday, April 05, 2013 12:24 -->
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,
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|