About
Hint in HANA
Articles Related
Management
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 );