6. SQL Hints#

This chapter describes SQL hints that let the user to change the execution plan of an SQL statement.

Overview of Hints#

Hints are used when the user wants to explicitly change the execution plan for a SQL statement.

There is no end to the variety of SQL statements that can be written by users. Furthermore, different execution plans might be created to process the same query depending on how the data is organized. The optimizer creates an efficient execution plan in most cases, but cannot come up with the optimum plan in every case.

To overcome this weakness, users can use hints to explicitly alter execution plans and improve performance. However, it is not appropriate to use hints to alter the execution plans for all queries, because hints need to be rewritten every time an index is created or the data is reorganized, which is burdensome. Therefore, hints should be used only for those queries that critically affect system performance.

Hint Processing Policy#

User-defined hints are processed in accordance with the following policy.

If the syntax of a user-defined hint is error-free and it is possible to obey the hint, the hint will automatically be obeyed. Conversely, hints containing syntax errors or those that cannot be executed will be disregarded.

Using Hints#

Hints can be specified in the following statements

Simple SELECT, UPDATE, DELETE, INSERT statements
The main query or subquery in compound statements
The first query in compound statements (combined with set operators)

A hint can be specified after the SELECT, UPDATE, DELETE, INSERT keywords as below.

/*+ hint */

The plus sign (+) notifies the Altibase server that the footnote is a hint. The plus sign must immediately follow the footnote delimiter without an intervening space.

Types of Hints#

Optimization Strategy#

These hints are used to tell the optimizer whether to perform rule-based optimization or cost-based optimization.

RULE#

Create an execution plan without considering the cost of execution

COST#

Take the cost of execution into consideration when creating an execution plan

FIRST_ROWS(n)#

Create an execution plan that most efficiently returns the first number of n rows.

Use the RULE hint to dictate that the same execution plan is always to be used for a given query. However, use the COST hint if variation in the amount of data greatly influences the estimated cost. If the client wishes to retrieve only the number of n rows in the shortest response time possible, use the FIRST_ROWS hint; the FIRST_ROWS(n) hint operates on a cost basis and affects the joining method. If neither hint is specified, cost-based optimization is performed by default.

Normalization Type#

This hint allows the normalization method to be set for individual SQL statements:

CNF#

Normalization using conjunctive normal form

In conjunctive normal form, the AND operator is the highest-level logical operator and the OR operator is a lower-level operator. When the CNF hint is used, Altibase converts the SELECT statement into conjunctive normal form while creating the execution plan. However, sometimes the use of CNF results in the creation of very complicated conditional clauses, resulting in excessive consumption of system resources. In such cases, Altibase does not use CNF even if this hint is specified.

DNF#

Normalization using disjunctive normal form

In disjunctive normal form, the OR operator is the highest-level logical operator, while the AND operator is a lower-level operator. The use of the DNF hint creates an execution plan that converts the SELECT statement into disjunctive normal form, so that each conditional clause is processed separately using a respective index.

Note that if the SQL statement does not contain any OR clauses, this hint will be ignored. Additionally, depending on the properties of the conditional clauses, conversion into DNF can result in the creation of a large number of conditional clauses, entailing excessive exhaustion of system resources. In such cases, Altibase do not use DNF even if this hint is specified.

NO_EXPAND#

It is identical with CNF.

USE_CONCAT#

It is identical with DNF.

If the hint is not used, a normalization method is selected by comparing the cost of the two normalizations.

Join Order#

This hint is used to set the join order.

LEADING#

This hint joins the tables used in a hint before anything else.

ORDERED#

This join order is determined according to the order that joins appear in the FROM clause of the SQL statement.

If this hint is not used, the join order is determined on the basis of cost estimation.

Joining Method#

This hint is used to set the joining method. For more detailed information about each joining method, please refer to The 3. Query Optimizer; for more detailed information about how to use hints (syntax), please refer to the SQL Reference.

USE_NL
USE_FULL_NL
USE_FULL_STORE_NL
USE_INDEX_NL
USE_SORT
USE_ONE_PASS_SORT
USE_TWO_PASS_SORT
USE_HASH
USE_ONE_PASS_HASH
USE_TWO_PASS_HASH
USE_MERGE
USE_ANTI
NO_USE_HASH
NO_USE_MERGE
NO_USE_NL
NO_USE_SORT

The hints pertaining to joining methods are processed as described below in order to determine the joining method.

The optimizer checks whether it is possible to create a join using each of the specified tables as the inner table in the order in which they are specified.

For example, USE_NL(t1, t2) directs the optimizer to check whether it is possible to create the join using table t1 as the outer table and table t2 as the inner table(t1 => t2).
The optimizer then checks whether it is possible to create a join using table T! as the inner table.

That is, if the join cannot be created in the specified order, the optimizer then checks whether the join can be created in the opposite order. In the above example, it checks whether the join can be created using table t2 as the outer table and table t1 as the inner table(t2 => t1).
If a join cannot be created in either case, another joining method is chosen on the basis of cost estimation.
Conflict with ORDERED hint.

Given the following query:
```
SELECT /*+ ORDERED USE_NL(t2, t1) */
  FROM t1, t2 WHERE t1.i1 = t2.i1;
```
If the table order specified in an ORDERED hint and that specified in an USE_NL hint contradict each other, the ORDERED hint takes priority.
If more than one joining method hint is specified for the same table, one of those hints is chosen on the basis of cost estimation.
```
/*+ USE_NL(t1, t2) USE_HASH(t2, t1) */
```
In case of starting with the NO_USE hints,

a join method is selected out from the rest of the hints except those which have not been used.

Intermediate Result Storage Medium#

These hints are used to specify the storage medium in which to store temporary tables that contain intermediate results.

TEMP_TBS_MEMORY#

Specifies that memory temporary tables are used to store all intermediate results generated while processing the query.

TEMP_TBS_DISK#

Specifies that disk temporary tables are used to store all intermediate results generated while processing the query.

TEMP_TBS_MEMORY is used to maximize performance in the case where the intermediate result set is small, whereas TEMP_TBS_DISK is used when the intermediate result set is large to save resources, despite the decrease in performance.

Hash Bucket Count#

These hints are used to set the number of buckets for execution nodes that use the hashing method.

The optimizer uses hashing to process statements such as GROUP BY, UNION, INTERSECT, MINUS, DISTINCT, HASH JOIN and aggregate functions. If the number of hash buckets that are allocated is suitable for the number of records to be processed, the query processing speed will be improved. In cost-based optimization, a suitable number of hash buckets is determined internally based on the number of records, however, the following hints can be used to set the number of hash buckets as desired.

HASH BUCKET COUNT(n)#

Changes the number of hash buckets for HASH and HSDS nodes.

GROUP BUCKET COUNT(n)#

Changes the number of hash buckets for GRAG and AGGR nodes.

SET BUCKET COUNT(n)#

Changes the number of hash bucket for SITS and SDIF ndoes.

Group Processing Method#

These hints are used to specify the method with which GROUP BY clauses are processed:

GROUP_HASH#

Use hash processing

GROUP_SORT#

Use the sort order for processing

Duplicate Removal Method#

These hints are used to specify the method with which DISTINCT clauses are processed:

DISTINCT_HASH#

Use hash processing

DISTINCT_SORT#

Use the sort order for processing

View Optimization Method#

For a WHERE clause located outside a view, these hints determine whether to process the conditions of the WHERE clause inside the view.

NO_PUSH_SELECT_VIEW(table_name)#

Specifies that the WHERE clause's conditions are not to be moved inside the view for processing.

PUSH_SELECT_VIEW(table_name)#

Specifies that those conditions of a WHERE clause that can be moved inside the view are to be moved into the view for processing.

Access Method#

These hints are used to control how tables are accessed.

FULL SCAN(table_name)#

Specifies that a full table scan is to be performed without using an index, even if an index is available.

INDEX(table_name, index_name1, index_name2, ...)#

Specifies that an index scan is to be performed using one of the listed indexes.

INDEX ASC(table_name, index_name1, index_name2, ...)#

Specifies that an ascending index scan is to be performed using one of the listed indexes.

INDEX_ASC(table_name, index_name1, index_name2, ...)#

It is the same with the INDEX ASC.

INDEX DESC(table_name, index_name1, index_name2, ...)#

Specifies that a descending index scan is to be performed using one of the listed indexes. However, when index created with DESC is used with this hint, it is forward scanned and the result is sorted in descending order.

INDEX_DESC(table_name, index_name1, index_name2, ...)#

It is the same with the INDEX DESC.

NO INDEX(table_name, index_name1, index_name2, ...)#

Specifies that none of the listed indexes are to be used in the optimization process.

NO_INDEX(table_name, index_name1, index_name2, ...)#

It is the same with the NO INDEX.

If this hint is not used, the method is determined on the basis of lowest cost. As for normal tables, index hints can be specified to views using indexes on the base table.

Many hints can be used to control the access method. These hints are processed according to the following policy:

If any hints contradict each other, the hint that appears first will be applied, and subsequent hints will be disregarded.

Example: /*+ INDEX(t1, idx1) NO INDEX(t1, idx1) */
If the hints do not contradict each other, the most efficient of the specified access methods will be chosen on the basis of cost estimation.

Example: /*+ FULL SCAN(t1), INDEX(t1, idx1) */
When an access method hint is used together with a joining method hint, the hints are processed separately.

Example: /*+ USE_HASH(t1, t2), INDEX(t2, idx2) */
The table is accessed using an index and is processed in the hash-based joining method.

Converting Queries#

Push Predicate Method#

For a WHERE clause located outside a view, these hints determine whether to process the join conditions of the WHERE clause inside the view.

PUSH_PRED#

Specifies that the join conditions of a WHERE clause that pertain to the view are to be moved into the view for processing

Whether or not to Unnest Nested Subqueries#

If a nested subquery is included in the SELECT statement, this hint specifies whether or not the query optimizer is to unnest the subquery.

A nested subquery is a subquery contained in the WHERE clause which mainly references columns from a main query in order to limit the result set. Converting a query that contains a nested subquery into an unnested join statement is called "Subquery Unnesting".

UNNEST#

Instructs the performance of subquery unnesting.

NO_UNNEST#

Instructs the nonperformance of subquery unnesting.

Joining Method for Unnesting Nested Subqueries#

This hint determines the joining method of unnesting nested subqueries.

The following hints are valid for use when unnesting subqueries are using semi-join statements:

NL_SJ#

Subquery is unnested using a nested loop join.

HASH_SJ#

Subquery is unnested using a hash join.

SORT_SJ#

Subquery is unnested using a sort join.

MERGE_SJ#

Subquery is unnested using a merge join.

The following hints are valid for use when unnesting subqueries are using anti-join statements:

NL_AJ#

Subquery is unnested using a nested loop join.

HASH_AJ#

Subquery is unnested using a hash join.

SORT_AJ#

Subquery is unnested using a sort join

MERGE_AJ#

Subquery is unnested using a merge join

You must specify a hint when unnesting a nested subquery using a semi-join or an anti-join. You must also differentiate between semi-joins and anti-joins because a hint becomes invalid if a semi-join is used instead of an anti-join or vice versa.

Correct usage example:

SELECT *
  FROM t1
 WHERE EXISTS (SELECT /*+ NO_UNNEST */ *
                 FROM t2
                WHERE t2.a1 = t1.i1 );

SELECT *
  FROM t1
 WHERE EXISTS (SELECT /*+ HASH_SJ */ *
                 FROM t2
                WHERE t2.a1 = t1.i1 );

Incorrect usage example:

The hint is used in the main query instead of the subquery.

SELECT /*+ NO_UNNEST */ *
  FROM t1
 WHERE EXISTS (SELECT *
                 FROM t2
                WHERE t2.a1 = t1.i1 );

EXISTS must be unnested using a inner/semi-join; however, an anti-join hint is used.

SELECT *
  FROM t1
 WHERE EXISTS (SELECT /*+ HASH_AJ */*
                 FROM t2
                WHERE t2.a1 = t1.i1 );

The following are plan cache-related hints:

NO_PLAN_CACHE#

Specifies that newly created plans are not to be stored in the plan cache.

KEEP_PLAN#

Specifies that newly created plans are not to be recreated but used as they are, even if the statistics of a table that is referenced by the plan are modified. This hint is available for use for performing direct/execute and prepare/execute.

PLAN_CACHE_KEEP#

This is a hint to instruct the plan to exclude it from the victim selection process and keep it in the plan cache. This hint is applied during the hardprepare process. So when the user switches the plan to upkeep, the softprepare will not go back to the keep state.

RESULT_CACHE#

Use to cache the intermediate result.

TOP_RESULT_CACHE#

Use to cache the final result.

The following Direct-Path INSERT-related hint can only be specified in INSERT statements.

APPEND#

Specifies that a Direct-Path INSERT operation is to be performed. A direct-Path INSERT operation creates new pages and inputs data, instead of looking for free spaces in existing pages.

Simple Query Execution#

These hints are used to specify whether the simple query of SELECT, INSERT, UPDATE, and DELETE should be executed with SIMPLE QUERY.

EXEC_FAST#

Operating with SIMPLE QUERY

NO_EXEC_FAST#

Non-Operating with SIMPLE QUERY

Parallel Query Execution#

This hint sets whether or not to execute parallel queries when scanning partitioned tables.

NOPARALLEL#

Does not execute in parallel.

PARALLEL integer#

The number of threads, as specified in the integer, executes in parallel.

NO_PARALLEL#

It is the same with the NOPARALLEL.

Thread management costs can increase for the right table of a nested loop join when using a parallel query hint; thus, a parallel query hint cannot be used in this case. A parallel query hint can neither be used within a subquery in a WHERE clause or a SELECT target clause.

This is a hint used for preventing errors occurring in the four fundamental arithmetic operations and mod operation.

HIGH_PRECISION#

The degree of precision is guaranteed for the arithmetic or mod operation up to 38 digits with the float data type.

Execution Plan Delay Hints#

A function of delaying execution is provided. The execution of hierarchy, sorting, windowing, grouping, set, and distinction based upon the execution plan graphs is available regardless of a property of queries. Such a function can be applied only to the top query set.

NO DELAY#

Deactivation in delaying execution of the execution plans.

DELAY#

Activation in delaying execution of the execution plan.