Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 15 Apr 2004 @ 12:17:37 GMT


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


Subj:   Re: Union and Spool space error
 
From:   Claybourne Barrineau

Rohit,

Make sure you are using 'Union All' instead of 'Union'. Also, if you are on V2R5, you might want to consider 'Horizontal Partitioning' via the use of check constraints in the Date column definitions. This will require you to create a view which performs a 'Union All' of your multiple year based fact tables. Like PPI, however, I doubt if the optimizer will make use of the horizontal partitioning if you select your date range from another table (such as a join to a Calendar table.)


Hope this helps,

Clay


     Create Table Test_Space.HP_CLB_2001
     (DateField date check (DateField between 1010101 and 1011231));

     Insert into Test_Space.HP_CLB_2001 Values (1010101);
     Insert into Test_Space.HP_CLB_2001 Values (1010102);
     Insert into Test_Space.HP_CLB_2001 Values (1010103);
     Insert into Test_Space.HP_CLB_2001 Values (1010104);
     Insert into Test_Space.HP_CLB_2001 Values (1010105);

     Collect Stats on Test_Space.HP_CLB_2001 Index (DateField);

     Create Table Test_Space.HP_CLB_2002
     (DateField date check (DateField between 1020101 and 1021231));

     Insert into Test_Space.HP_CLB_2002 Values (1020101);
     Insert into Test_Space.HP_CLB_2002 Values (1020102);
     Insert into Test_Space.HP_CLB_2002 Values (1020103);
     Insert into Test_Space.HP_CLB_2002 Values (1020104);
     Insert into Test_Space.HP_CLB_2002 Values (1020105);

     Collect Stats on Test_Space.HP_CLB_2002 Index (DateField);

     Create View Test_Space.HP_CLB
     (
     DateField
     )
     as
     (
     Select   DateField
     From  Test_Space.HP_CLB_2001

     Union All

     Select   DateField
     From  Test_Space.HP_CLB_2002
     );

     explain
     Select   *
     From  Test_Space.HP_CLB
     Where DateField   between  1010101 and 1011231;

-- Note, no reference to Test_Space.HP_CLB_2002 in the explain

       
  1)First, we lock a distinct Test_Space."pseudo table" for read on a RowHash to prevent global deadlock for Test_Space.HP_CLB_2001.  
  2)Next, we lock Test_Space.HP_CLB_2001 for read.  
  3)We do an all-AMPs RETRIEVE step from Test_Space.HP_CLB_2001 by way of an all-rows scan with a condition of ("(Test_Space.HP_CLB_2001.DateField <= DATE '2001-12-31') AND (Test_Space.HP_CLB_2001.DateField >= DATE '2001-01-01')") into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 5 rows. The estimated time for this step is 0.03 seconds.  
  4)We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan with a condition of ("(DATEFIELD <= DATE '2001-12-31') AND (DATEFIELD >= DATE '2001-01-01')") into Spool 2 (all_amps), which is built locally on the AMPs. The size of Spool 2 is estimated with high confidence to be 5 rows. The estimated time for this step is 0.03 seconds.  
  5)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 2 are sent back to the user as the result of statement 1. The total estimated time is 0.06 seconds.  



     
  <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