Important Hints related to SAP HANA

Important Hints related to SAP HANA

What are important hints related to SAP HANA?

The most important hints of SAP HANA are:

 

Hint

CALC_VIEW_UNFOLDING
NO_CALC_VIEW_UNFOLDING

Type

HANA

Details

Activate / deactivate unfolding of calculation views in SQL statements

Scenarios

These hints influence the processing of calculation views within SQL statements (see SAP 2177965 and SAP internal 2170436):

• CALC_VIEW_UNFOLDING: Calculation views are unfolded and processed by SQL engine
• NO_CALC_VIEW_UNFOLDING: Calculation view are independently processed by attribute engine

 

Hint

CS_EXPR_JOIN
NO_CS_EXPR_JOIN

Type

HANA

Details

Prefer / avoid column engine expression joins

Scenarios

These hints can be used to influence the handling of expressions. SAP Note 2212330 describes a bug with Rev. 100 and 101 that can produce wrong results and can be bypassed by using the hint NO_CS_EXPR_JOIN.

 

Hint

CS_ITAB_IN_SUBQUERY
NO_CS_ITAB_IN_SUBQUERY

Type

HANA

Details

Prefer / avoid internal tables in subquery

Scenarios

The hint NO_CS_ITAB_IN_SUBQUERY can sometimes help to avoid large sizes of heap allocator Pool/QueryMediator.

 

Hint

IGNORE_PLAN_CACHE

Type

HANA

Details

Bypassing of SQL cache and reparsing for every execution

Scenarios

Normally a SQL statement is parsed when it is executed the first time, and the resulting execution plan is stored in the SQL cache. Subsequent executions can then rely on the buffered plan and don't need a complete reparsing. If the following conditions are met at the same time, IGNORE_PLAN_CACHE can be useful:

• Depending on the actual bind values different execution plans are optimal.
• The amount of different bind value combinations in the WHERE clause is not extraordinary high.
• A parsing overhead (typically not more than a few ms) is acceptable for each execution.

 

Hint

JOIN_THRU_AGGR
NO_JOIN_THRU_AGGR

Type
HANA

Details

Pushdown of joins through aggregations

Scenarios

Among others, setting JOIN_THRU_AGGR can be useful to bypass wrong result sets (see SAP 2222121).

 

Hint

OPTIMIZE_METAMODEL
NO_OPTIMIZE_METAMODEL

Type

HANA

Details

Multi-column join processing with CONCAT attribute vs. hash approach

Scenarios

Multi-column joins can be processed via an internal CONCAT attribute or a hash approach (see SAP Note 1986747). The actual behavior can be controlled with these hints:

• OPTIMIZE_METAMODEL: Creation / utilization of CONCAT attribute
• NO_OPTIMIZE_METAMODEL: Hash-based algorithm

Hint

ROUTE_BY
ROUTE_BY_CARDINALITY
ROUTE_TO
NO_ROUTE_TO

Type

HANA

Details

Statement routing control

Scenarios

As of Rev. 73 these hints can be used to control statement routing in scale-out scenarios.

 

Hint

USE_OLAP_PLAN
NO_USE_OLAP_PLAN

Type

HANA

Details

Activate / deactivate use of OLAP engine for column searches

Scenarios

Certain database requests can be executed by either the OLAP engine (USE_OLAP_PLAN) or the join engine (NO_USE_OLAP_PLAN). Depending on the chosen exection plan there are situations where one engine has significant disadvantages compared to the other. Using the hints USE_OLAP_PLAN and NO_USE_OLAP_PLAN it is possible to influence the choice of the engine used for column searches.

The hint OLAP_PARALLEL_AGGREGATION can also be used to prefer the OLAP engine, but with new codings you should use USE_OLAP_PLAN whenever possible.

SAP 1734002 describes how BW can be configured (e.g. RSADMIN parameter HDB_JOIN_ENGINE_QUERY or BW execution mode) to implicitly make use of these parameters.

 

Hint

USE_REMOTE_CACHE

Type

HANA

Details

Optimize HANA-Hadoop connector to use materialized result set

Scenarios

When the USE_REMOTE_CACHE hint is used and the parameter indexserver.ini -> [smart_data_access] -> enable_remote_cache is set to 'true', SDA queries to Hive sources are materialized and subsequent queries are served from the materialized view.