 |
 |
Archives of the TeradataForum
Message Posted: Tue, 14 Sep 1999 @ 14:10:24 GMT
| 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
| |
