Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 04 Oct 2002 @ 14:06:13 GMT


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


Subj:   Re: Joins using varchar and char columns
 
From:   Burton, Bruce

okay, we eliminated the varchar vs. char issue correcting the query to make both join values "acct" as char(14) but still have problems. now it gets really strange. if we run the join selecting specific columns from both tables we get one result...if we run the same exact query with a select * (exactly the same...with select * substituted for the column names) we get a different amount of rows returned. why would a select * vs. selecting specific columns give different results???

here are the queries, explains, show view:

show view cmdmviews.static

REPLACE VIEW CMDMVIEWS.Static AS LOCK CMDMDB.Static FOR ACCESS SELECT * FROM CMDMDB.Static ;

1-table t1 has a unique prim. key defined as "UNIQUE PRIMARY INDEX (acct);". acct is char(14)

2-t2 is a 'sel *' view on a tbl and has a unique prim. index defined as "UNIQUE PRIMARY INDEX UPI_Static ( acct ,mobile );". acct is char(14).

Query1:
=======

explain
select *
from    mktg_temp.p065_snet_input t1
        left join
        cmdmviews.static t2
        on t1.acct=t2.acct
Explanation
--------------------------------------------------
 
  1)First, we lock a distinct mktg_temp."pseudo table" for read on a RowHash to prevent global deadlock for mktg_temp.t1.  
  2)Next, we lock mktg_temp.t1 for read, and we lock CMDMDB.Static for access.  
  3)We do an all-AMPs RETRIEVE step from mktg_temp.t1 by way of an all-rows scan with no residual conditions into Spool 2 fanned out into 9 hash join partitions, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with no confidence to be 236,600 rows.  
  4)We do an all-AMPs RETRIEVE step from Spool 2 by way of an all-rows scan into Spool 3 fanned out into 9 hash join partitions, which is duplicated on all AMPs. The size of Spool 3 is estimated with low confidence to be 66,248,000 rows. The estimated time for this step is 1 minute and 56 seconds.  
  5)We do an all-AMPs RETRIEVE step from CMDMDB.Static by way of an all-rows scan with no residual conditions into Spool 4 fanned out into 9 hash join partitions, which is built locally on the AMPs. The size of Spool 4 is estimated with high confidence to be 26,605,513 rows. The estimated time for this step is 4 minutes and 29 seconds.  
  6)We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to Spool 4 (Last Use). Spool 3 and Spool 4 are joined using a hash join of 9 partitions, with a join condition of ("acct = acct"). The result goes into Spool 5, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 5 by row hash. The size of Spool 5 is estimated with low confidence to be 316,435 rows. The estimated time for this step is 4 minutes and 10 seconds.  
  7)We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan., which is joined to Spool 5 (Last Use). Spool 2 and Spool 5 are left outer joined using a merge join of 9 partitions, with a join condition of ("Field_1 = Field_1"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 316,435 rows. The estimated time for this step is 0.16 seconds.  
  8)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 10 minutes and 36 seconds.  


Query2:
=======

explain
select
t1.acct,t2.acct,Con_actend_date,combill_status,churn_rank,Cgsa,Bus_seg,Bus_ind
from  mktg_temp.p065_snet_input t1
        left join
        cmdmviews.static t2
        on t1.acct=t2.acct
Explanation
--------------------------------------------------
 
  1)First, we lock a distinct mktg_temp."pseudo table" for read on a RowHash to prevent global deadlock for mktg_temp.t1.  
  2)Next, we lock mktg_temp.t1 for read, and we lock CMDMDB.Static for access.  
  3)We do an all-AMPs RETRIEVE step from CMDMDB.Static by way of an all-rows scan with no residual conditions into Spool 2, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with high confidence to be 26,605,513 rows. The estimated time for this step is 3 minutes and 27 seconds.  
  4)We do an all-AMPs JOIN step from mktg_temp.t1 by way of a RowHash match scan. with no residual conditions, which is joined to Spool 2 (Last Use). mktg_temp.t1 and Spool 2 are left outer joined using a merge join, with a join condition of ("mktg_temp.t1.acct = acct"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 316,435 rows. The estimated time for this step is 23.40 seconds.  
  5)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 3 minutes and 50 seconds.  

-end-



     
  <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: 15 Jun 2023