github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/docs/RFCS/20150810_select_from_index.md (about)

     1  - Feature Name: select_from_index
     2  - Status: completed
     3  - Start Date: 2015-08-10
     4  - RFC PR: [#2046](https://github.com/cockroachdb/cockroach/pull/2046)
     5  - Cockroach Issue:
     6  
     7  # Summary
     8  
     9  Extend the SELECT syntax to allow an optional index to be specified in table
    10  references. The current table reference syntax is `table` or
    11  `database.table`. This syntax would be extended to allow `table@index` and
    12  `database.table@index`.
    13  
    14  Only the column names that are part of the index will be made available to the
    15  rest of the query. For example, consider the following table:
    16  
    17  ```sql
    18  CREATE TABLE test (
    19    a INT PRIMARY KEY,
    20    b TEXT,
    21    c BOOLEAN,
    22    CONSTRAINT foo INDEX (b, c)
    23  )
    24  ```
    25  
    26  The new syntax can be used to restrict the query to only using the index `foo`:
    27  
    28  ```sql
    29  SELECT * FROM test@foo
    30  ```
    31  
    32  It will be an error to access columns that are not part of the index:
    33  
    34  ```sql
    35  SELECT a FROM test@foo WHERE a > 2
    36  ```
    37  
    38  # Motivation
    39  
    40  As indexes are optional in SQL, it is difficult to know when an index is used
    41  to satisfy a query which in turn makes it difficult to use the existing logic
    42  test infrastructure to test correctness of index scans. Providing for
    43  specification of an index to read from will make testing of index scans easier
    44  and meshes with our desire to allow explicitness in queries.
    45  
    46  Note that this proposal is essentially a very strict form of an index hint or
    47  forced index hint functionality. It is possible we'll adjust the semantics in
    48  the future.
    49  
    50  # Detailed design
    51  
    52  The grammar will be extended to allow an `@index` suffix to
    53  table names. `planner.Select` will be enhanced to select the index to use
    54  (if specified). `scanNode` will be enhanced to understand scanning from
    55  secondary indexes. This latter work is required to support secondary index
    56  scans.
    57  
    58  # Drawbacks
    59  
    60  * Providing an SQL extension inhibits portability. It is expected this
    61    functionality will primarily be used for testing. If necessary, we can
    62    restrict this functionality to only be available under a flag.
    63  
    64  * There are likely unforeseen interactions with joins.
    65  
    66  # Alternatives
    67  
    68  * Testing of index scans can use a different test harness than the existing SQL
    69    logic test infrastructure.
    70  
    71  * Instead of adding a new delimiter for index specification, we could overload
    72    the dot-separated notation for qualified names. For example, `table.index` or
    73    `database.table.index`. This would remove the need to adjust the grammar but
    74    would add ambiguity as to whether the first element in a qualified name is a
    75    table or a database.
    76  
    77  # Unresolved questions