How to force optimizer to broadcast table and use as inner source in hash join

edited February 2020 in General Discussion

I have user SQL that rather stubbornly stick to same execution plan, despite my attempts to change it (using only documented hints).
It is effectively
select XXXXXX
from A join B join C
First, A and B resegmented and joined. It is possible to change what will be inner and outer source in hash join for this stage, using hint.
Then, output of A and B join resegmented with C, and joined. C is inner source, A and B is outer. And, it is not possible to change what would be inner and what would be outer source (despite frantic hints manipulations).
A is rather small, B and C are huge. Result of join A and B is small.
That is very poor execution plan, optimizer is not smart enough (Vertica optimizer developers are smart but way too few of them).
What I want:
1) Broadcast A
2) Join with B locally, and broadcasted A should be inner source in hash join
3) Broadcast result to join
4) Use broadcasted join result as inner source in hash join with C.
Prime point, should be no resegmentation of B and C. As those are humongous.
What I cannot accomplish:
1) I cannot force result of join to be broadcasted.
2) I cannot force result of previous join to be used as inner source in hash join.
3) I cannot avoid resegmentation of B and C.
Can you give some hints how to do it?
Thanks in advance.



Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file