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

Support specifying optimizer hints for a query block

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Fixed
    • Major
    • 7.1.0
    • 7.1.0
    • query
    • 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

        Activity

          People

            bingjie.miao Bingjie Miao
            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