EclipseLink/DesignDocs/283430

Design Specification: Indexes

ER 283430

Feedback

Document History

Date	Author	Version Description & Notes
2010-08-25	James	0.1 Draft

Project overview

The ability to define database indexes and have EclipseLink's DDL generation generate them.

Concepts

An index is a database structure define for a table to improve query and lookup performance for a set of columns.

Requirements

Support defining indexes in JPA through annotations. Support defining indexes in JPA through XML.

Design Constraints

Must be database independent.

Functionality

An @Index annotation and XML will be added. An index can be defined on an Entity, or attribute. For the Entity it must define a set of columns to index, the table will be defaulted. For an attribute the table and column will be defaulted.

The name of the index will be default to _<column>_INDEX, but the name should normally be provided. An IndexDefinition will also be added to the schema framework to allow defining, creating/dropping indexes through code. Annotation process will create an IndexDefinition from the @Index and store in on the descriptor's DatabaseTable. During default schema generation the DefaultSchemaGenerator will add the IndexDefinition to the TableDefition to be created. The existing support to create indexes for primary key and unique constraints will be refactored to use the IndexDefinition. Support for indexes will be defined in the platform, by default a platform will be assumed to support indexes. MySQL will require special drop syntax, to include the table name in the drop statement.

Testing

Define indexes for some test schemas. Need to test on all databases.

API

• Index
• @Index(name<String>, schema<String>, catalog<String> table<String>, unique<boolean>, columns<String>[])
• @Indexes

Config files

• orm.xml

<index name="EMP_NAME_INDEX" table="EMPLOYEE" unique="true">
    <column>F_NAME</column>
    <column>L_NAME</column>
</index>

Documentation

Should be documented under extended annotations, and schema generation.

Open Issues

Issue #	Owner	Description / Notes
1		What databases support indexes, do any use a different syntax?
2		Shouldn't the @Index annotation support choosing the various types of index algorithms supported by the database being used? We may need to add another attribute to @Index, named 'type' that can take a value from predefined list of values like BTree, RTree, Hash, BitMap, etc. Further, there should be a way to define clustered indexes too, may be an attribute clustered=true/false.

Decisions

Issue	Description / Notes	Decision

Future Considerations

• Other DDL options.