 |
 |
Archives of the TeradataForum
Message Posted: Fri, 04 Oct 2002 @ 14:06:13 GMT
| 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-
| |
