Optimizer expert, please correct my under standing here

Database
Enthusiast

Optimizer expert, please correct my under standing here

The way I thought Teradata always work is trying to keep things "local" to amp. Therefore, if A Join B on column C , the table A and Table B will be retrieved with not null on C and redistribute on hashcode of C and then join , do I under stand this right? (well of course, there are situations that you like to do duplicate to all amps in case one table is too small). The idea is that table needs to be distributed by hash of equal join column and then the data will resides on the same amp and they can perform join operation.

However, recently I found several instances that this is not true. Teradata optimizer picked a column to distribute the spool file which does not appear any where in the join condition. The structture of the query is like this

SELECT ...
(SELECT * FROM A JOIN B QUALIFY RANK()... = 1) subq INNER JOIN B1 ON COL1 INNER JOIN C ON COL2 INNER JOIN D ON COL3

In the explain, I can see the subq is executed as expected and generated the derived table. the B is duplicated to all Amps,
COL3 is the UPI on D, in the explain the table D is retrieved with no residual condition and redistributed by COL3 (isn't D already distributed by its own PI?) and sort by rowash..
and real strange thing happened here, the spool file generated from subq inner join B1 is redistributed by hashcode of a column in table A which not appear in the join at all. then the next step somehow join this spool with COL3's double distributed spool based on COL3

The real query is a little too long to post here. But this is what happened. Table A is a global temp with some stats. But I do not see how global temp could affect this behavior here. Everyting in subq was executed as expected and the interesting behavior happens after subq get joined to B1..

Please help me explain why an irrelevant column was picked to distributed the result spool of subq JOIN B1 and if it the spool is not distributed by same set of columns, how the join is performed in the same amp using rowhash match scan and merge join.
Tags (2)
10 REPLIES
Junior Contributor

Re: Optimizer expert, please correct my under standing here

Could you please post at least the appropriate steps from explain.
It's hard to tell without knowing the join types etc.

Dieter
Enthusiast

Re: Optimizer expert, please correct my under standing here

Step 22.1 redistribute by TMP.puap1.fam_frnd_ind_nbr generated the spool #24
Step 23, spool #24 and #23 joined row hash match scan merge join condition ("prepd_srv_loc_cd =LOC_IND_CD")
Notice that spol #23 is generated from step 21.3 and distributed by prepd_src_loc_cd

21) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 18 (Last Use) by way of
a RowHash match scan, which is joined to Spool 19 (Last Use)
by way of a RowHash match scan. Spool 18 and Spool 19 are
joined using a merge join, with a join condition of (
"prepd_feat_cd = BSC_FEAT_CD"). The result goes into Spool 21
(all_amps), which is built locally on the AMPs. Then we do a
SORT to order Spool 21 by the hash code of (
TMP.puap1.callng_nbr). The size of Spool 21 is
estimated with no confidence to be 5,960 rows (8,427,440
bytes). The estimated time for this step is 0.03 seconds.
2) We do an all-AMPs RETRIEVE step from PDB.pura1 by way of
an all-rows scan with a condition of ("NOT
(PDB.pura1.prepd_usge_rtg_cd IS NULL)") into Spool 22
(all_amps), which is duplicated on all AMPs. The size of
Spool 22 is estimated with high confidence to be 197,120 rows
(4,928,000 bytes). The estimated time for this step is 0.03
seconds.
3) We do an all-AMPs RETRIEVE step from PDB.psla1 by way of
an all-rows scan with no residual conditions into Spool 23
(all_amps), which is redistributed by the hash code of (
PDB.psla1.prepd_srv_loc_cd) to all AMPs. Then we do a
SORT to order Spool 23 by row hash. The size of Spool 23 is
estimated with high confidence to be 338,557 rows (9,818,153
bytes). The estimated time for this step is 0.06 seconds.
22) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 20 (Last Use) by way of
a RowHash match scan, which is joined to Spool 21 (Last Use)
by way of a RowHash match scan. Spool 20 and Spool 21 are
joined using a merge join, with a join condition of (
"prepd_tarf_period_cd = TARF_PERIOD_CD"). The result goes
into Spool 24 (all_amps), which is redistributed by the hash
code of (TMP.puap1.fam_frnd_ind_nbr) to all AMPs. Then
we do a SORT to order Spool 24 by row hash. The size of Spool
24 is estimated with no confidence to be 5,960 rows (
8,451,280 bytes). The estimated time for this step is 0.03
seconds.
2) We do an all-AMPs JOIN step from PDB.pca1 by way of an
all-rows scan with no residual conditions, which is joined to
Spool 22 (Last Use) by way of an all-rows scan. PDB.pca1
and Spool 22 are joined using a product join, with a join
condition of ("(1=1)"). The result goes into Spool 25
(all_amps), which is redistributed by the hash code of (
PDB.pura1.prepd_usge_rtg_cd, PDB.pca1.prepd_cos_cd)
to all AMPs. Then we do a SORT to order Spool 25 by row hash.
The size of Spool 25 is estimated with high confidence to be
14,168 rows (467,544 bytes). The estimated time for this step
is 0.03 seconds.
23) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of a
RowHash match scan, which is joined to Spool 24 (Last Use) by way
of a RowHash match scan. Spool 23 and Spool 24 are joined using a
merge join, with a join condition of ("prepd_srv_loc_cd =
LOC_IND_CD"). The result goes into Spool 26 (all_amps), which is
redistributed by the hash code of (TMP.puap1.tsrv_cd,
TMP.puap1.dur_val) to all AMPs. Then we do a SORT to order
Spool 26 by row hash. The size of Spool 26 is estimated with no
confidence to be 5,960 rows (8,475,120 bytes). The estimated time
for this step is 0.03 seconds.
Enthusiast

Re: Optimizer expert, please correct my under standing here

someone care to help?
Teradata Employee

Re: Optimizer expert, please correct my under standing here

did you try opening an incident with TD support with the query, explain, TD version, and the TSET information? It takes more information in long and complicated query to know what may be going on.
Enthusiast

Re: Optimizer expert, please correct my under standing here

welll , i am in this particular group who do not have such access to create incident... bunch red tapes to go through to bother the ones who have such access...
Teradata Employee

Re: Optimizer expert, please correct my under standing here

Is it possible to post the TSET of your query ?
Enthusiast

Re: Optimizer expert, please correct my under standing here

Thanks for suggestion.. but I do not have that much access on this system. the curious thing about this explain is the distribution key of spool and the upcoming join are not quite related.. trying to get some attention from TD optimizer experts to understand how could this happen.
Junior Contributor

Re: Optimizer expert, please correct my under standing here

You're absolutely right, according to explain there can't be a merge join using those columns.

Is the answer set correct?
Then it might be a kind of explain bug.

Dieter
Enthusiast

Re: Optimizer expert, please correct my under standing here

thanks for clear my doubts.. the resulset looks fine. I guess it is an explain bug.