HINT Details

The SQL Optimizer usually determines the access path (for example, index search versus table scan) on the basis of the costs (Cost-Based Optimizer). You can deactivate this mechanism and explicitly specify hints for an SQL statement, which enforces a certain access path.

Hint Syntax in SQL Statements

{ SELECT | INSERT | UPDATE | DELETE } ... <hint_clause>

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

<hint>::= { <hint_element> | <routing_hint> | <value_input> }

<hint_element> ::= <hint_name> from public hint list [(<hint_argument_list>)]

<hint_argument_list> ::= <hint_argument> [, <hint_argument> ...]

<hint_argument> ::= hint argument type according to csv file (for example, one of STR_CONST, INT_CONST,  ID, or <table_ref>)

<routing_hint> ::=
   ROUTE_TO(<volumd_id> [{, <volumd_id> }] )
   | NO_ROUTE_TO( <volumd_id> [{, <volumd_id> }] )
   | ROUTE_BY( <table_name> [{, <table_name> }] )
   | ROUTE_BY_CARDINALITY( <table_name> [{, <table_name> }] )

<value_input> ::= MAX_CONCURRENCY (1) | DATA_TRANSFER_COST ({0 | 1})

When there are hint clauses in the subquery, only the outermost hint is applied. Additionally, hint arguments with a framework are also applied.

Examples:

SELECT T2.*FROM (SELECT MAX(COL) FROM T1 GROUP BY COL WITH HINT( NO_USE_OLAP_PLAN )) T2 WITH HINT( USE_OLAP_PLAN ); -- use_olap_plan hint is applied
SELECT T2.*FROM (SELECT MAX(COL) FROM T1  GROUP BY COL WITH HINT( NO_USE_OLAP_PLAN )) T2; -- no hint is applied

Hint Validation

Prior to SP09: incorrect syntax and/or semantics generates an SQL warning.

Starting from SP09: incorrect syntax and/or semantics generates an SQL error.

Hints by Category

Refer to each category for a more detailed description.

Hints for Controlling Request Processing

THROW_ERROR

Blocks the execution of a given query.

Examples:

SELECT * FROM T1 WITH HINT( THROW_ERROR );
ALTER SYSTEM ADD STATEMENT HINT ( THROW_ERROR ) FOR SELECT * FROM T1;

Hints for Controlling Cache Behaviors

SAP HANA supports different cache types.

SQL Plan Cache saves compiled plans so that the same query does not need to be compiled every time. This setting saves CPU and compilation time and enables the gathering of statistics (user name, execution count, avg execution time, and so on) for each plan.

IGNORE_PLAN_CACHE

Ignores the existing plan cache entry (if any) and forces the query to compile and execute.

  • If a plan cache entry already exists then it is ignored but still remains in the plan cache.
  • If a plan cache entry does not exist, then no plan cache entry is added.
USE_REMOTE_CACHE

Optimizes SAP HANA Hadoop and forces virtual table queries to have to a materialized result set. Subsequent queries are served from the materialized view.

Examples:

SELECT * FROM T1 WITH HINT( IGNORE_PLAN_CACHE );
SELECT * FROM T1 WITH HINT( USE_REMOTE_CACHE );

Hints for Scale-out Environment

In a scale-out environment, a query is routed to a certain node for execution. Query routing is determined in a round-robin manner to one of the nodes that contains related objects, such as tables and views.

You may want to specify preferred nodes or objects for routing for performance or any other possible issues. Volume ID or table name can be supplied to hints to enable such routing.

ROUTE_TO

Routes the query to the specified volume ID.

NO_ROUTE_TO

Avoids query routing to a specified volume ID.

ROUTE_BY

Routes the query to the specified table location.

ROUTE_BY_CARDINALITY

Routes the query to the location of the table with the highest cardinality from the input list.

DATA_TRANSFER_COST (<value>)

Guides the optimizer to use the weighting factor for the data transfer cost. The value 0 ignores the data transfer cost.

For a scale-out environment, the SQL optimizer decides on the execution location for each operator based on the operator cost and the data transfer cost.

Example:

CALL P1() WITH HINT( ROUTE_TO(1)); -- route to the node with volume id = 1
CALL P1() WITH HINT( NO_ROUTE_TO(2,3)); -- route to one of the nodes with a volume id other than 2 or 3
CALL P1() WITH HINT( ROUTE_BY(T2)); -- route to the node which has table T2
CALL P1() WITH HINT( ROUTE_BY_CARDINALITY(T1,T2,T3)); -- route to the node that has the highest cardinality table among T1, T2, and T3
CALL P1() 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));

Hints for Execution Engine Selection

The Column Search execution engine is chosen automatically by the optimizer based on the cost model.

USE_OLAP_PLAN

Guides the optimizer to prefer the OLAP engine over the Join engine.

NO_USE_OLAP_PLAN

Guides the optimizer to prefer the Join engine over the OLAP engine.

OLAP_PARALLEL_AGGREGATION (deprecated)

Guides the optimizer to prefer the OLAP engine for a column search.

Examples:

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

Hints for Join Engine Optimization

The following hints force a certain join operation behavior:

OPTIMIZE_METAMODEL

Creates concat attributes for joins with multiple columns (if they do not already exist) and uses them during the join calculation. Concat attributes need additional memory as they are persisted.

NO_OPTIMIZE_METAMODEL

Forces the use of the native hash-based multi-column join implementation and does not use concat attributes even if they should have already been created.

MAX_CONCURRENCY (1)

Controls concurrency. This setting only accepts the value 1 (single thread plan execution) and the Join Engine determines suitable parallelism by default.

Examples:

SELECT * FROM T1 WITH HINT( OPTIMIZE_METAMODEL );
SELECT * FROM T1 WITH HINT( NO_OPTIMIZE_METAMODEL );
SELECT * FROM T1 WITH HINT( MAX_CONCURRENCY(1) );
CS_JOIN_RESULT_MATERIALIZATION(<table_name>)

Specifies the table at which join materialization begins.

CS_JOIN_REDUCTION(<table_name>)

Specifies the table at which the reduction phase begins.

CS_JOIN_CYCLE_BREAK(<table_name_left>, <table_name_right>)
Specifies the join edge at which a cyclic join is broken up.
CS_JOIN_MATERIALIZATION_BIGGEST_TABLE_FIRST

Specifies that the materialization starts at the biggest table.

CS_JOIN_MATERIALIZATION_SMALLEST_TABLE_FIRST

Specifies that the materialization starts at the smallest table.

CS_JOIN_MATERIALIZATION_CONCURRENCY(<number>)

Defines the concurrency of the join materialization.

CS_JOIN_SHRINK_SIZE_THRESHOLD(<number>)

Specifies that the intermediate result exceeds this threshold, then tries to shrink it.

CS_JOIN_POST_CHECK_SIZE_THRESHOLD(<number>)

Specifies the intermediate result exceeds this threshold, then runs post-conditions and distinct checks on it.

Hints for Controlling Access Path

The following hints override the cost-based decision of the optimizer in order to utilize or avoid indexes for table access.

INDEX_SEARCH

Guides the optimizer to access the table by using the available index.

NO_INDEX_SEARCH

Guides the optimizer to avoid table access by using the available index.

Examples:

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

Hints for Controlling Join Operations

There are different join algorithms for performing join operations.

INDEX_JOIN

Guides the optimizer to join input relations through index searches.

NO_INDEX_JOIN

Guides the optimizer to avoid joining the input relations through index searches.

HASH_JOIN

Guides the optimizer to join the input relations through probing the hash table.

NO_HASH_JOIN

MIXED_INVERTED_INDEX_JOIN

Guides the optimizer to join the input relations of the row store format with a column table that is without a format. This conversion is done using an inverted index of the column table.Guides the optimizer to avoid joining the input relations through probing the hash table.

NO_MIXED_INVERTED_INDEX_JOIN

Guides the optimizer to avoid joining the input relations by using an inverted index of the column table.

OPTIMIZE_METAMODEL

Guides the optimizer to avoid joining the input relations through probing the hashCreates concat attributes for joins with multiple columns (if they do not already exist) and uses them during the join calculation. Concat attributes need additional memory as they are persisted.

NO_OPTIMIZE_METAMODEL

Forces the use of the native hash-based multi-column join implementation and does not use the concat attributes even if they should have already been created.

Examples:

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 );

Hints for Controlling Row/Column Store Operations

Most of the operations can be supported in both the row engine and the column engine. The optimizer selects the best operations based on the cost-model. The hints in this section can be used to control whether to prefer or avoid the use of specific engine operators in the query plan.

The following hints guide the optimizer to prefer the column engine operator:
  • CS_SORT
  • CS_LIMIT
  • CS_FILTER
  • CS_DISTINCT
  • CS_AGGR
  • CS_JOIN
  • CS_UNION_ALL
The following hints guide the optimizer to prefer the row engine operator:
  • NO_CS_SORT
  • NO_CS_LIMIT
  • NO_CS_FILTER
  • NO_CS_DISTINCT
  • NO_CS_AGGR
  • NO_CS_JOIN
  • NO_CS_UNION_ALL

Hints for Smart Data Access/Dynamic Tiering

Smart Data Access/Dynamic Tiering uses two kinds of operators to read data from the remote source: Remote Column Scan reads data from the remote server and stores it in ITAB and Remote Row Scan reads data from the remote server and stores it in a buffer.

REMOTE_COLUMN_SCAN

Guides the optimizer to prefer Remote Column Scan, which returns ITAB from the remote server.

NO_REMOTE_COLUMN_SCAN

Guides the optimizer to prefer Remote Row Scan.

REMOTE_JOIN_RELOCATION

Guides the optimizer to process the join at the remote server if possible (default behavior).

NO_REMOTE_JOIN_RELOCATION

Guides the optimizer to process the join at the HANA server if possible.

REMOTE_EXPR_MATERIALIZATION

Guides the optimizer to prefer the expression evaluation at the remote server if possible (default behavior).

NO_REMOTE_EXPR_MATERIALIZATION

Guides the optimizer to prefer the expression evaluation at the HANA server if possible.

REMOTE_PREAGGR

Guides the optimizer to generate pre-aggregation at the remote server (default behavior).

NO_REMOTE_PREAGGR

Guides the optimizer not to generate pre-aggregation at the remote server.

REMOTE_AGGR
Guides the optimizer to process aggregation at remote data source if possible (default behavior).
NO_REMOTE_AGGR
Guides the optimizer to process aggregation at HANA server if possible.
REMOTE_JOIN
Guides the optimizer to process the join at the remote server if possible. When used with NO_REMOTE_JOIN_RELOCATION, it prefers processing of the join involving tables from the same remote source at the remote server (default behavior).
NO_REMOTE_JOIN
Guides the optimizer to process the join between tables at HANA server, even if the tables are from the same remote source.
REMOTE_DISTINCT
Guides the optimizer to process the distinct operation at the remote server if possible (default behavior).
NO_REMOTE_DISTINCT

Guides the optimizer to process the distinct operation at the HANA server if possible.

REMOTE_UNION
Guides the optimizer to process union operation at the remote server if possible (default behavior).
NO_REMOTE_UNION
Guides the optimizer to process the union operation at the HANA server if possible.

Examples:

SELECT * FROM T1 WITH HINT( REMOTE_COLUMN_SCAN );
SELECT * FROM T1 WITH HINT( REMOTE_JOIN_RELOCATION );
SELECT * FROM T1 WITH HINT( REMOTE_EXPR_MATERIALIZATION );
SELECT * FROM T1 WITH HINT( REMOTE_PREAGGR );
SELECT SUM(HT.C1) FROM RT  JOIN HT ON  RT.C1 = HT.C1 GROUP BY  RT.C1 WITH HINT(REMOTE_AGGR)
SELECT * FROM RT  JOIN HT ON  RT.C1= HT.C1 WITH HINT(REMOTE_JOIN)
SELECT DISTINCT(HT.C1) FROM RT  JOIN HT ON  RT.C1 = HT.C1 WITH HINT(REMOTE_DISTINCT)
SELECT * FROM RT UNION SELECT * FROM HT WITH HINT(REMOTE_UNION)

Hints for Result Cache

Use the result cache for applications that accept stale data access. Table functions and SQL/Calculation views can be cached. Since the result cache shows stale data, it can only be used when suitable hints are turned on.

RESULT_CACHE

Guides the optimizer to utilize the result cache regardless of indexserver configuration, if the result cache is available.

RESULT_CACHE_MAX_LAG(<seconds>)

Guides the optimizer to set the retention period of the result cache to a minimum of this value or the value set in the ADD CACHE configuration.

RESULT_CACHE_NON_TRANSACTIONAL

Allows join or union operations using the result cache entries and disregards possible transaction inconsistencies.

RESULT_CACHE_NO_REFRESH

Accesses cached data if cached data is prepared before, without refreshing it, even when its retention period is over.

RESULT_LAG('<name>'[, <seconds>])

Guides the optimizer to use specified engines if its result lag is less than the maximum seconds.

RESULT_CACHE_AFTER_ANALYTIC_PRIVILEGE

Enforces the result cache to use separate data cached after filtering with analytic privileges. This hint does not enforce using the result cache and it is only effective when the query can use the result cache.

RESULT_CACHE_BEFORE_ANALYTIC_PRIVILEGE

Enables the result cache to use shared data cached before filtering with analytic privileges. This hint does not enforce using the result cache and it is only effective when the query can use the result cache.

Examples:

SELECT * FROM T1 WITH HINT( NO_RESULT_CACHE );
SELECT * FROM T1 WITH HINT( RESULT_CACHE );
SELECT * FROM T1 WITH HINT( RESULT_CACHE_MAX_LAG(60) );
SELECT * FROM T1 WITH HINT( RESULT_CACHE_NON_TRANSACTIONAL );
SELECT * FROM T1 WITH HINT( RESULT_LAG ('hana_long') );
SELECT * FROM T1 WITH HINT( RESULT_LAG ( 'hana_long', 30) );
SELECT * FROM T1 WITH HINT( RESULT_CACHE_AFTER_ANALYTIC_PRIVILEGE );
SELECT * FROM T1 WITH HINT( RESULT_CACHE_BEFORE_ANALYTIC_PRIVILEGE );

Hints for Dynamic Result Cache

Returns an up-to-date query result. This means that the result of the query run on the dynamic result cache is always the same as the result of a query that is not using the cache.

NO_DYNAMIC_RESULT_CACHE

Guides the optimizer not to utilize the dynamic result cache regardless of indexserver configuration, even if it is available.

DYNAMIC_RESULT_CACHE

Guides the optimizer to utilize the dynamic result cache if the dynamic result cache is available (default).

NO_DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH

Guides the optimizer not to try automatic view matching (default).

DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH

Guides the optimizer to try automatic view matching regardless of indexserver configuration.

DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH

Guides the optimizer to try automatic view matching only with given views.

DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH(<schema_name>.<view_name>[,<schema_name>.<view_name>…]);

Examples:

SELECT * FROM V1 WITH HINT( NO_DYNAMIC_RESULT_CACHE );
SELECT * FROM V1 WITH HINT( DYNAMIC_RESULT_CACHE);
SELECT * FROM T1 WITH HINT( NO_DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH );
SELECT * FROM T1 WITH HINT( DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH );
SELECT * FROM T1 WITH HINT( DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH ("TEST"."V1") );

Hints for Asynchronous Table Replication

Used for applications that accept stale data access. Since the asynchronous table shows stale data, it can only be used with suitable hints or to access the replica table using the explicit replica table name.

RESULT_LAG
Guides the optimizer to use specified engines if its result lag is less than the maximum number of seconds.
RESULT_LAG('<name>' [ ,<seconds>])

Examples:

SELECT * FROM T1 WITH HINT( RESULT_LAG ('hana_short') );
SELECT * FROM T1 WITH HINT( RESULT_LAG ('hana_atr') );

Hints for Column View Cardinality Estimation

Column Views (Join views, OLAP views, and Calculation views) are relations which can be used in SQL plans. Cardinality estimation of such view objects are necessary for the SQL Optimizer to find an optimal plan.

The optimizer has three different estimation methods, each of which has its own trade-offs in terms of the quality and efficiency. The optimizer utilizes an appropriate method among them, considering the complexity of a given plan.

These hints maneuver the optimizer to use a designated method for cardinality estimation of all of the related Column views.

NO_COLUMN_VIEW_ESTIMATION

Gets a record count estimation of the largest base table (legacy).

COLUMN_VIEW_ESTIMATION(RECORD COUNT)

Guides the optimizer to use the output record count estimation, which is done in each engine layer.

COLUMN_VIEW_ESTIMATION(SIMPLE)

Guides the optimizer to use a comprehensive estimation method to retrieve SIMPLE histograms (record count, distinct value count, and so on) for all of the requested attributes.

COLUMN_VIEW_ESTIMATION

Guides the optimizer to utilize the latest estimation method.

Examples:

SELECT * FROM T1 WITH HINT( NO_COLUMN_VIEW_ESTIMATION );
SELECT * FROM T1 WITH HINT( COLUMN_VIEW_ESTIMATION (RECORD COUNT) );
SELECT * FROM T1 WITH HINT( COLUMN_VIEW_ESTIMATION (SIMPLE) );
SELECT * FROM T1 WITH HINT( COLUMN_VIEW_ESTIMATION ); 

Hints for Access Path Optimization on Column Store Tables

CS_PRIMARY_KEY

Forces the use of the primary key, even though the optimizer estimates otherwise. This hint is only applicable if the key is not fully defined in the query.

NO_CS_PRIMARY_KEY

Forbids the use of the primary key, although the optimizer estimates otherwise. This hint is only applicable if the key is not fully defined in the query.

CS_ESTIMATION(SIMPLE | ALL)

Controls the estimation phase of the column-store table query. ALL (default) estimates all of the predicates and SIMPLE skips predicates that are too expensive to estimate.

NO_CS_ESTIMATION

Skips the estimation phase of the column-store table query completely.

CS_FILTER_FIRST(<column>)

Specifies the preferred starting column in a conjunction.

Hints for Active/Active (Read Enabled)

Routes the statement to a secondary site on an Active/Active (read enabled) system.

RESULT_LAG
Guides the optimizer to use the secondary system. The routed statement queries data on the secondary system. The lag time is the time between the commit on the primary system and visibility on the secondary system. If the lag time on the secondary system exceeds the specified time on the hint, then the statement may be re-routed to the primary system implicitly.
RESULT_LAG( '<hana_sr>' [ , <seconds> ] )

Examples:

SELECT * FROM T1 WITH HINT( RESULT_LAG ('hana_sr') );
SELECT * FROM T1 WITH HINT( RESULT_LAG ('hana_sr', 60) );

Hints for Workload Management

Provides a method to set another workload class for a query, regardless of any workload mappings.

WORKLOAD_CLASS
Designates a certain workload class for the statement execution.
  • The workload class must be created before using this hint.
  • If the specified workload class does not exist, the hint is ignored and the corresponding SQL warning is returned for the statement execution.
  • Only workload classes which have more restricted properties compared to the ones set for session-wise admission control are applied. Otherwise, the hint is ignored and the corresponding SQL warning is returned. This hint clause only allows priority, thread limit, and memory limit down-wise.

Examples:

CREATE WORKLOAD CLASS "MY_WORKLOAD_CLASS_1" SET 'PRIORITY'='9';
SELECT * FROM T1 WITH HINT( WORKLOAD_CLASS("MY_WORKLOAD_CLASS_1") );

The following examples suppose that two workload classes are matched to the current execution request:

Case 1
  • Workload class 'MY_WORKLOAD_CLASS1' fit by mapping: PRIORITY 5, THREAD 5, MEMORY 50GB
  • Workload class 'MY_WORKLOAD_CLASS2' fit by HINT: PRIORITY 4, THREAD 4, MEMORY 30GB

The hint clause is applied and the effective values are: PRIORITY 4, THREAD 4, and MEMORY 30 GB.

Case 2
  • Workload class 'MY_WORKLOAD_CLASS1' fit by mapping: PRIORITY 5, THREAD 5, MEMORY 50GB
  • Workload class 'MY_WORKLOAD_CLASS2' fit by HINT: PRIORITY 4, THREAD undefined, MEMORY 30GB

The hint clause is ignored because the thread limit is not defined.

Case 3
  • Workload class 'MY_WORKLOAD_CLASS1' fit by mapping: PRIORITY 5, THREAD undefined, MEMORY 50GB
  • Workload class 'MY_WORKLOAD_CLASS2' fit by HINT: PRIORITY 4, THREAD undefined, MEMORY 30GB

The hint clause is applied because both thread limits are not defined.

Case 4
  • Workload class 'MY_WORKLOAD_CLASS1' fit by mapping: PRIORITY 5, THREAD undefined, MEMORY 50GB
  • Workload class 'MY_WORKLOAD_CLASS2' fit by HINT: PRIORITY 4, THREAD undefined, MEMORY 70GB

The hint clause is ignored because the memory limit exceeded the 50 GB that is defined by the mapping.

Hints for Size Estimation

Size Estimations are related to the SQL Optimizer and are necessary to find an optimal plan.

ESTIMATION_SAMPLES(<number>)
  • Controls the number of samples that are randomly picked from each of the base tables. The samples estimate local filter selectivity, as well as join condition selectivity. You can increase the number of samples to increase the accuracy of the estimation in exchange for a longer compilation time.
  • By default, the <number> value is 1000 when there is no hint.
  • If the <number> is 0, then no sampling is done and all sampling related estimation is skipped.
  • A <number> value which is below 0 is not allowed and throws an exception.
JOIN_SAMPLING, NO_JOIN_SAMPLING
  • Turns join selectivity sampling on or off.
  • By default join sampling is enabled.
  • The feature also turns off when ESTIMATION_SAMPLES(0) is specified because there are no samples to use.
JOIN_SKEW_ESTIMATION, NO_JOIN_SKEW_ESTIMATION
  • Turns join skew estimation using the top k value from base tables on or off.
  • By default join skew estimation is enabled, but depending on the ini configuration, it can be turned off. The hint has higher priority over the ini configuration settings.
  • Join skew estimation is internally implemented independently from join sampling. Join sampling uses randomly selected samples, whereas skew factor computations use the top 10 most frequent values provided by the column store. Their related issues have different characteristics: Join sampling related issues are usually performance related issues whereas Skew factor computation issues are usually crash problems.
TABLE_FILTER_ESTIMATION(MINIMAL | SAMPLING | ALL)
  • Controls the level of techniques used to estimate the table filter selectivity. Lower-level use is a subset of techniques that are used in the upper level.
  • MINIMAL: uses heuristic-based selectivity estimation using only base statistics (table row count and distinct count of each column).
  • SAMPLING: uses basic sampling for filter estimation together with heuristic-based estimation.
  • ALL (default): uses table filter estimation. Newly added estimation techniques are only included for this level.
DISTRIBUTED_ESTIMATION, NO_DISTRIBUTED_ESTIMATION
  • Turns remote size statistics (row count/distinct count/filter selectivity of remote tables) retrieval on or off.
  • By default remote statistics retrieval is enabled, but depending on the initial configuration, it can be turned off. The hint has higher priority over the initial configuration setting.

Examples:

SELECT * FROM T1 WITH HINT( ESTIMATION_SAMPLES(0) );
SELECT * FROM T1 WITH HINT( JOIN_SAMPLING );
SELECT * FROM T1 WITH HINT( NO_JOIN_SKEW_ESTIMATION );
SELECT * FROM T1 WITH HINT( TABLE_FILTER_ESTIMATION(SAMPLING) );
SELECT * FROM T1 WITH HINT( NO_DISTRIBUTED_ESTIMATION );

Hints for SQL Optimizer Plan Generation

There can be a plan where the same sequence of execution repeats multiple times, each with a different requester. For such plans, the optimizer can perform the sequence of executions only once and pass the results to all the requesters depending on the cost model (called subplan sharing).

SUBPLAN_SHARING

Guides the optimizer to prefer choosing the shared subplan.

  • Accepts view names as optional parameters to provide more granular control of subplan sharing.
  • Accepts aliases and nested views (optional).
NO_SUBPLAN_SHARING

Guides the optimizer to unfold the shared subplan.

  • Accepts view names as optional parameters to provide more granular control of unfolding the shared subplan.
  • Accepts aliases and nested views (optional).
SELECT * FROM T1 WITH HINT( SUBPLAN_SHARING );
SELECT * FROM VIEW1 WITH HINT( SUBPLAN_SHARING (VIEW1) );
SELECT * FROM T1 WITH HINT( NO_SUBPLAN_SHARING );
SELECT * FROM VIEW1,VIEW2 WITH HINT( NO_SUBPLAN_SHARING (VIEW1, VIEW2) );
SELECT * FROM VIEW1,VIEW2 WITH HINT( SUBPLAN_SHARING (VIEW1), NO_SUBPLAN_SHARING (VIEW2) ); --forces subplan sharing on view1 and forces no subplan sharing on view2. The rest of the shared views are generated following default logic.
SELECT * FROM VIEWS WITH HINT( SUBPLAN_SHARING (VIEW1), NO_SUBPLAN_SHARING ); --forces subplan sharing on view1 and forces no subplan sharing on any other possible shared views.

Calculation views are unfolded during plan generation to enable optimization using the SQL Optimizer whenever possible. Normally an unfolding calculation view is beneficial, but there can be cases where it leads to a degradation in performance.

CALC_VIEW_UNFOLDING

Guides the optimizer to unfold calculation views.

  • Accepts view names as optional parameters to provide more granular control of calculation view unfolding.
  • Accepts nested views but does not accept aliases (optional).
NO_CALC_VIEW_UNFOLDING

Guides the optimizer to preserve the calculation view (does not unfold the calculation view).

  • Accepts view names as optional parameters to provide more granular control of calculation view unfolding.
  • Accepts nested views but does not accept aliases (optional).

Examples:

SELECT * FROM T1 WITH HINT( CALC_VIEW_UNFOLDING ); 
SELECT * FROM VIEW1 WITH HINT( CALC_VIEW_UNFOLDING ("SYS_BIC"."CALC_VIEW") ); 
SELECT * FROM T1 WITH HINT( NO_CALC_VIEW_UNFOLDING ); 
SELECT * FROM VIEW1,VIEW2 WITH HINT( NO_CALC_VIEW_UNFOLDING ("SYS_BIC"."CALC_VIEW1", "SYS_BIC"."CALC_VIEW2") );
SELECT * FROM VIEW1,VIEW2 WITH HINT( CALC_VIEW_UNFOLDING ("SYS_BIC"."CALC_VIEW1"), NO_CALC_VIEW_UNFOLDING ("SYS_BIC"."CALC_VIEW2") ); --forces calculation view unfolding on view1 and forces no calculation view unfolding on view2. The rest of the calculation views are generated following default logic.
SELECT * FROM VIEWS WITH HINT( CALC_VIEW_UNFOLDING (("SYS_BIC"."CALC_VIEW1"), NO_CALC_VIEW_UNFOLDING );  --forces calculation view unfolding on view1 and forces no calculation view unfolding on any other calculation views.

There are optimization levels created during plan generation to enable optimization using the SQL Optimizer. Normally, a high optimization level is beneficial but selecting another level can be useful in some cases when optimization leads to a degradation in performance.

Table 39: OPTIMIZATION_LEVEL

Hint Name

Description

OPTIMIZATION_LEVEL(MINIMAL)

MINIMAL: all rewriting rules.

OPTIMIZATION_LEVEL(RULE_BASED)

RULE_BASED: heuristic join reordering.

OPTIMIZATION_LEVEL(MINIMAL_COST_BASED)

Default option: MINIMAL_COST_BASED plus available cost-based optimizations (including logical enumeration).

OPTIMIZATION_LEVEL(COST_BASED)

Disables all optimization rules, except mandatory ones, to execute the user query as it is.

  • Accepts ID-type arguments (MINIMAL | RULE_BASED | MINIMAL_COST_BASED | COST_BASED).
  • COST_BASED is the default optimization level which enables all optimization steps.
  • Other hints that enable/disable rewriting rules are applied regardless of the OPTIMIZATION_LEVEL.

Examples:

SELECT * FROM TABLES WITH HINT (OPTIMIZATION_LEVEL(MINIMAL))
SELECT * FROM TABLES WITH HINT (OPTIMIZATION_LEVEL(MINIMAL), JOIN_REMOVAL) --JOIN_REMOVAL is applied even though it is not a mandatory rule.
SELECT * FROM TABLES WITH HINT (OPTIMIZATION_LEVEL(RULE_BASED))
SELECT * FROM TABLES WITH HINT (OPTIMIZATION_LEVEL(MINIMAL_COST_BASED))
SELECT * FROM TABLES WITH HINT (OPTIMIZATION_LEVEL(COST_BASED), NO_JOIN_REMOVAL)

Inter-operator parallelism for multiple column search operator is decided during plan generation to enable optimization using the SQL Optimizer. Normally parallelism is beneficial in performance, but minimizing parallelism is useful in some cases.

PARALLEL_COLUMN_SEARCH

Guides the optimizer to force column search operator parallelism.

NO_PARALLEL_COLUMN_SEARCH

Guides the optimizer to minimize column search operator parallelism.

Example:

SELECT * FROM (SELECT t1.a, t1.b, SUM(t1.c) AS s1 , ROW_NUMBER() OVER (PARTITION by t1.b) AS rr1 FROM t1,t2 WHERE t1.a+0=t2.a+0 GROUP BY t1.a, t1.b) UNION ALL (SELECT t2.a, t2.b, SUM(t2.c) AS s2, ROW_NUMBER() OVER (PARTITION BY t2.b) AS rr2 FROM t2 GROUP BY t2.a, t2.b) WITH HINT (NO_CS_EXPR_JOIN, PARALLEL_COLUMN_SEARCH)

Generated plan:

Without the hint, TS1 is not parallelized, which means an OLTP search may be used. However, you can parallelize TS1 by using the hint PARALLEL_COLUMN_SEARCH.

The predicate is simplified during plan generation to enable optimization using the SQL Optimizer whenever possible. Normally, simplifying the predicate is beneficial but there can be cases where it leads to a degradation in performance.

NO_PREDICATE_SIMPLIFICATION

Guides the optimizer to disable predicate simplification. This setting applies to all internal predicates.

Example:

Given predicate: a != 0 and a = 0 Without hint: const false With hint: a != 0 and a = 0

Constant values are pre-evaluated during plan generation to enable optimization using the SQL Optimizer whenever possible. Normally pre-evaluating constant values is beneficial but there can be cases where it leads to a degradation in performance.

NO_CONST_PREEVALUATION

Guides the optimizer to disable constant pre-evaluation. This is applied to all internal constant values.

Example:

Given constant value: a = 0 + 1 Without hint: a = 1 With hint: a = 0 + 1
JOIN_FILTER_REORDERING

Guides the optimizer to reorder join predicates inside of conjunctive and disjunctive conditions.

  • Reordering is done by using the estimated selectivity of each predicate inside of conjunctive and disjunctive terms.
  • Can only be applied to row store join operations.
OLAP_FACT_TABLE("<table_name>")

Chooses a fact table whose name is as given.

  • If the <table_name> does not exist, then the hint is ignored.
  • If candidate table exists more than two times in one column search (for example, in a self-join), then one of the tables is chosen by the current fact table decision heuristic logic.
(NO_)RECOMPILE_WITH_SQL_PARAMETERS

Guides the optimizer to enable/disable recompilation if a query has parameters.

  • The default behavior for compilation is to compile without parameters.
  • The default behavior for execution is to recompile with parameters if parameters exist and the plan is not based on heuristics.
  • When used with the RECOMPILE_WITH_SQL_PARAMETERS hint, this is compiled without parameters.
  • When used with the RECOMPILE_WITH_SQL_PARAMETERS hint, execution is performed by recompiling with parameters if parameters exist.
  • When used with the NO_RECOMPILE_WITH_SQL_PARAMETERS hint, this is compiled without parameters.

Hints for Query Rewriting and Logical Transformations

The SQL Optimizer relies heavily on query rewriting and logical transformation rules to enumerate all possible plans to find the best plan in the potential search space. Using the hints below can help you manipulate the SQL Optimizer and provide a quick workaround to fix various issues. For example:

SELECT * FROM T1 WITH HINT( JOIN_REMOVAL ); SELECT * FROM T1 WITH HINT(  NO_JOIN_REMOVAL );

Refer to the below table for a list of hints and their descriptions:

Category

Hint Name

Description

Available from

OPTIMIZER_REWRITE DISJ_FILTER_TRANSFORMATION Prefers a split disjunctive filter into union all. SP9
OPTIMIZER_REWRITE NO_DISJ_FILTER_TRANSFORMATION Avoids a split disjunctive filter into union all. SP9
OPTIMIZER_REWRITE JOIN_SIMPLIFICATION Prefers join simplification. SP9
OPTIMIZER_REWRITE NO_JOIN_SIMPLIFICATION Avoids join simplification. SP9
OPTIMIZER_REWRITE GROUPING_REMOVAL Prefers unnecessary grouping removal. SP9
OPTIMIZER_REWRITE NO_GROUPING_REMOVAL Avoids unnecessary grouping removal. SP9
OPTIMIZER_REWRITE AGGR_SIMPLIFICATION Prefers aggregation simplification. SP9
OPTIMIZER_REWRITE NO_AGGR_SIMPLIFICATION Avoids aggregation simplification. SP9
OPTIMIZER_REWRITE MATERIALIZED_COLUMN_REMOVAL Prefers an unnecessary column removal. SP9
OPTIMIZER_REWRITE NO_MATERIALIZED_COLUMN_REMOVAL Avoids an unnecessary column removal. SP9
OPTIMIZER_REWRITE JOIN_REMOVAL Prefers an unnecessary join removal. SP9
OPTIMIZER_REWRITE NO_JOIN_REMOVAL Avoids an unnecessary join removal. SP9
OPTIMIZER_REWRITE GROUPING_SIMPLIFICATION Prefers a groupby simplification. SP9
OPTIMIZER_REWRITE NO_GROUPING_SIMPLIFICATION Avoids a groupby simplification. SP9
OPTIMIZER_REWRITE CONST_VIEW_UNFOLDING Prefers constant view unfolding. SP9
OPTIMIZER_REWRITE NO_CONST_VIEW_UNFOLDING Avoids constant view unfolding. SP9
OPTIMIZER_REWRITE HOST_PORT_JOIN_COLOCATION Prefers a host/port join thru union. SP9
OPTIMIZER_REWRITE NO_HOST_PORT_JOIN_COLOCATION Avoids a host/port join thru union. SP9
OPTIMIZER_REWRITE CALC_VIEW_UNFOLDING Prefers the unfold calculation view. SP9
OPTIMIZER_REWRITE NO_CALC_VIEW_UNFOLDING Avoids the unfold calculation view. SP9
OPTIMIZER_REWRITE TYPE_CAST_REMOVAL Prefers the removal of a redundant type cast. SP9
OPTIMIZER_REWRITE NO_TYPE_CAST_REMOVAL Avoids the removal of a redundant type cast. SP9
OPTIMIZER_REWRITE WINDOW_REMOVAL Prefers the removal of window function aggregation. SP9
OPTIMIZER_REWRITE NO_WINDOW_REMOVAL Avoids the removal of window function aggregation. SP9
OPTIMIZER_REWRITE JOIN_REMOVAL_USING_CARDINALITY Prefers join removal based on cardinality. SP9
OPTIMIZER_REWRITE NO_JOIN_REMOVAL_USING_CARDINALITY Avoids join removal based on cardinality. SP9
OPTIMIZER_REWRITE PARTITION_PRUNING Prefers compile-time partition pruning. SP11
OPTIMIZER_REWRITE NO_PARTITION_PRUNING Avoids compile-time partition pruning. SP11
OPTIMIZER_REWRITE FILTER_RULE Prefers applying the filter rule. SP01
OPTIMIZER_REWRITE NO_FILTER_RULE Avoids applying the filter rule. SP01
OPTIMIZER_REWRITE JOIN_RULE Prefers applying the join rule. SP01
OPTIMIZER_REWRITE NO_JOIN_RULE Avoids applying the join rule. SP01
OPTIMIZER_LOGICAL_ENUMERATION AGGR_THRU_FILTER Prefers push down aggregation through a filter. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_AGGR_THRU_FILTER Avoids push down aggregation through a filter. SP9
OPTIMIZER_LOGICAL_ENUMERATION AGGR_THRU_JOIN Prefers push down aggregation through join. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_AGGR_THRU_JOIN Avoids push down aggregation through join. SP9
OPTIMIZER_LOGICAL_ENUMERATION DISTINCT_THRU_UNION Prefers push down distinct through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_DISTINCT_THRU_UNION Avoids push down distinct through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION AGGR_INTO_AGGR Prefers merging two aggregations into one. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_AGGR_INTO_AGGR Avoids merging two aggregations into one. SP9
OPTIMIZER_LOGICAL_ENUMERATION AGGR_INTO_TABLE Prefers merging aggregation with table scans. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_AGGR_INTO_TABLE Avoids merging aggregation with table scans. SP9
OPTIMIZER_LOGICAL_ENUMERATION DISTINCT_INTO_DISTINCT Prefers merging two distincts into one. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_DISTINCT_INTO_DISTINCT Avoids merging two distincts into one. SP9
OPTIMIZER_LOGICAL_ENUMERATION DISTINCT_INTO_SEMI_JOIN Prefers merging a distinct with a join. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_DISTINCT_INTO_SEMI_JOIN Avoids merging a distinct with a join. SP9
OPTIMIZER_LOGICAL_ENUMERATION UNION_INTO_UNION Prefers merge_union_all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_UNION_INTO_UNION Avoids merge_union_all. SP9
OPTIMIZER_LOGICAL_ENUMERATION UNION _INTO_UNION _THRU_AGGR Prefers merge union all through aggregation. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_UNION _INTO_UNION _THRU_AGGR Avoids merge union all through aggregation. SP9
OPTIMIZER_LOGICAL_ENUMERATION PREAGGR_BEFORE_JOIN Prefers pre-aggregation before join. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_PREAGGR_BEFORE_JOIN Avoids pre-aggregation before join. SP9
OPTIMIZER_LOGICAL_ENUMERATION DOUBLE_PREAGGR_BEFORE_JOIN Prefers pre-aggregation before join for both of the children. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_DOUBLE_PREAGGR_BEFORE_JOIN Avoids pre-aggregation before join for both of the children. SP9
OPTIMIZER_LOGICAL_ENUMERATION PREAGGR_BEFORE_UNION Prefers pre-aggregation before union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_PREAGGR_BEFORE_UNION Avoids pre-aggregation before union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION DISJ_JOIN_INTO_UNION Prefers to split disjunctive join predicates into filters and making union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_DISJ_JOIN_INTO_UNION Avoids splittting disjunctive join predicates into filters and making union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION PREDISTINCT_BEFORE_SEMI_JOIN Prefers putting DISTINCT on the right child of SEMI or ANTI SEMI joins. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_PREDISTINCT_BEFORE_SEMI_JOIN Avoids putting DISTINCT on the right child of SEMI or ANTI SEMI joins. SP9
OPTIMIZER_LOGICAL_ENUMERATION JOIN_THRU_AGGR Prefers pushing down joins through aggregation. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_JOIN_THRU_AGGR Avoids pushing down joins through aggregation. SP9
OPTIMIZER_LOGICAL_ENUMERATION JOIN_THRU_FILTER Prefers pushing down joins through the filter. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_JOIN_THRU_FILTER Avoids pushing down joins through the filter. SP9
OPTIMIZER_LOGICAL_ENUMERATION JOIN_THRU_JOIN Prefers pushing down joins through join. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_JOIN_THRU_JOIN Avoids pushing down joins through join. SP9
OPTIMIZER_LOGICAL_ENUMERATION JOIN_THRU_UNION Prefers pushing down joins through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_JOIN_THRU_UNION Avoids pushing down joins through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION SEMI_JOIN_BEFORE_UNION Prefers pushing down semi-joins through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_SEMI_JOIN_BEFORE_UNION Avoids pushing down semi-joins through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION FILTER_THRU_JOIN Prefers pushing down filters through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_FILTER_THRU_JOIN Avoids pushing down filters through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION FILTER_THRU_AGGR Prefers pushing down filters through aggregation. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_FILTER_THRU_AGGR Avoids pushing down filters through aggregation. SP9
OPTIMIZER_LOGICAL_ENUMERATION FILTER_THRU_UNION Prefers pushing down filters through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_FILTER_THRU_UNION Avoids pushing down filters through union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION PRELIMIT_BEFORE_UNION Prefers pushing down limits before union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_PRELIMIT_BEFORE_UNION Avoids pushing down limits before union all. SP9
OPTIMIZER_LOGICAL_ENUMERATION LIMIT_THRU_JOIN Prefers pushing down limits through joins. SP11
OPTIMIZER_LOGICAL_ENUMERATION NO_LIMIT_THRU_JOIN Avoids pushing down limits through joins. SP11
OPTIMIZER_LOGICAL_ENUMERATION PRELIMIT_BEFORE_JOIN Prefers LIMIT/SORT on the left child of the LEFT OUTER JOIN. SP9
OPTIMIZER_LOGICAL_ENUMERATION NO_PRELIMIT_BEFORE_JOIN Avoids LIMIT/SORT on the left child of the LEFT OUTER JOIN. SP9
OPTIMIZER_PHYSICAL_ENUMERATION INDEX_SEARCH Prefers the row engine index search. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_INDEX_SEARCH Avoids the row engine index search. SP9
OPTIMIZER_PHYSICAL_ENUMERATION INDEX_JOIN Prefers the row engine index join. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_INDEX_JOIN Avoids the row engine index join. SP9
OPTIMIZER_PHYSICAL_ENUMERATION HASH_JOIN Prefers the row engine hash join. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_HASH_JOIN Avoids the row engine hash join. SP9
OPTIMIZER_PHYSICAL_ENUMERATION RANGE_JOIN Prefers the row engine range join. SP11
OPTIMIZER_PHYSICAL_ENUMERATION NO_RANGE_JOIN Avoids the row engine range join. SP11
OPTIMIZER_PHYSICAL_ENUMERATION HASHED_RANGE_JOIN Prefers the row engine hashed range join. SP11
OPTIMIZER_PHYSICAL_ENUMERATION NO_HASHED_RANGE_JOIN Avoids the row engine hashed range join. SP11
OPTIMIZER_PHYSICAL_ENUMERATION JOIN_FILTER_REORDERING Prefers join predicate reordering. SP11
OPTIMIZER_PHYSICAL_ENUMERATION NO_JOIN_FILTER_REORDERING Avoids join predicate reordering. SP11
OPTIMIZER_PHYSICAL_ENUMERATION MIXED_INVERTED_INDEX_JOIN Prefers the mixed index  join. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_MIXED_INVERTED_INDEX_JOIN Avoids the mixed index join. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_SORT Prefers the column engine order by. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_SORT Avoids the column engine order by. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_LIMIT Prefers the column engine limit. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_LIMIT Avoids the column engine limit. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_FILTER Prefers the column engine filter. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_FILTER Avoids the column engine filter. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_DISTINCT Prefers the column engine distinct. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_DISTINCT Avoids the column engine distinct. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_AGGR Prefers column engine aggregation. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_AGGR Avoids column engine aggregation. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_JOIN Prefers column engine join. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_JOIN Avoids the column engine join. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_SCALAR_SUBQUERY Prefers the column engine scalar subquery. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_SCALAR_SUBQUERY Avoids the column engine scalar subquery. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_ITAB_IN_SUBQUERY Prefers the column engine ITAB in the subquery. SP9 (extended to normal column tables in SP12)
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_ITAB_IN_SUBQUERY Avoids the column engine ITAB in the subquery. SP9 (extended to normal column tables in SP12)
OPTIMIZER_PHYSICAL_ENUMERATION CS_ITAB_IN_PREDICATE Prefers the column engine ITAB for multi-columns in predicates. SP12
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_ITAB_IN_PREDICATE Avoids the column engine ITAB for multi-columns in predicates. SP12
OPTIMIZER_PHYSICAL_ENUMERATION CS_UNION_ALL Prefers the column engine union all. SP9 (extended to utilize new column union all in SP12)
OPTIMIZER_PHYSICAL_ENUMERATION CS_UNION_ALL(CALC) Prefers the calc engine union all. SP12 (extended to utilize the old CE column union all in SP12)
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_UNION_ALL Avoids the column engine union all. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_WINDOW Prefers the column engine window for row numbers only. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_WINDOW Avoids the column engine window for row numbers only. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CYCLIC_JOIN Prefers cyclic joins in a single column search. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_CYCLIC_JOIN Avoids cyclic joins in a single column search. SP9
OPTIMIZER_PHYSICAL_ENUMERATION OLAP_GUIDED_NAVIGATION Prefers OLAP engine guided navigation. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_OLAP_GUIDED_NAVIGATION Avoids OLAP engine guided navigation. SP9
OPTIMIZER_PHYSICAL_ENUMERATION OLAP_FEMS Prefers the OLAP engine fems. SP9
OPTIMIZER_PHYSICAL_ENUMERATION NO_OLAP_FEMS Avoids the OLAP engine fems. SP9
OPTIMIZER_PHYSICAL_ENUMERATION CS_EXPR_JOIN Prefers the column engine expression join. SP10
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_EXPR_JOIN Avoids the column engine expression join. SP10
OPTIMIZER_PHYSICAL_ENUMERATION CS_SEMI_JOIN Prefers the column engine semi-join. SP12
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_SEMI_JOIN Avoids the column engine semi-join. SP12
OPTIMIZER_PHYSICAL_ENUMERATION CS_OUTER_JOIN_WITH_FILTER Prefers the outer join with the filter pushed down to the column engine. SP12
OPTIMIZER_PHYSICAL_ENUMERATION NO_CS_OUTER_JOIN_WITH_FILTER Avoids the outer join with the filter pushed down to the column engine. SP12
OPTIMIZER_PHYSICAL_ENUMERATION REMOTE_COLUMN_SCAN Prefers the ITAB-producing remote scan operator. SP10
OPTIMIZER_PHYSICAL_ENUMERATION NO_REMOTE_COLUMN_SCAN Avoids the ITAB-producing remote scan operator. SP10
OPTIMIZER_PHYSICAL_ENUMERATION REMOTE_JOIN_RELOCATION Prefers join relocation. Performs a JOIN between SAP HANA and the remote table at the remote database. SP10
OPTIMIZER_PHYSICAL_ENUMERATION NO_REMOTE_JOIN_RELOCATION Avoids join relocation. Performs a JOIN between SAP HANA and the remote table at the remote database. SP10
OPTIMIZER_PHYSICAL_ENUMERATION REMOTE_EXPR_MATERIALIZATION Prefers expression materialization at the remote database. SP10
OPTIMIZER_PHYSICAL_ENUMERATION NO_REMOTE_EXPR_MATERIALIZATION Avoids expression materialization at the remote database. SP10 
OPTIMIZER_PHYSICAL_ENUMERATION DISJUNCTIVE_HASH_JOIN Prefers disjunctive hash join implementation. SAP HANA 2.0 SP00
OPTIMIZER_PHYSICAL_ENUMERATION NO_DISJUNCTIVE_HASH_JOIN Avoids disjunctive hash join implementation. SAP HANA 2.0 SP00
OPTIMIZER_PHYSICAL_ENUMERATION OLAP_FACT_TABLE Prefers choosing a fact table whose name is the same as is given. SP12