Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Database Hints

Oracle Usage

Oracle provides users with the ability to influence how the query optimizer behaves and the decisions made to generate query run plans. Controlling the behavior of the database optimizer is performed using database hints. They can be defined as a directive operation to the optimizer and alter the decisions of how run plans are generated.

Oracle supports over 60 different database hints, and each database hint can have 0 or more arguments. Database hints are divided into different categories such as optimizer hints, join order hints, and parallel run hints.

Database hints are embedded directly into the SQL queries immediately following the SELECT keyword using the following format: /* <DB_HINT> */.

Examples

Force the query optimizer to use a specific index for data access.

SELECT /* INDEX(EMP, IDX_EMP_HIRE_DATE)*/ * FROM EMPLOYEES EMP
WHERE HIRE_DATE >= '01-JAN-2010';

Run Plan
Plan hash value: 3035503638
| Id | Operation                   | Name          | Rows | Bytes | Cost (%CPU) | Time
| 0  | SELECT STATEMENT            |               | 1    | 62    | 2 (0)       | 00:00:01
| 1  | TABLE ACCESS BY INDEX ROWID | EMPLOYEES     | 1    | 62    | 2 (0)       | 00:00:01
|* 2 | INDEX RANGE SCAN            | IDX_HIRE_DATE | 1    |       | 1 (0)       | 00:00:01

Predicate Information (identified by operation id):
2 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

For more information, see Additional Hints and Influencing the Optimizer in the Oracle documentation.

MySQL Usage

Aurora MySQL supports two types of hints: optimizer hints and index hints.

Index Hints

The USE INDEX hint limits the optimizer’s choice to one of the indexes listed in the <Index List> white list. Alternatively, indexes can be black listed using the IGNORE keyword.

The FORCE INDEX hint is similar to USE INDEX (index_list), but with strong favor towards seek against scan. The hints use the actual index names, not column names. You can refer to primary keys using the keyword PRIMARY.

Syntax

SELECT ...
FROM <Table Name>
    USE {INDEX|KEY}
        [FOR {JOIN|ORDER BY|GROUP BY}] (<Index List>)
    | IGNORE {INDEX|KEY}
        [FOR {JOIN|ORDER BY|GROUP BY}] (<Index List>)
    | FORCE {INDEX|KEY}
        [FOR {JOIN|ORDER BY|GROUP BY}] (<Index List>)
...n

The syntax for index hints has the following characteristics: * You can omit <Index List> for USE INDEX only. It translates to don’t use any indexes, which is equivalent to a clustered index scan. * Index hints can be further scoped down using the FOR clause. Use FOR JOIN, FOR ORDER BY, or FOR GROUP BY to limit the hint applicability to that specific query processing phase. * Multiple index hints can be specified for the same or different scope.

Optimizer Hints

Optimizer hints give developers or administrators control over some of the optimizer decision tree. They are specified within the statement text as a comment with the prefix +.

Optimizer hints may pertain to different scopes and are valid in only one or two scopes. The available scopes for optimizer hints in descending scope width order are:

Syntax

SELECT /*+ <Optimizer Hints> */ <Select List>...

INSERT /*+ <Optimizer Hints> */ INTO <Table>...

REPLACE /*+ <Optimizer Hints> */ INTO <Table>...

UPDATE /*+ <Optimizer Hints> */ <Table> SET...

DELETE /*+ <Optimizer Hints> */ FROM <Table>...

The following optimizer hints are available in Aurora MySQL.

You can use query block names with QB_NAME to distinguish a block for limiting the scope of the table hint. Add @ to indicate a hint scope for one or more named subqueries. Consider the following example:

SELECT /*+ SEMIJOIN(@SubQuery1 FIRSTMATCH, LOOSESCAN) */ *
FROM Table1
WHERE Col1 IN (SELECT /*+ QB_NAME(SubQuery1) */ Col1
    FROM t3);

Values for MAX_EXECUTION_TIME are measured in seconds and are always global for the entire query.

Migration Considerations

In general, the Aurora MySQL hint framework is relatively limited compared to the granular control provided by Oracle. It is recommended to start migration testing with all hints removed. Then, selectively apply hints as a last resort if other means such as schema, index, and query optimizations have failed.

Aurora MySQL uses a list of indexes and hints, both white list (USE) and black list (IGNORE), as opposed to Oracle’s explicit index approach.

Index hints are not mandatory instructions. Aurora MySQL may choose alternatives if it cannot use the hinted index.

Examples

Force an index access.

SELECT * FROM Table1 USE INDEX (Index1) ORDER BY Col1;

Specify multiple index hints.

SELECT * FROM Table1
    USE INDEX (Index1)
    INNER JOIN Table2
        IGNORE INDEX(Index2)
        ON Table1.Col1 = Table2.Col1
    ORDER BY Col1;

Specify optimizer hints.

SELECT /*+ NO_RANGE_OPTIMIZATION(Table1 PRIMARY, Index2) */
Col1 FROM Table1 WHERE Col2 = 300;

SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM Table1 INNER JOIN Table2 ON ...;

SELECT /*+ NO_ICP(t1, t2) */ * FROM Table1 INNER JOIN Table2 ON ...;

Summary

For more information, see Controlling the Query Optimizer, Optimizer Hints, Index Hints, and Optimizing Subqueries, Derived Tables, and View References in the MySQL documentation.