[jira] [Updated] (DERBY-3748) add support to create indexes on non key fields

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-3748?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-3748:
-----------------------------------

    Urgency: Normal
     Labels: derby_triage10_10  (was: )
    
> add support to create indexes on non key fields
> -----------------------------------------------
>
>                 Key: DERBY-3748
>                 URL: https://issues.apache.org/jira/browse/DERBY-3748
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL, Store
>    Affects Versions: 10.4.1.3
>            Reporter: Mike Matrigali
>            Priority: Minor
>              Labels: derby_triage10_10
>
> Add support to CREATE INDEX on non key fields.   
> Often query execution can be improved by creating indexes that include all the columns that are returned by the query (referred to as a "covering" index).
> Currently in Derby to do this one must make all the fields part of the key, but often the requirements of the query only need part of these columns to
> be keyed.  By making them part of the key it increases the size of the key that is used in the non-key part of the index structure resulting in more 
> search overhead (both cpu and disk space), than is necessary.  
> The existing btree code has some framework already for non-key fields so a major rework should not be necessary, but would not be surprised if some
> issues still remain.  Things to resolve as part of this work:
> 1) what part of sql to change, maybe just CREATE INDEX.
> 2) are there any optimizer/execution issues with the new type of index?  any metadata changes necessary to track key vs. non-key fields?
> 3) current btree code assumes non-key fields are at end, and use non-key field stuff for unique vs. non-unique indexes.  

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira