Hibernate @Index on @Enumerated field doesn't work

Question

I am using Hibernate to talk to my DB. I have one field that is an enumeration and it is going to be used in a slow query. So I'd like to index it. I've put the following annotations on the field:

@Column(name="RIGHT_TYPE", unique=false, nullable=false, length=10)
@Enumerated(EnumType.STRING)
@Index(name = "ABC_INDEX")
protected RightType rightType;

However, I don't see any index on that field created.

I'm using org.hibernate.dialect.Oracle9Dialect. The (trimmed down) ddl is:

create table DOCUMENTS (documentID varchar2(255 char) not null, owner_principalId varchar2(255 char), primary key (documentID))
create table DOC_RIGHTS (document raw(255) not null, primary key (document))
create table PRINCIPAL (TYPE varchar2(31 char) not null, principalId varchar2(255 char) not null, displayName varchar2(255 char), primary key (principalId))
create table PRINCIPAL_RIGHTS (id varchar2(255 char) not null, PRINCIPAL_ID varchar2(255 char) unique, DOCUMENT_RIGHT_ID raw(255) unique, primary key (id))
create table RIGHTS (TYPE varchar2(31 char) not null, id number(19,0) not null, RIGHT_TYPE varchar2(10 char) not null, PRINCIPAL_RIGHT_ID varchar2(255 char) unique, primary key (id))
create table ROLE_MAP (PRINCIPAL_ID varchar2(255 char) not null, ROLE_ID varchar2(255 char) not null)
alter table DOCUMENTS add constraint FKDC2BB35E362547 foreign key (owner_principalId) references PRINCIPAL
create index PRINCIPAL_INDEX on PRINCIPAL_RIGHTS (PRINCIPAL_ID)
alter table PRINCIPAL_RIGHTS add constraint FKB32239ADFB30571B foreign key (PRINCIPAL_ID) references PRINCIPAL
alter table PRINCIPAL_RIGHTS add constraint FKB32239ADE1F0C813 foreign key (DOCUMENT_RIGHT_ID) references DOC_RIGHTS
create index RIGHT_TYPE_INDEX on RIGHTS (RIGHT_TYPE)
alter table RIGHTS add constraint FKF34FBA9CA09D6215 foreign key (PRINCIPAL_RIGHT_ID) references PRINCIPAL_RIGHTS
alter table ROLE_MAP add constraint FKA413CD78FB30571B foreign key (PRINCIPAL_ID) references PRINCIPAL
alter table ROLE_MAP add constraint FKA413CD7883A04939 foreign key (ROLE_ID) references PRINCIPAL
create sequence RIGHTS_SEQUENCE

But I can create one manually if I wish so. Why is that? Is there any way to force Hibernate to make it happen?

Answer 1

I did a test with the following entity:

@Entity
public class EntityWithIndices {
    @Id
    @GeneratedValue
    private Long id;

    @Column(name = "GENDER_TYPE", unique = false, nullable = false, length = 10)
    @Enumerated(EnumType.STRING)
    @Index(name = "ABC_INDEX")
    protected GenderType genderType;

    // getter and setters
} 

With the following enum:

public enum GenderType {
    FEMALE("0"), MALE("1");

    private final String genderCode;

    public String getCode() {
        return this.genderCode;
    }

    private GenderType(String code) {
        this.genderCode = code;
    }
}

And with Hibernate Annotations 3.4.0.GA, MySQL 5.1 as database, the index gets generated. I can't try with Oracle though. I did a search for existing issues (project z- Hibernate Annotations, Any Issue Type, on "index") and couldn't find any recent issue with recent versions of Hibernate annotations (which does not mean there is no problem). If you create an issue, try to provide a simple runnable test case and the SchemaExport logs, this would highly increase your chances to get it fixed.

Answer 2

This seems to work just as you've got in your question. I've created a little test app that demonstrates this working in a standalone app.

One thing that my be happening is that hibernate will not attempt to create indexes during an update operation. It will create them from scratch hbm2ddl=create but not during an update. The hibernate teams standard response for this being reported as a bug is 'hbm2ddl is only for development and therefore indexes are not required and you should get a DBA.

My personal recomendation if this is the problem is to start to use liquibase to manage creating things like indexes.