 |
 |
Archives of the TeradataForum
Message Posted: Sat, 06 Oct 2001 @ 11:42:59 GMT
| Subj: | | Join question |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Thursday, October 04, 2001 14:23 ->
I'm not a join expert so I'm baffled about this.
ni_cktid is non-unique PI of circuit, clli_cd is unique PI of location Why is step 2 distributing this to all-amps and causing an all-
amps join in step 3? I thought this would be a 2-amp join.
replace macro a_kcr046
( ni_cktid (integer),
ckt_date (integer),
ckt_time (integer)
)
as (
select clli_cd, lata_cd (char(3))
from location
where circuit.ni_cktid = :ni_cktid and
circuit.ckt_date = :ckt_date and
circuit.ckt_time = :ckt_time and
circuit.msg_loc_offa = location.clli_cd;
);
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock CORPDBP1.LOCATIONT for access, and we lock corpdbp1.CIRCUITT for access.
| |
| | 2) | Next, we do a single-AMP RETRIEVE step from corpdbp1.CIRCUITT by way of the primary index "corpdbp1.CIRCUITT.NI_CKTID = 1" with a
residual condition of ("(corpdbp1.CIRCUITT.CKT_TIME = 1) AND (corpdbp1.CIRCUITT.CKT_DATE = 1)") 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 low confidence to be 16 rows. The
estimated time for this step is 0.03 seconds.
| |
| | 3) | We do an all-AMPs JOIN step from CORPDBP1.LOCATIONT by way of a RowHash match scan with no residual conditions, which is joined to
Spool 2 (Last Use). CORPDBP1.LOCATIONT and Spool 2 are joined using a merge join, with a join condition of ("CORPDBP1.LOCATIONT.CLLI_CD =
Spool_2.MSG_LOC_OFFA"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with index join
confidence to be 16 rows. The estimated time for this step is 0.63 seconds.
| |
| | 4) | 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 0.66 seconds.
| |
| |
