ORDERED Hint

[Julie]

Hey guys,

I heard that there might be a SQL hint in Vertica that is similar Oracle's ORDERED hint that we can use to force the ordering of the table joins. However, I can't find anyone who knows if this is true or not. So I thought I'd see if any of the experts on this site might know

[NorbertKrupa]

I'm also very curious about this.

[scutter]

select /*+ add_vertica_options(OPT, ENABLE_SYNTACTIC_OPTIMIZER)*/ …

Not to be used on a regular basis, only if for some reason the optimizer gets the join order wrong. Better to look first at root causes for an incorrect join order such as missing or stale statistics.

Requires using the ansi-style join (ON clause for the join keys, not WHERE).

—Sharon

[NorbertKrupa]

Sharon always delivers. How does this option work with the rest of the statement to force a early/late materialization ?

[Julie]

Sharon, that's awesome! THANKS!!!

[scutter]

Norbert - changing the join order will have an impact on which columns have early materialization. But I don’t really consider materialization when I’m reviewing performance - it is what it is and not something that I can or want to influence.

—Sharon

[nnani]

Thanks sharon !!!!