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.