Register Login

Important Hints related to SAP HANA

Updated May 18, 2018

What are important hints related to SAP HANA?

The most important hints of SAP HANA are:

Hint

SUBSTITUTE VALUES

Type

ABAP

Details

Use of literals instead of bind variables

Scenarios

Sending literals instead of bind values to the database is in general not recommended, because of parsing and SQL cache overhead (see SAP Note 2124112). In specific situations with a limited amount of different sets of literals and the need of different execution plans depending on the literals it is possible to disable the use of bind variables by setting the &SUBSTITUTE VALUES& hint.

Hint

SUBSTITUTE LITERALS

Type

ABAP

Details

Use of literals instead of bind variables for constants

Scenarios

The &SUBSTITUTE LITERALS& only sends ABAP constants to the database as literals. For variable values bind variables are used. As a consequence the amount of different WHERE clauses typically remain on a lower level than with &SUBSTITUTE VALUES& and so the negative effect on parsing and the SQL cache is minimized. Due to the fact that SAP HANA evaluates the literals behind the bind variables during first execution, the effect of this hint is smaller than on other databases where the bind variable content is not known during parsing.

Hint

max_in_blocking_factor <n>

Type

ABAP

Details

Limitation of IN list generated by FOR ALL ENTRIES to a maximum length on <n>

Scenarios

The SAP profile parameter rsdb/max_in_blocking_factor defines the maximum length of the IN list generated in the context of FOR ALL ENTRIES statements. This scenario is typically used when exactly one field of the WHERE clause refers to the FOR ALL ENTRIES list. For some reasons it can be useful to deviate from the standard, e.g.:

  • Higher values can result in less network roundtrips and reduce the communication overhead.
  • Lower values can result in less SQL statements with only a different IN list length, so that parsing and the SQL cache utilization is optimized.
  • A variation in either direction can help to convince the query optimizer to use a better execution plan.

The default value of rsdb/max_in_blocking_factor is 1024 (SAP 1987132). Often it is useful to set the parameter centrally to a lower value in order to minimize parsing and SQL cache overhead (see SAP2124112).

Hint

max_blocking_factor <n>

Type

ABAP

Details

Limitation of OR concatenations generated by FOR ALL ENTRIES to a maximum length on <n>

Scenarios

The SAP profile parameter rsdb/max_blocking_factor defines the maximum length of the OR concatenation generated in the context of FOR ALL ENTRIES statements. This scenario is typically used when more than one field of the WHERE clause refers to the FOR ALL ENTRIES list. For some reasons it can be useful to deviate from the standard, e.g.:

  • Higher values can result in less network roundtrips and reduce the communication overhead.
  • Lower values can result in less SQL statements with only a different number of OR concatenations, so that parsing and the SQL cache utilization is optimized.
  • A variation in either direction can help to convince the query optimizer to use a better execution plan.
  • The default value of rsdb/max_blocking_factor in SAP HANA environments is 50 (SAP 1987132).

Hint

prefer_join_with_fda 1

Type

ABAP

(kernel >= 7.42)

Details

Pushdown of FOR ALL ENTRIES list to SAP HANA via fast data access

Scenarios

This FOR ALL ENTRIES implementation is available as of SAP kernel 7.42. A join is created between the FOR ALL ENTRIES list and the database table. The FOR ALL ENTRIES list is sent to the database using the fast data access (FDA) protocol.

This processing can be more efficient than the classic approaches.

Hint

prefer_join 1& dbsl_equi_join

Type

ABAP

Details

Generation of an IN list of IN lists instead of OR concatenations in case of FOR ALL ENTRIES

Scenarios

If multiple WHERE conditions refer to the FOR ALL ENTRIES list, an OR concatenation is used per default. Sometimes OR concatenations are not processed optimally by SAP HANA (see SAP 2000002). In these cases the dbsl_equi_join hint may be used to switch to an IN list consisting of smaller IN lists. See SAP Notes1662726 and 1622681 for more information and restrictions. The length of generated IN lists is limited by parameter rsdb/max_blocking_factor in this case (not by rsdb/max_in_blocking_factor).

In BW environments the function module RSDU_CREATE_HINT_FAE_HDB is used to generate proper dbsl_equi_join hints for specific BW functionalities (see SAP Note 1718930). SAP Notes 1919804, 2020193 and 2127008 provide optimizations for this function in SAP BW 7.30 to 7.40 environments in order to avoid terminations and reduce the statement size.

In order to take optimal advantage of the dbsl_equi_join hint in BW, you have to make sure that SAP document 2012779 (7.40 SPS 08), 2007363 (7.40 SPS 09), 2020193 (7.40 SPS 08), 2092759 (7.40 SPS 10) and 2143880 (7.40 SPS 12) are implemented.

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.


×