Adding index on a existing table in hibernate

Question

I have OneToMany relationship between Job and Detail entities and both have common parent(BaseEntity)

@MappedSuperclass
public abstract class BaseEntity {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private Long id;
@Version
@Column(name = "version")
private Integer version;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "create_time")
private Date createTime;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "last_update_time")
private Date lastUpdateTime;

@PrePersist
public void prePersist() {
    createTime = new Date();
}

@PreUpdate
public void preUpdate() {
    lastUpdateTime = new Date();
}

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public Integer getVersion() {
    return version;
}

public void setVersion(Integer version) {
    this.version = version;
}

public Date getCreateTime() {
    return createTime;
}

public void setCreateTime(Date createTime) {
    this.createTime = createTime;
}

public Date getLastUpdateTime() {
    return lastUpdateTime;
}

public void setLastUpdateTime(Date lastUpdateTime) {
    this.lastUpdateTime = lastUpdateTime;
}
}  

Job

public class Job extends BaseEntity {

    ...
    @OneToMany(mappedBy = "job", cascade = CascadeType.ALL, fetch=FetchType.EAGER)
    @OrderColumn
    private List<Detail> detailList;
    ...
}  

Detail

@Entity
public class Detail extends BaseEntity {
    ...
    @ManyToOne
    private Job job;
    ...
}  

The job table has about 200K rows, and the detail table has 3M rows. If I get the details of a specific job, it takes 2sec with a native query. I created index for job_id column by running sql statement. And the query response is about 20 msec now. However, the app is in production now and it has data therefore I want to create the index by adding annotation not running sql statement. I added @OrderColumn annotation in order to create index, and while fetching it gives the following exception. By the way, the job_id column on Detail table was named by hibernate. I did not explicitly give a name for the column.

org.hibernate.HibernateException: null index column for collection: tr.com.ttnet.beyazfatura.model.Job.detailList
    at org.hibernate.persister.collection.AbstractCollectionPersister.readIndex(AbstractCollectionPersister.java:770)
    at org.hibernate.collection.PersistentList.readFrom(PersistentList.java:402)
    at org.hibernate.loader.Loader.readCollectionElement(Loader.java:1156)
    at org.hibernate.loader.Loader.readCollectionElements(Loader.java:774)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:622)
    at org.hibernate.loader.Loader.doQuery(Loader.java:829)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.loadCollection(Loader.java:2166)
    at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:62)
    at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:627)
    at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:83)
    at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1863)
    at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:479)
    at org.hibernate.engine.StatefulPersistenceContext.initializeNonLazyCollections(StatefulPersistenceContext.java:900)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:279)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    at tr.com.ttnet.beyazfatura.ejb.ModelOperations.getJobs(ModelOperations.java:488)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.bea.core.repackaged.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
    at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
    at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
    at com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
    at com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
    at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at com.bea.core.repackaged.springframework.jee.spi.MethodInvocationVisitorImpl.visit(MethodInvocationVisitorImpl.java:37)
    at weblogic.ejb.container.injection.EnvironmentInterceptorCallbackImpl.callback(EnvironmentInterceptorCallbackImpl.java:54)
    at com.bea.core.repackaged.springframework.jee.spi.EnvironmentInterceptor.invoke(EnvironmentInterceptor.java:50)
    at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at com.bea.core.repackaged.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
    at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
    at com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
    at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at com.bea.core.repackaged.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy250.getJobs(Unknown Source)
    at tr.com.ttnet.beyazfatura.ejb.ModelOperations_vrg1wg_ModelOperationsLocalImpl.__WL_invoke(Unknown Source)
    at weblogic.ejb.container.internal.SessionLocalMethodInvoker.invoke(SessionLocalMethodInvoker.java:39)
    at tr.com.ttnet.beyazfatura.ejb.ModelOperations_vrg1wg_ModelOperationsLocalImpl.getJobs(Unknown Source)
    at tr.com.ttnet.datamodel.JobLazyDataModel.load(JobLazyDataModel.java:86)
    at org.primefaces.component.datatable.DataTable.loadLazyData(DataTable.java:644)
    at org.primefaces.component.datatable.DataTableRenderer.encodeMarkup(DataTableRenderer.java:178)
    at org.primefaces.component.datatable.DataTableRenderer.encodeEnd(DataTableRenderer.java:103)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:879)
    at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:61)
    at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:45)
    at org.primefaces.component.panel.PanelRenderer.encodeContent(PanelRenderer.java:185)
    at org.primefaces.component.panel.PanelRenderer.encodeMarkup(PanelRenderer.java:108)
    at org.primefaces.component.panel.PanelRenderer.encodeEnd(PanelRenderer.java:55)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:879)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1655)
    at javax.faces.render.Renderer.encodeChildren(Renderer.java:164)
    at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:849)
    at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:57)
    at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:45)
    at org.primefaces.component.layout.LayoutUnitRenderer.encodeEnd(LayoutUnitRenderer.java:51)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:879)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1655)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1651)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1651)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1651)
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:395)
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:127)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:117)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:97)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:135)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:309)
    at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
    at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300)
    at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:79)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at tr.com.ttnet.filter.AuthenticationFilter.doFilter(AuthenticationFilter.java:40)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3715)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3681)
    at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
    at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
    at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2277)
    at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2183)
    at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1454)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:207)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:176)
2012-02-13 14:08:41,462 WARN  (LoadContexts.java:132) - fail-safe cleanup (collections) : org.hibernate.engine.loading.CollectionLoadContext@d39617<rs=weblogic.jdbc.
wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl@a67>
2012-02-13 14:08:41,463 WARN  (CollectionLoadContext.java:370) - On CollectionLoadContext#cleanup, localLoadingCollectionKeys contained [1] entries

Answer 1

" However, the app is in production now and it has data therefore I want to create the index by adding annotation not running sql statement"

Wrong. It is more important to use a script when creating indexes in production than in other environments. There needs to be a proper round of impact analysis and configuration management.

Anyway, I think you have misunderstood the purpose of the annotation. @OrderColumn provides an array index for controlling the order of the records in the result set, and appears to have no significance for improving performance of the underlying database query. (However I am a database person rather a Hibernate person, so my interpretation may be wrong.)

As for why you get that specific excpetion, try reading this response to a related question.