Clarification on Rowhash Match Scan and Sync Scan


Clarification on Rowhash Match Scan and Sync Scan

Hi Folks,

I need confirmation on my understaning of Rowhash Match Scan and Sync Scan. 


I am joining two tables TableA and TableB using TableA.A1 column which is PI and TableB.B2 column which is not PI.

Prior to join TableB needs to be spooled for Rowhash Match Scan. 

This means TableB needs to be redistributed across AMPs on B2 and spooled, for join with TableA.

Is my understanding correct?


In Sync Scan, while accessing a large table, if User1 is currently reading Datablock3 of TableA and User2 comes in to access TableA, User2 will start from Datablock3 with User1. When the reading is over, User2 will be wrapped back to finish its reading of DB1 and DB2 of TableA.

This way only a portion of the datablock will be cached in memory but not the entire large table. Am I right?


Now in case of JOIN, when 2 large tables are getting joined through Rowhash Match scan, what it means when it says in Explain "not cached in memory, but eligible for synchronized scanning". Does it mean that the Rowhash Matching is happening on Sync Scan basis?

There may be some previous discussions in this forum on the same topic. However, I just wanted to clarify my understanding.