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:

/*+ DIRECT, LABEL(myLabel) */

Restrictions

When embedding hints, 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 /*+.

Supported Hints

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.
ECSMODE Specifies the strategy to use when dividing data in a shard among is subscribers in a subcluster using ECS. See Manually Choosing an ECS Strategy for more information.
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.
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.
VERBATIM Enforces execution of an annotated query exactly as written.