Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <516564271.1214935245020.JavaMail.jira@brutus>
Date: Tue, 1 Jul 2008 11:00:45 -0700 (PDT)
From: "Mike Matrigali (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-3748) add support to create indexes on non
 key fields
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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


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.
-
You can reply to this email to add a comment to the issue online.