Hints

Hints are directives that you embed within a query or.

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 /*+.

Supported hints

Vertica supports the following hints:

General hints

Hint Description
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

Hint Description
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 elastic crunch scaling (ECS) strategy for dividing shard data among its subscribers.

Join hints

Hint Description
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.

Projection hints

Hint Description
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

The following hints are only supported by directed queries:

Hint Description
:c Marks a query constant that must be included in an input query; otherwise, that input query is disqualified from using the directed query.
:v Maps an input query constant to one or more annotated query constants.
VERBATIM Enforces execution of an annotated query exactly as written.

In this section