Keyspace Hints

Capella Operational

reference

Keyspace hints apply to a specific keyspace.

A keyspace hint is a type of optimizer hint. Keyspace hints include index hints, which enable you to specify indexes, and join hints, which enable you to specify join methods.

For each keyspace hint, you must specify the keyspace or keyspaces that the hint applies to. If a keyspace is given an explicit alias in the query, then the hint must refer to the explicit alias, not the keyspace name. This is to avoid confusion in situations where the same keyspace can be used multiple times (with different aliases) in the same query.

If the keyspace is not given an explicit alias in the query, the hint must refer to the keyspace using the keyspace name. (If the keyspace name is a dotted path, the hint must refer to the keyspace using its implicit alias, which is the last component in the keyspace path.)

There are two possible formats for each optimizer hint: simple syntax and JSON syntax. Note that you cannot mix simple syntax and JSON syntax in the same hint comment.

INDEX

This hint directs the optimizer to consider one or more specified secondary indexes. If not specified, the optimizer selects the optimal available index.

Simple Syntax

gsi-hint-simple ::= 'INDEX' '(' keyspace index* ')'

Syntax diagram: refer to source code listing

With the simple syntax, this hint specifies a single keyspace expression, and zero, one, or more indexes. You can use this hint multiple times within the hint comment to specify hints for more than one keyspace.

Arguments

keyspace: The keyspace or alias to which this hint applies.
index: A secondary index that the optimizer should consider for the given keyspace. This argument is optional; if omitted, the optimizer considers all secondary indexes available in the given keyspace.

JSON Syntax

gsi-hint-json ::= '"index"' ':' ( index-array | index-object )

With the JSON syntax, this hint takes the form of an index property. You may only use this property once within the hint comment. The value of this property may be an Index Array or an Index Object.

Index Array

index-array ::= '[' index-object ( ',' index-object )* ']'

Use this array to specify indexes for multiple keyspaces. Each element must be an Index Object.

Index Object

index-object ::= '{' keyspace-property ',' indexes-property '}'

Use this object to specify indexes for a single keyspace. It must contain a Keyspace Property and an Indexes Property. The order of the properties within the object is not significant.

Keyspace Property

keyspace-property ::= ( '"keyspace"' | '"alias"' ) ':' '"' keyspace '"'

Synonym for "keyspace": "alias"

The value of this property is the keyspace or alias to which this hint applies.

Indexes Property

indexes-property ::= '"indexes"' ':' ( 'null'
                                     | '"' index '"'
                                     | '[' '"' index '"' ( ',' '"' index '"' )* ']' )

The value of this property may be:

`null`	The optimizer considers all secondary indexes available in the given keyspace.
An index string	A secondary index that the optimizer should consider for the given keyspace.
An array of index strings	An array of secondary indexes that the optimizer should consider for the given keyspace.

Examples

For the examples in this section, it is assumed that the cost-based optimizer is active, and all optimizer statistics are up-to-date.

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Optimized index selection

The following query does not include an index hint.

Query

SELECT id
FROM route
WHERE sourceairport = "SFO"
LIMIT 1;

If you examine the plan for this query, you can see that the optimizer has selected the index def_inventory_route_sourceairport, which is installed with the travel sample dataset.

Explain plan

{
  "#operator": "IndexScan3",
  "bucket": "travel-sample",
  "index": "def_inventory_route_sourceairport",

INDEX hint

The following query hints that the optimizer should select the index def_inventory_route_route_src_dst_day for the keyspace route.

Query

SELECT /*+ INDEX (route def_inventory_route_route_src_dst_day) */ id (1)
FROM route (2)
WHERE sourceairport = "SFO"
LIMIT 1;

1	The keyspace is not given an explicit alias in the query. You must therefore refer to the keyspace using the keyspace name or implicit alias — in this case, `route`.
2	The implicit alias is the last element in the keyspace path.

If you examine the plan for this query, you can see that the query uses the suggested index.

Explain plan

"scan": {
  "#operator": "IndexScan3",
  "bucket": "travel-sample",
  "index": "def_inventory_route_route_src_dst_day",

Legacy Equivalent

This hint is equivalent to the legacy USE INDEX (USING GSI) clause. For more details, refer to USE INDEX Clause.

Note that you cannot use a hint comment and the USE clause to specify optimizer hints on the same keyspace. If you do this, the hint comment and the USE clause are marked as erroneous and ignored by the optimizer.

INDEX_FTS

This hint directs the optimizer to consider one or more specified full-text indexes. If not specified, the optimizer selects the optimal available index.

Simple Syntax

fts-hint-simple ::= 'INDEX_FTS' '(' keyspace index* ')'

With the simple syntax, this hint specifies a single keyspace expression; and zero, one, or more indexes. You can use this hint multiple times within the hint comment to specify hints for more than one keyspace.

Arguments

keyspace: The keyspace or alias to which this hint applies.
index: A full-text index that the optimizer should consider for the given keyspace. This argument is optional; if omitted, the optimizer considers all full-text indexes available in the given keyspace.

JSON Syntax

fts-hint-json ::= '"index_fts"' ':' ( index-array | index-object )

With the JSON syntax, this hint takes the form of an index_fts property. You may only use this property once within the hint comment. The value of this property may be an Index Array or an Index Object.

Index Array

index-array ::= '[' index-object ( ',' index-object )* ']'

Use this array to specify indexes for multiple keyspaces. Each element must be an Index Object.

Index Object

index-object ::= '{' keyspace-property ',' indexes-property '}'

Use this object to specify indexes for a single keyspace. It must contain a Keyspace Property and an Indexes Property. The order of the properties within the object is not significant.

Keyspace Property

keyspace-property ::= ( '"keyspace"' | '"alias"' ) ':' '"' keyspace '"'

Synonym for "keyspace": "alias"

The value of this property is the keyspace or alias to which this hint applies.

Indexes Property

indexes-property ::= '"indexes"' ':' ( 'null'
                                     | '"' index '"'
                                     | '[' '"' index '"' ( ',' '"' index '"' )* ']' )

The value of this property may be:

`null`	The optimizer considers all full-text indexes available in the given keyspace.
index string	A full-text index that the optimizer should consider for the given keyspace.
index array	An array of full-text indexes that the optimizer should consider for the given keyspace.

Examples

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

INDEX_FTS hint

This example specifies that the optimizer should prefer any suitable FTS index, without specifying an index by name. To qualify for this query, there must be an FTS index on state and type, using the keyword analyzer. (Or alternatively, an FTS index on state, with a custom type mapping on "hotel".)

Query

SELECT /*+ INDEX_FTS (hotel) */
       META().id
FROM hotel
WHERE state = "Corse" OR state = "California";

All FTS indexes are considered. If a qualified FTS index is available, it is selected for the query. If none of the available FTS indexes are qualified, the available GSI indexes are considered instead.

Legacy Equivalent

This hint is equivalent to the legacy USE INDEX (USING FTS) clause. For more details, refer to USE INDEX Clause.

USE_NL

This hint directs the optimizer to consider a nested-loop join for the specified keyspace. This hint must be specified on the keyspace on the right-hand side of the join. If not specified, the optimizer selects the optimal join method.

Simple Syntax

nl-hint-simple ::= 'USE_NL' '(' ( keyspace )+ ')'

With the simple syntax, this hint specifies one or more keyspaces. You may also use this hint multiple times within the hint comment.

Arguments

keyspace: The keyspace or alias to which this hint applies.

JSON Syntax

nl-hint-json ::= '"use_nl"' ':' ( keyspace-array | keyspace-object )

With the JSON syntax, this hint takes the form of a use_nl property. You may only use this property once within the hint comment. The value of this property may be a Keyspace Array or a Keyspace Object.

Keyspace Array

keyspace-array ::= '[' keyspace-object ( ',' keyspace-object )* ']'

Use this array to apply the hint to multiple keyspaces. Each element must be a Keyspace Object.

Keyspace Object

keyspace-object ::= '{' keyspace-property '}'

Use this object to apply the hint to a single keyspace. It must contain a Keyspace Property.

Keyspace Property

keyspace-property ::= ( '"keyspace"' | '"alias"' ) ':' '"' keyspace '"'

Synonym for "keyspace": "alias"

The value of this property is the keyspace or alias to which this hint applies.

Examples

For the examples in this section, it is assumed that the cost-based optimizer is active, and all optimizer statistics are up-to-date.

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Optimized join method selection

The following query does not include a join hint.

Query

SELECT a.airportname AS airport, r.id AS route
FROM route AS r,
     airport AS a
WHERE a.faa = r.sourceairport
  AND r.sourceairport = "SFO"
LIMIT 4;

If you examine the plan for this query, you can see that the optimizer has selected the hash join method.

Explain plan

{
  "#operator": "HashJoin",

USE_NL hint

The following query is equivalent to the one in the Optimized join method selection example, but includes a nested-loop join hint.

Query

SELECT /*+ USE_NL (a) */ (1)
       a.airportname AS airport, r.id AS route
FROM route AS r,
     airport AS a (2)
WHERE a.faa = r.sourceairport
  AND r.sourceairport = "SFO"
LIMIT 4;

1	The keyspace is given an explicit alias in the query. You must therefore refer to the keyspace using the explicit alias.
2	In this case, the explicit alias is `a`.

If you examine the plan text for this query, you can see that the query uses the suggested join method.

Explain plan

{
  "#operator": "NestedLoopJoin",

Legacy Equivalent

This hint is equivalent to the legacy USE NL clause. For more details, refer to USE NL Clause.

Note that you cannot specify optimizer hints and the USE clause on the same keyspace in the same query. If you do this, the optimizer hints and USE clause are both marked as erroneous and ignored by the optimizer.

USE_HASH

This hint directs the optimizer to consider a hash join for the specified keyspace. This hint must be specified on the keyspace on the right-hand side of the join. If not specified, the optimizer selects the optimal join method.

A hash join has two sides: a build side and a probe side. The build side of the join is used to create an in-memory hash table. The probe side uses that table to find matches and perform the join. Typically, this means you want the build side to be used on the smaller of the two sets.

This hint enables you specify whether the right side of the join should be the build side or the probe side. If you specify that the right side of the join is the build side, then the left side will be the probe side, and vice versa.

Simple Syntax

hash-hint-simple ::= 'USE_HASH' '(' ( keyspace ( '/' ( 'BUILD' | 'PROBE' ) )? )+ ')'

With the simple syntax, this hint specifies one or more keyspaces. For each keyspace, you may also add a slash, followed by an option. You may also use this hint multiple times within the hint comment.

Arguments

keyspace: The keyspace or alias to which this hint applies.

Options

/BUILD: The specified keyspace is to be used as the build side of the join.
/PROBE: The specified keyspace is to be used as the probe side of the join.

If you omit the option (including the slash), the optimizer determines whether the specified keyspace is to be used as the build side or the probe side of the join, based on the estimated cardinality of both sides.

JSON Syntax

hash-hint-json ::= '"use_hash"' ':' ( hash-array | hash-object )

With the JSON syntax, this hint takes the form of a use_hash property. You may only use this property once within the hint comment. The value of this property may be a Hash Array or a Hash Object.

Hash Array

hash-array ::= '[' hash-object ( ',' hash-object )* ']'

Use this array to apply the hint to multiple keyspaces. Each element must be a Hash Object.

Hash Object

hash-object ::= '{' keyspace-property ( "," option-property )? '}'

Use this object to apply the hint to a single keyspace. It must contain a Keyspace Property and an optional Option Property. The order of the properties within the object is not significant.

Keyspace Property

keyspace-property ::= ( '"keyspace"' | '"alias"' ) ':' '"' keyspace '"'

Synonym for "keyspace": "alias"

The value of this property is the keyspace or alias to which this hint applies.

Option Property

option-property ::= '"option"' ':' ( '"build"' | '"probe"' | 'null' )

The value of this property may be:

`"build"`	The specified keyspace is to be used as the build side of the join.
`"probe"`	The specified keyspace is to be used as the probe side of the join.
`null`	The optimizer determines whether the specified keyspace is to be used as the build side or the probe side of the join, based on the estimated cardinality of both sides.

Similarly, if you omit this property entirely, the optimizer determines whether the specified keyspace is to be used as the build side or the probe side of the join.

Examples

For the examples in this section, it is assumed that the cost-based optimizer is active, and all optimizer statistics are up-to-date.

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Optimized join method selection

The following query does not include a join hint.

Query

SELECT COUNT(1) AS Total_Count
FROM route rte
INNER JOIN airline aline
ON rte.airlineid = META(aline).id;

If you examine the plan for this query, you can see that the optimizer has selected to use the hash join method, and to put the aline keyspace on the build side of the join.

Explain plan

{
  "#operator": "HashJoin",
  "build_aliases": [
    "aline"
  ],
  "build_exprs": [
    "cover ((meta(`aline`).`id`))"
  ],
  "on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
  "probe_exprs": [
    "(`rte`.`airlineid`)"
  ],

USE_HASH with PROBE

The following query is equivalent to the one in the Optimized join method selection example, but specifies that the keyspace aline is to be joined (with rte) using a hash join, and aline is used as the probe side of the hash join.

Query — simple syntax

SELECT /*+ USE_HASH (aline/PROBE) */
       COUNT(1) AS Total_Count
FROM route rte
INNER JOIN airline aline
ON rte.airlineid = META(aline).id;

If you examine the explain plan for this query, you can see that the query uses the hash join method as suggested, with the aline keyspace on the probe side of the join.

Explain plan

{
  "#operator": "HashJoin",
  "build_aliases": [
    "rte"
  ],
  "build_exprs": [
    "(`rte`.`airlineid`)"
  ],
  "on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
  "probe_exprs": [
    "cover ((meta(`aline`).`id`))"
  ],

USE_HASH with BUILD

This is effectively the same query as the USE_HASH with PROBE example, except the two keyspaces are switched, and here the BUILD option is used, indicating the hash join should use rte as the build side.

Query

SELECT /*+ { "use_hash": { "keyspace": "rte", "option": "build" } } */
       COUNT(1) AS Total_Count
FROM airline aline
INNER JOIN route rte
ON (rte.airlineid = META(aline).id);

If you examine the explain plan for this query, you can see that the query uses the hash join method as suggested, with the rte keyspace on the build side.

Explain plan

{
  "#operator": "HashJoin",
  "build_aliases": [
    "rte"
  ],
  "build_exprs": [
    "(`rte`.`airlineid`)"
  ],
  "on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
  "probe_exprs": [
    "cover ((meta(`aline`).`id`))"
  ],

USE_HASH with optimizer

This is the same query as the USE_HASH with PROBE example, but the hint does not specify whether the aline keyspace should be on the probe side or the build side of the join.

Query — JSON syntax

SELECT /*+ USE_HASH (aline) */
       COUNT(1) AS Total_Count
FROM route rte
INNER JOIN airline aline
ON rte.airlineid = META(aline).id;

If you examine the explain plan for this query, you can see that the query uses the hash join method as suggested, but the optimizer has selected to put the aline keyspace on the build side of the join.

Explain plan

{
  "#operator": "HashJoin",
  "build_aliases": [
    "aline"
  ],
  "build_exprs": [
    "cover ((meta(`aline`).`id`))"
  ],
  "on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
  "probe_exprs": [
    "(`rte`.`airlineid`)"
  ],

Legacy Equivalent

This hint is equivalent to the legacy USE HASH clause. For more details, refer to USE HASH Clause.

Note that you cannot specify both optimizer hints and the USE clause on the same keyspace. If you do this, the optimizer hints and the USE clause are both marked as erroneous and ignored by the optimizer.

Keyspace Hints

INDEX

Simple Syntax

Arguments

JSON Syntax

Index Array

Index Object

Keyspace Property

Indexes Property

Examples

Legacy Equivalent

INDEX_FTS

Simple Syntax

Arguments

JSON Syntax

Index Array

Index Object

Keyspace Property

Indexes Property

Examples

Legacy Equivalent

USE_NL

Simple Syntax

Arguments

JSON Syntax

Keyspace Array

Keyspace Object

Keyspace Property

Examples

Legacy Equivalent

USE_HASH

Simple Syntax

Arguments

Options

JSON Syntax

Hash Array

Hash Object

Keyspace Property

Option Property

Examples

Legacy Equivalent

Related Links