Details
-
Improvement
-
Resolution: Fixed
-
Major
-
7.1.0
-
1
Description
Currently user can specify a hint in the form of USE KEYS, USE INDEX, USE NL, USE HASH in the query text itself, right after a keyspace specification. This is fine for the type of hints that's specific to a keyspace (all the hints we have so far), but awkward for any type of hints that applies to a query block or a statement itself. One such example is ORDERED, which directs the optimizer to use the order of the keyspaces specified in the query (i.e. do not attempt to reorder joins).
Add an infrastructure to add optimizer hints as a comment, which is common in relational databases. A plus sign is needed immediately after the opening of the comment for it to be recognized as optimizer hint:
/*+ INDEX(keyspace1 ix1 ix2) USE_HASH(keyspace2 keyspace3/BUILD) ORDERED */ |
or
--+ INDEX(keyspace1 ix1 ix2) USE_NL(keyspace2 keyspace3)
|
You can also specify the hints as a JSON object, like:
/*+ {"index": {"keyspace": "keyspace1", "indexes": ["ix1", "ix2"]}, "use_hash": {"keyspace": "keyspace2", "option": "BUILD"}} */ |
or
--+ {"index": {"keyspace": "keyspace1", "indexes": "ix1"}, "use_nl": [ {"keyspace": "keyspace2"}, {"keyspace": "keyspace3} ] } |
We plan to support this for SELECT statement for now. Also you cannot use both the legacy style USE INDEX/USE HASH/USE NL hint with the new style INDEX/USE_NL/USE_HASH hint for the same keyspace.
Attachments
For Gerrit Dashboard: MB-48545 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
161989,4 | MB-48545 Support specifying optimizer hints as comment in a query block | master | query | Status: MERGED | +2 | +1 |
161999,2 | MB-48545 Add simple test case | master | query | Status: MERGED | +2 | +1 |
162103,2 | MB-48545 Remove debug logging | master | query | Status: MERGED | +2 | +1 |
162430,2 | MB-48545 Allow NULL to be specified as 'indexes' for INDEX hint and 'options' for USE_HASH hint | master | query | Status: MERGED | +2 | +1 |