SAP Hana - Hint

Syntax

HINT Specifies a hint to use for the query.

<hint_clause> ::= 
     WITH HINT( <hint_element> [, <hint_element> ...])

    <hint_element> ::= 
     { <hint> | <hint_with_parameters> }

    <hint> ::= !! hint_name from public hint list

    <hint_with_parameters> ::= 
     ROUTE_TO( <volume_id> [{, <volume_id> }] ) 
     | NO_ROUTE_TO( <volume_id> [{, <volume_id> }] ) 
     | ROUTE_BY( <table_name> [{, <table_name>}] ) 
     | ROUTE_BY_CARDINALITY( <table_name> [{, <table_name>}] ) 
     | DATA_TRANSFER_COST ({0 | 1})

When there are hint clauses in the subquery, only the most outer hint is applied.

View

HINTS: Provides all available hints to be used in WITH HINT clauses.

select * from hints

Syntax Example

-- Examples for cache controlling hints:
SELECT FROM T1 WITH HINT( IGNORE_PLAN_CACHE ); 
SELECT FROM T1 WITH HINT( USE_REMOTE_CACHE );

-- Examples for the use of hints in a scale-out environment:

SELECT FROM T1 WITH HINT( ROUTE_TO(1));
SELECT FROM T1 WITH HINT( NO_ROUTE_TO(2,3));
SELECT FROM T1 WITH HINT( ROUTE_BY(T2));
SELECT FROM T1 WITH HINT( ROUTE_BY_CARDINALITY(T1,T2,T3));
SELECT FROM T1 WITH HINT( NO_ROUTE_TO(1), ROUTE_TO(1));   -- route to volume id = 1 ( last one is used )
SELECT FROM T1 WITH HINT( DATA_TRANSFER_COST(0) );

-- Examples for execution engine selection with hints:

SELECT FROM T1 WITH HINT( USE_OLAP_PLAN ); 
SELECT FROM T1 WITH HINT( NO_USE_OLAP_PLAN );

-- Examples for controlling the access path with hints:

SELECT FROM T1 WITH HINT( INDEX_SEARCH ); 
SELECT FROM T1 WITH HINT( NO_INDEX_SEARCH );

-- Examples for controlling join operations with hints:

SELECT FROM T1, T2 WITH HINT( INDEX_JOIN ); 
SELECT FROM T1, T2 WITH HINT( NO_INDEX_JOIN ); 
SELECT FROM T1, T2 WITH HINT( HASH_JOIN ); 
SELECT FROM T1, T2 WITH HINT( NO_HASH_JOIN ); 
SELECT FROM T1, T2 WITH HINT( MIXED_INVERTED_INDEX_JOIN ); 
SELECT FROM T1, T2 WITH HINT( NO_MIXED_INVERTED_INDEX_JOIN ); 
SELECT FROM T1, T2 WITH HINT( OPTIMIZE_METAMODEL ); 
SELECT FROM T1, T2 WITH HINT( NO_OPTIMIZE_METAMODEL );

-- Examples for query rewriting and logical transformations with hints:

SELECT FROM T1 WITH HINT( SUBPLAN_SHARING); 
SELECT FROM T1 WITH HINT( NO_SUBPLAN_SHARING );