Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 29 Oct 2003 @ 15:39:12 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


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



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023