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* ')'
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.
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.
The following query does not include an index hint.
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.
{
"#operator": "IndexScan3",
"bucket": "travel-sample",
"index": "def_inventory_route_sourceairport",
The following query hints that the optimizer should select the index def_inventory_route_route_src_dst_day
for the keyspace route
.
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.
"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.
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.
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".)
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.
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.
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.
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.
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.
The following query does not include a join hint.
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.
{
"#operator": "HashJoin",
The following query is equivalent to the one in the Optimized join method selection example, but includes a nested-loop join hint.
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.
{
"#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.
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.
The following query does not include a join hint.
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.
{
"#operator": "HashJoin",
"build_aliases": [
"aline"
],
"build_exprs": [
"cover ((meta(`aline`).`id`))"
],
"on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
"probe_exprs": [
"(`rte`.`airlineid`)"
],
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.
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.
{
"#operator": "HashJoin",
"build_aliases": [
"rte"
],
"build_exprs": [
"(`rte`.`airlineid`)"
],
"on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
"probe_exprs": [
"cover ((meta(`aline`).`id`))"
],
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.
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.
{
"#operator": "HashJoin",
"build_aliases": [
"rte"
],
"build_exprs": [
"(`rte`.`airlineid`)"
],
"on_clause": "(((`rte`.`airlineid`) = cover ((meta(`aline`).`id`))))",
// ...
"probe_exprs": [
"cover ((meta(`aline`).`id`))"
],
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.
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.
{
"#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.