Index on discriminator column

medeski · **Posted:** Tue Jul 04, 2006 8:12 am

Hi,

I have a rather general question: Does it make sense, to use a db index on a discriminator column? I have the following annotations on a superclass that is extended by to sublcasses:

Code:

@Entity
@Table(name = "tblSubject")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@org.hibernate.annotations.Table(appliesTo="tblSubject", indexes={
      @Index(name="tblSubject_idx_type", columnNames={"type"})})
@DiscriminatorColumn(name = "type", discriminatorType = DiscriminatorType.STRING, length = 4)
public abstract class Subject ....

Is this a common practice or is it better to omit the index when only 2 different values (i.e. the discriminator values of the 2 sub classes) can appear on the type column?

thx,

john

alesj · **Posted:** Tue Jul 04, 2006 10:16 am

Depends on the database provider, number of data, query types, ...
More of a DBA question.

medeski · **Posted:** Tue Jul 04, 2006 12:27 pm

sounds reasonable, I was just wondering if there were some basic rules considered as best practice...

medeski · **Posted:** Tue Jul 04, 2006 12:27 pm

sounds reasonable, I was just wondering if there were some basic rules considered as best practice...

(accidently sent twice)

emmanuel · **Posted:** Fri Jul 07, 2006 9:23 am

this is usually a bad practice since your index is not discriminative enough.