Hibernate: When to use @Index annotation

Question

In my scenario, I have a schema generation script to create tables and required indexes. I am wondering is there any need to define @Index annotation in hibernate entities as well, if so why?

Script:

create table issues (id, project_id, .., status_id)

create index idx_issues_projid on issues (project_id)

Entity:

@Table(name="issues")
public class DBIssue {

..
  @ManyToOne(fetch = FetchType.EAGER)
  @JoinColumn(name = "PROJECT_ID")
  @Index(name="INDEX_TFW_ISSUE_PROJECT_ID")
  private DBProject project;
}

Hibernate configuration:

<property name="hibernate.hbm2ddl.auto">off</property>

Solution

I presume you're asking about the Hibernate @Index annotation, which has essentially been imported into JPA 2.1. You would use @Index anywhere you would otherwise proactively tell a relational database to index a column, primarily on a field where you know you'll be doing lots of lookups. In this case, for example, you are probably going to want to "select the DBIssues belonging to a particular DBProject frequently, so it would make sense to index that column in the table holding DBIssue.

Comments

[harsh]

I understand this, but if I already have index created in database on required columns. Is there any benefit in configuring same in hibernate entity? If I omit this annotation is there any downside?

[chrylis -cautiouslyoptimistic-]

This annotation tells Hibernate (or a JPA 2.1 provider) to create the index when it's generating a schema. There's no explicit need to include it in your code, but it's good documentation and will ensure that if you ever need to regenerate your DDL the index will be attached automatically.