Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 14 Sep 1999 @ 14:10:24 GMT


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


Subj:   Question with CASE
 
From:   Rudel Simard

I have 2 tables to join together.

All fields are define column format X (1), character set latin, column type CF.

The join condition are :

     SELECT *
     FROM
     Table1
     LEFT JOIN Table2 ON
     Table2.COD_ROLE = (CASE WHEN Table1.COD_ROLE IS NULL OR
                                  Table1.COD_LIEU_DISP = '0'
                             THEN '1'
                             ELSE table1.COD_LIEU_DISP
                             END)

And the explain is :

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct DONNE_MAJ."pseudo table" for read on a RowHash to prevent global deadlock for TABLE2.  
  2)Next, we lock a distinct DONNE."pseudo table" for read on a RowHash to prevent global deadlock for TABLE1.  
  3)We lock TABLE2 for read, and we lock TABLE1 for read.  
  4)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from TABLE1 by way of a traversal of index # 4 extracting row ids only with a residual condition of ("(TABLE1.DAT_SERV >= DATE '1997-01-01') AND (TABLE1.DAT_SERV <= DATE '1997-01-31')") into Spool 2, which is built locally on the AMPs. Then we do a SORT to order Spool 2 by row id eliminating duplicate rows. The size of Spool 2 is estimated to be 4,249,066 rows. The estimated time for this step is 2 minutes and 39 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from TABLE2 by way of an all-rows scan with a condition of ("TABLE2.COD_SERV_EXTRA_STATS_MOD = 1") into Spool 3, which is duplicated on all AMPs. The size of Spool 3 is estimated with high confidence to be 4,256 to 17,024 rows. The estimated time for this step is 0.17 seconds.
 
  5)We do an all-AMPs JOIN step from TABLE1 by way of row ids from Spool 2 (Last Use) with a residual condition of ("(TABLE1.COD_STA_DECIS = 'PAY') OR (TABLE1.COD_STA_DECIS = 'PPY')"), which is joined to Spool 3 (Last Use). TABLE1 and Spool 3 are left outer joined using a product join, with a join condition of ("COD_ROLE = (TRANSLATE(CASE WHEN ({LeftTable}.COD_ROLE IS NULL) OR ({LeftTable}.COD_ROLE = '0')THEN ('1') ELSE (TRANSLATE({LeftTable}.COD_ROLE USING LATIN_TO_UNICODE)) END USING UNICODE_TO_LATIN))"). The input table TABLE1 will not be cached in memory. The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 4,314,360 to 127,177,132,320 rows. The estimated time for this step is 44 minutes and 37 seconds to 309 hours and 13 minutes.  
  6)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  


The condition in Step 5 can be re-read like this :

     ("COD_ROLE = (TRANSLATE(CASE WHEN  ({LeftTable}.COD_ROLE IS NULL)
           OR ({LeftTable}.COD_LIEU_DISP = '0')
     THEN ('1')
     ELSE (TRANSLATE({LeftTable}.COD_LIEU_DISP USING LATIN_TO_UNICODE))
     END USING UNICODE_TO_LATIN))

What's going on?

How can I rewrite my condition whitout TRANSLATE?

How can I rewrite my condition whitout product join?


Rudel Simard
Régie de l'assurance-maladie du Québec
DBA de l'environnement informationnel



     
  <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