Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/JPA/Indexes"
(New page: Indexes EclipseLink (as of 2.2) provides a way to have a database INDEX generated when EclipseLink is used to generate the database. An [http://en.wik...) |
|||
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. | ||
+ | |||
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 and a set of | + | 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 == | == Example Index == | ||
− | The firstName and lastName are indexed. | + | The firstName and lastName are indexed, together and individually. |
<source lang="java"> | <source lang="java"> | ||
@Entity | @Entity | ||
− | @Index(name="EMP_NAME_INDEX", columns={ | + | @Index(name="EMP_NAME_INDEX", columns={"F_NAME","L_NAME"}) |
public class Employee{ | public class Employee{ | ||
@Id | @Id | ||
Line 29: | Line 31: | ||
<source lang="sql"> | <source lang="sql"> | ||
− | CREATE INDEX | + | 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) | ||
</sql> | </sql> |
Revision as of 12:34, 24 January 2011
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 database auto-index primary key and foreign key fields, so normally these do not require 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.
Example Index
The firstName and lastName are indexed, together and individually.
@Entity @Index(name="EMP_NAME_INDEX", columns={"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) </sql>