 |
 |
Archives of the TeradataForum
Message Posted: Wed, 29 Oct 2003 @ 15:39:12 GMT
| Subj: | | Re: What do the following mean ? |
| |
| From: | | Anantaraman, Kumaran (K.) |
I identify 4 parts in your explain below
| | We do an all-AMPs JOIN step (1) from CHK.B by way of a RowHash match scan (2) with no residual conditions, which is joined to CHK.A
by way of a RowHash match scan (3) with no residual conditions. CHK.B and CHK.A are joined using a merge join (4), with a join condition of
("CHK.A.a = CHK.B.a") | |
(4) : Indicates that a merge join strategy will be used.
(1) : Indicates that all amps participate in the join
(2) and (3)
Now there are different 'flavors' of merge join. The typical merge algorithm we know is when the 2 tables are sorted, and we image 2
pointers, with 1 pointer per table and the pointers scroll down, thus scanning the 2 tables a maximum of once. If we did do this, then the
left table, CHK.B will be 'all rows scan', and we will see this text in explain in (2). In your case, CHK.B is RowHash match scanned, which
is even better. Instead of scanning all rows of left table, JUMP to the row in left table based on (hashing) the value of row in right
table. This is 'rowhash match scan' of left table. In this way, we will skip un-matching rows in the left table for a even faster
join.
This is explained in manual SQL - vol 2, chapter 'query optimization'. Manual uses the terms 'fast path' and 'slow path' merge joins. I
think they correspond to the rowhash match scan and all-rows scan respectively.
Kumaran Anantaraman
| |
