Hints

Hints are directives that you embed within a query or directed query. They conform to the following syntax:

/*+hint-name[, hint-name]…*/

Hints are bracketed by comment characters /*+ and */, which can enclose multiple comma-delimited hints. For example:

SELECT /*+syntactic_join,verbatim*/

Restrictions

When embedding hints in a query, be aware of the following restrictions:

Do not embed spaces in the comment characters /* and */.
In general, spaces are allowed before and after the plus (+) character and hint‑name; however, some third-party tools do not support spaces embedded inside /*+.

Vertica supports the following hints:

General hints	ALLNODES	Qualifies an `EXPLAIN` statement to request a query plan that assumes all nodes are active.
	EARLY_MATERIALIZATION	Specifies early materialization of a table for the current query.
	ENABLE_WITH_CLAUSE_MATERIALIZATION	Enables and disables WITH clause materialization for a specific query.
	LABEL	Labels a query so you can identify it for profiling and debugging.
	SKIP_STATISTICS	Directs the optimizer to produce a query plan that incorporates only minimal statistics.
Eon Mode hints	DEPOT_FETCH	Specifies whether a query fetches data to the depot from communal storage when the depot lacks data for this query.
Eon Mode hints	ECSMODE	Specifies the elastic crunch scaling (ECS) strategy for dividing shard data among its subscribers.
Join hints	SYNTACTIC_JOIN	Enforces join order and enables other join hints.
	DISTRIB	Sets the input operations for a distributed join to broadcast, resegment, local, or filter.
	GBYTYPE	Specifies which algorithm—`GROUPBY HASH` or `GROUPBY PIPELINED`—the Vertica query optimizer should use to implement a `GROUP BY` clause.
	JTYPE	Enforces the join type: merge or hash join.
	UTYPE	Specifies how to combine `UNION ALL` input.
Table hints	PROJS	Specifies one or more projections to use for a queried table.
Table hints	SKIP_PROJS	Specifies which projections to avoid using for a queried table.
Directed query hints	IGNORECONST	Maps an input query constant to one or more annotated query constants.
Directed query hints	VERBATIM	Enforces execution of an annotated query exactly as written.