Indexes on @JoinTable

Quikee · **Joined:** Tue Oct 02, 2007 4:48 am **Posts:** 9

Hello.

To declare indexes on a table/entity I have to add an annotation to the entity like:

Code:

@Entity
@Table(name = "Partner")
@org.hibernate.annotations.Table(appliesTo="Partner", indexes = @Index(name = "PartnerNumber_idx", columnNames = { "partnerNumber" }))
class Partner {
...
   @Column(name="partnerNumber")
   Long partnerNumber;
...
}

or:

Code:

@Entity
@Table(name = "Partner")
class Partner {
...
   @Column(name="partnerNumber")
   @Index(name="partnerNumber_IDX")
   Long partnerNumber;
...
}

Where do I declare the index for a @JoinTable in for example @ManyToMany relationship??

For example:

Code:

@Entity
@Table(name = "Partner")
class Partner {
...
   @Column(name="partnerNumber")
   @Index(name="partnerNumber_IDX")
   Long partnerNumber;

   @OrderBy("oid ASC")
   @ManyToMany(cascade = CascadeType.ALL)
   @JoinTable(name = "PARTNER_ADDRESSES", joinColumns = @JoinColumn(name="ADDRESSES"), inverseJoinColumns = @JoinColumn(name="PARTNER"))
   private List<Address> addresses = new ArrayList<Address>();
...
}

Thanks.

emmanuel · **Posted:** Mon Oct 29, 2007 3:44 pm

I know I worked on this problem, but I don't remember if I fixed it.
Try @org.hibernate.annotations.Table(appliesTo="AssocTable" ...)

Quikee · **Joined:** Tue Oct 02, 2007 4:48 am **Posts:** 9

emmanuel wrote:

I know I worked on this problem, but I don't remember if I fixed it.
Try @org.hibernate.annotations.Table(appliesTo="AssocTable" ...)

Hello.
Thanks for the reply.

I think you mean this bug report: http://opensource.atlassian.com/projects/hibernate/browse/ANN-630.
Will this be fixed in the next Hibernate Annotations release?

The trick with appliesTo="AssocTable" doesn't work.

emmanuel · **Posted:** Tue Oct 30, 2007 1:59 pm

Ah yes I remember now, so same question for you. why do you need an index on a many to many table? Isn't the PK taking care of your needs?

Quikee · **Joined:** Tue Oct 02, 2007 4:48 am **Posts:** 9

The index on pk is not enough. On oracle the performance drops when the number of objects increase if there is no index on the joined table as it does full table scan for searching the correct pk of the object you navigate from.

Did I miss or misunderstood something?

ylazzari · **Joined:** Fri Oct 12, 2007 5:18 pm **Posts:** 10

Hi, I have the same problem and I was wondering, Emmanuel, what primary key are you talking about?

If we take Quikee's example, his @ManyToMany definition in the Partner entity will generate a PARTNER_ADDRESSES table, with an ADDRESSES column and a PARTNER column. The ADDRESSES column will have a foreign key pointing to the primary key of the table defined in the Address entity and the PARTNER column will have a foreign key pointing to the primary key on the PARTNER table. No primary keys will be generated on the PARTNER_ADDRESSES table. What primary key are you talking about?

mark_in_gr · **Joined:** Mon Sep 05, 2005 4:48 pm **Posts:** 31

I am having the exact same issue w/Hibernate mappings on objects which work over Oracle 10g. In my case, the difference in performance is a couple of minutes sans the index, and under 2 seconds w/it(I manually created an index based on suggestions from Oracle's Performance Analyser). A table scan is also being performed when the index is not present.

On one side of the bi-directional relationship, the mapping is as follows:

Code:

<hibernate-mapping>
    
    <class name="com.xrite.ind.core.Color" table="tblColor">
        <id name="Id" type="long">
            <column name="colorID" />
            <generator class="native" />
        </id>
        <property name="name" column="name" type="string" not-null="true" index="IDX_COLOR_NAME" /> 
        <property name="description" column="description" type="string" />

    . . .

<joined-subclass name="com.xrite.ind.core.Standard" table="tblStandards">
            <key column="standardID" on-delete="cascade"/>
            <property name="masterPanel" column="masterPanel" type="boolean" />
            <property name="panelAutoName" column="panelAutoName" type="boolean" />           
            
            <set name="samples" lazy="true" cascade="save-update" inverse="true" sort="com.xrite.ind.core.ColorDateSort"  >
                <key column="standardID" on-delete="cascade"/>
                <one-to-many class="com.xrite.ind.core.Sample" />
            </set>
            
            <list name="tolerances" table="tblStandardTolerances" fetch="subselect" cascade="save-update" lazy="false" >
                <key column="standardID" not-null="true" />
                <index column="tolerance_index" />
                <many-to-many column="ToleranceID" class="com.xrite.ind.core.tolerances.Tolerance" />
            </list>
                        
            <many-to-one name="shadeSortInfo" class="com.xrite.ind.core.ShadeSortInfo" cascade="save-update" unique="true" index="IDX_COLOR_SSI" lazy="false" >
                <column name="ShadeSortInfoID" />
            </many-to-one>

        </joined-subclass>
  </class>
</hibernate-mapping>

On the other side, we have this snippet:

Code:

<set name="Standards" table="tblStandardTolerances" inverse="true" cascade="save-update">
            <key column="ToleranceID" />
            <many-to-many column="StandardID" class="com.xrite.ind.core.Standard"/>
        </set>

With this relationship and mapping, the PK of the tblStandardTolerances table is StandardID. If I change the PK to be a composite(StandardID, ToleranceID), performance is still slow(table scan). If I switch the inverse side around, this does achieve somewhat of the desired affect by making the ToleranceID the PK, however, this breaks other things in my object model.

There should be a simple way to arbitrarily assign an index to a foreign key column on a many-to-many. Currently, I have to use either straight JDBC or a database object.