Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-48545

Support specifying optimizer hints for a query block

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: Neo
    • Fix Version/s: Neo
    • Component/s: query
    • Labels:
    • Story Points:
      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

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

          Hide
          build-team Couchbase Build Team added a comment -

          Build couchbase-server-7.1.0-1323 contains query commit 03b9fde with commit message:
          MB-48545 Support specifying optimizer hints as comment in a query block

          Show
          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1323 contains query commit 03b9fde with commit message: MB-48545 Support specifying optimizer hints as comment in a query block
          Hide
          build-team Couchbase Build Team added a comment -

          Build couchbase-server-7.1.0-1324 contains query commit 81beb31 with commit message:
          MB-48545 Add simple test case

          Show
          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1324 contains query commit 81beb31 with commit message: MB-48545 Add simple test case
          Hide
          build-team Couchbase Build Team added a comment -

          Build couchbase-server-7.1.0-1332 contains query commit 92b9f94 with commit message:
          MB-48545 Remove debug logging

          Show
          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1332 contains query commit 92b9f94 with commit message: MB-48545 Remove debug logging
          Hide
          build-team Couchbase Build Team added a comment -

          Build couchbase-server-7.1.0-1367 contains query commit 07862a0 with commit message:
          MB-48545 Allow NULL to be specified as 'indexes' for INDEX hint and 'options' for USE_HASH hint

          Show
          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1367 contains query commit 07862a0 with commit message: MB-48545 Allow NULL to be specified as 'indexes' for INDEX hint and 'options' for USE_HASH hint

            People

            Assignee:
            bingjie.miao Bingjie Miao
            Reporter:
            bingjie.miao Bingjie Miao
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Gerrit Reviews

                There are no open Gerrit changes

                  PagerDuty