 |
 |
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
| |
