Is it that a HASH join has to be always instructed via a hint? i.e. the optimizer will never choose a HASH join on its own? If yes, is that because the optimizer is a rule-based one? If yes, does the upcoming cost based optimizer choose a HASH join over NL join if it finds the cost to be lower?
A HASH join will need memory for holding the in-memory hash table. That memory will be used on which node: the node running the Data service, Index service or the Query service?