Javablog » Creating an Index with Hibernate

Creating an Index with Hibernate

January 4th, 2010 by Sam

The job of creating a SQL Index is rightly the job of a DBA. However, sometimes the need for an index is blindingly obvious and only a mad DBA would miss the opportunity to create a lookup.

Unfortunately, annotations for index hinting did not make it into the JPA 2 specification.

In this post we’re highlighting a Hibernate annotation - @org.hibernate.annotations.Index - that hints for the creation of an index on various columns.

Consider a TABLE that has been constructed for doing lookups between two COLUMNs - and the search can happen on either COLUMN. If one is already assigned to be the @Id, lookups in the opposite direction will be horrendously slow, especially as the database grows.

The following Hibernate annotation will ensure that the myName column is given an index

@org.hibernate.annotations.Index(name = "myNameIndex")
private String myName;

which will result in the equivalent of typing the following native SQL command

CREATE INDEX myNameIndex ON MyEntity(myName);

optimising queries such as

em.createQuery(
    "SELECT e FROM MyEntity e WHERE e.myName = :myName"
    ).setParameter("myName", myName).getResultList();

If you are using a @OneToMany Collection with a mappedBy entry, then you should probably consider indexing the mappedBy property - you’ll notice an instant speedup in the retrieval of your entities.

An alternative way to define the index is to include everything in the supplementary @Table annotation. This approach also allows for a multi-column index

@Entity
@org.hibernate.annotations.Table(
    name="Forest",
    indexes = { @Index(name="idx", columnNames = { "name", "length" } ) }
    )
public class Forest { ... }

Note that in some cases, developers should consider using @UniqueConstraint instead of building an index and allowing multiple results.

This entry was posted by by Sam on Monday, January 4th, 2010 at 4:20 pm, and is filed under Database, EJB3, Hibernate, J2EE, Java 6, Persistence. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

9 comments on “Creating an Index with Hibernate”

Caleb wrote:

April 22nd, 2010 at 3:07 pm

Why would you use Hibernate to create an index instead of just designing the right indexes into your database schema in the first place?

Sam wrote:

April 22nd, 2010 at 5:39 pm

@Caleb Hibernate can create schemas automatically from the Java classes. But if using Hibernate to communicate with an existing schema, then you’re right, this should be part of the schema.

Diego Bravo wrote:

May 21st, 2010 at 12:29 am

IMO this “facility” must be carefully restricted to development environments. It would be catastrophic is a bug in the application goes to the point of issuing a remaining DROP TABLE X; CREATE TABLE X in production.

Since this CAN happen, I simply avoid using it. Ever. Yet I can’t understand why it would be preferable to executing an SQL script. Learning how to execute a shell script or SQL script should be easier than learning a convoluted Hibernate API to do non standard operations. Of course YMMV.

sub wrote:

July 22nd, 2010 at 1:24 pm

what if my product is downloaded by lot of people without db knowledge and they wants single click installation. creating tables,indexes etc should happen programatically.

So i think creating table , index etc should be part of jpa spec

Bojan wrote:

November 11th, 2010 at 4:06 pm

Sub, I don’t understand your point. DBs, tables, indices and other DB objects were created automatically long before JPA. You just insure that DDL invocation is part of singe-click installation.

I agree with Diego on this completely.

dale wrote:

February 11th, 2011 at 5:46 pm

“Learning how to execute a shell script or SQL script should be easier than learning a convoluted Hibernate API to do non standard operations”

too right. especially when these days 14 year old kidscan execute sql scripts easy, piece of cake.

Tiny Tim wrote:

March 11th, 2011 at 3:08 pm

Abstracting the persistence layer from SQL scares you? Too bad.

No one cares what you think, naysayers. Just cuz you don’t want it doesn’t mean there aren’t people who do.

You don’t get it? Try reading a book or thinking before you type.

Tim Mattison wrote:

April 6th, 2011 at 3:34 pm

Guys, I think the real purpose for this mechanism (that is, automatically generating the database from your Java classes via Hibernate) is to save you the work of doing that process by hand. If Hibernate doesn’t provide this mechanism then you have to go through each class and carefully script every class and data member. There is little to no benefit of doing that since you have to precisely match what Hibernate would’ve generated anyway.

That said you would only really use this to generate the database in a development environment and you’d distribute SQL scripts based off of that. When you do an upgrade that modifies the schema you’d rebuild your development environment, use a tool that can do a schema diff (like DB Solo, or Aqua Data Studio) against production, and then tune the result by hand.

This auto-generation feature is mostly for convenience in development because there are schema changes that Hibernate can’t act on automatically. With those kind of changes there’s a chance Hibernate will do something stupid but it appears to try pretty hard to avoid those situations and favors inaction versus dropping tables or columns.

Creating indexes using this feature means that you don’t need to have developers write the application, test it, then have a DBA go in and re-create the schema by hand and put those indexes in that the developers already determined were necessary. The flip side of this is that there should always be a DBA on the other end of the process determining what indexes the developers missed. That information should be communicated back to the developers so they can either add it to the codebase in the form of annotations or as an automated process that adds the proper SQL to any and all SQL scripts that come out of their development environments. The fewer manual steps in the process the more reliable your final build will be.

zet wrote:

November 15th, 2011 at 10:34 pm

Thanks for sharing.

Why aren’t there any new blog entries?