Difference between revisions of "EclipseLink/Examples/JPA/Indexes"

From Eclipsepedia

Jump to: navigation, search
 
(6 intermediate revisions by 2 users not shown)
Line 4: Line 4:
 
An [http://en.wikipedia.org/wiki/Index_%28database%29 index] is used in a database to optimize queries that use that column to provide optimal look-up and avoid table scans.
 
An [http://en.wikipedia.org/wiki/Index_%28database%29 index] is used in a database to optimize queries that use that column to provide optimal look-up and avoid table scans.
 
Different databases have different levels of index support, EclipseLink supports the creation of basic indexes on databases that support them.
 
Different databases have different levels of index support, EclipseLink supports the creation of basic indexes on databases that support them.
Normally database auto-index primary key and foreign key fields, so normally these do not require indexes.
+
Normally databases auto-index primary key and foreign key fields, so normally these do not require indexes.
 +
Previous to EclipseLink 2.2, DDL scripts or native SQL queries could be used to defined indexes.  These can also be used to define more complex indexes.
  
 
EclipseLink supports index creation using its [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/annotations/Index.html <code>@Index</code>] annotation or <code><index></code> xml element.
 
EclipseLink supports index creation using its [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/annotations/Index.html <code>@Index</code>] annotation or <code><index></code> xml element.
 
The <code>@Index</code> has a name, schema, catalog, table, unique and a set of column names.  An <code>@Index</code> can be put on any <code>@Basic</code> attribute and all of its properties will be defaulted to index the attribute's column.  <code>@Index</code> can be set on a class to define a multi-column index.  The <code>@Indexes</code> annotation can be used to define multiple indexes in one class.
 
The <code>@Index</code> has a name, schema, catalog, table, unique and a set of column names.  An <code>@Index</code> can be put on any <code>@Basic</code> attribute and all of its properties will be defaulted to index the attribute's column.  <code>@Index</code> can be set on a class to define a multi-column index.  The <code>@Indexes</code> annotation can be used to define multiple indexes in one class.
  
== Example Index ==
+
To allow indexes to be generated DDL generation must be enabled.  This is done through the <code>"eclipselink.ddl-generation"</code> persistence unit property, set to either <code>"create-tables"</code> or <code>"drop-and-create-tables"</code>.
 +
 
 +
See also:
 +
* [http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_index.htm#BABGIFEC @Index] (EclipseLink JPA Extensions Reference)
 +
* [http://java-persistence-performance.blogspot.com/2013/03/but-what-if-im-not-querying-by-id.html But what if I'm not querying by id? (database and cache indexes) ] (blog)
 +
 
 +
=== Example Index===
 
The firstName and lastName are indexed, together and individually.
 
The firstName and lastName are indexed, together and individually.
  
 
<source lang="java">
 
<source lang="java">
 
@Entity
 
@Entity
@Index(name="EMP_NAME_INDEX", columns={"F_NAME","L_NAME"})
+
@Index(name="EMP_NAME_INDEX", columnNames={"F_NAME","L_NAME"})
 
public class Employee{
 
public class Employee{
 
     @Id
 
     @Id
Line 34: Line 41:
 
CREATE INDEX INDEX_EMPLOYEE_L_NAME ON EMPLOYEE (L_NAME)
 
CREATE INDEX INDEX_EMPLOYEE_L_NAME ON EMPLOYEE (L_NAME)
 
CREATE INDEX EMP_NAME_INDEX ON EMPLOYEE (F_NAME, L_NAME)
 
CREATE INDEX EMP_NAME_INDEX ON EMPLOYEE (F_NAME, L_NAME)
</sql>
+
</source>

Latest revision as of 08:38, 4 April 2013


EclipseLink (as of 2.2) provides a way to have a database INDEX generated when EclipseLink is used to generate the database. An index is used in a database to optimize queries that use that column to provide optimal look-up and avoid table scans. Different databases have different levels of index support, EclipseLink supports the creation of basic indexes on databases that support them. Normally databases auto-index primary key and foreign key fields, so normally these do not require indexes. Previous to EclipseLink 2.2, DDL scripts or native SQL queries could be used to defined indexes. These can also be used to define more complex indexes.

EclipseLink supports index creation using its @Index annotation or <index> xml element. The @Index has a name, schema, catalog, table, unique and a set of column names. An @Index can be put on any @Basic attribute and all of its properties will be defaulted to index the attribute's column. @Index can be set on a class to define a multi-column index. The @Indexes annotation can be used to define multiple indexes in one class.

To allow indexes to be generated DDL generation must be enabled. This is done through the "eclipselink.ddl-generation" persistence unit property, set to either "create-tables" or "drop-and-create-tables".

See also:

[edit] Example Index

The firstName and lastName are indexed, together and individually.

@Entity
@Index(name="EMP_NAME_INDEX", columnNames={"F_NAME","L_NAME"})
public class Employee{
    @Id
    private long id;
    @Index
    @Column(name="F_NAME")
    private String firstName;
    @Index
    @Column(name="L_NAME")
    private String lastName;
    ...
}

This produces the SQL,

CREATE INDEX INDEX_EMPLOYEE_F_NAME ON EMPLOYEE (F_NAME)
CREATE INDEX INDEX_EMPLOYEE_L_NAME ON EMPLOYEE (L_NAME)
CREATE INDEX EMP_NAME_INDEX ON EMPLOYEE (F_NAME, L_NAME)