Can Hibernate generate indexes from mapping files?

mtraudt · **Joined:** Wed Sep 08, 2004 1:23 pm **Posts:** 5

gavin wrote:

you may now use a comma-separated list of index names

Why not provide a way to specify multicolumn indexes at the entity/table level. This would allow you to specify the order of the columns explicitly for each index, as well as whether the sort order should be ascending or descending.

Anodos · **Joined:** Wed Feb 25, 2004 6:23 pm **Posts:** 39

mtraudt wrote:

Why not provide a way to specify multicolumn indexes at the entity/table level. This would allow you to specify the order of the columns explicitly for each index, as well as whether the sort order should be ascending or descending.

I think this is a very good idea. Our current DB can be very picky about index creation. In order to optimize table lookup, most columns end up in both multi-column indices and also multiple indices. The order that the columns appear in the index is important as well. For our app, it would be nice not to involve the DB admin in index maintenance. Just let Hibernate handle everything.

RobJellinghaus · **Joined:** Thu Jan 08, 2004 6:17 pm **Posts:** 278

It would be useful for us too. I'll look into creating a JIRA entry for it (which the other posters to this thread are also welcome to do -- it has a much bigger chance of getting done that way; our comments in this thread will have basically no effect since the Hibernate maintainers are so busy and the forum is so large).

bonnyr · **Posted:** Thu Jun 30, 2005 1:39 am

Rob,

RobJellinghaus wrote:

It would be useful for us too. I'll look into creating a JIRA entry for it (which the other posters to this thread are also welcome to do -- it has a much bigger chance of getting done that way; our comments in this thread will have basically no effect since the Hibernate maintainers are so busy and the forum is so large).

Has this issue been submitted to JIRA? I could not find it.
Also, I think mtraudt suggestion above is a good way to handle this. In
this case there will be a very explicit definition of the index makeup,
it is not restricted then to property declarations (within the file) and, in
the case of multi column indexes (at least) is a more natural way of
presenting it.

ractoc · **Joined:** Mon Mar 05, 2007 2:25 am **Posts:** 12

gavin wrote:

you may now use a comma-separated list of index names

Does this also work with unique constraints?
I have a situation where one comlumn has to be part of two different multi-column unique keys.

e.g.:
city - province
province - country

Mark

vikneswar_me5 · **Joined:** Mon Dec 24, 2007 6:21 am **Posts:** 8

hi,

I am doing a application that needs multi-clumn index.

I explain the thing:

I have user and group tables as well as objects.

In the user Object, userid is the primary key..

In the group Object, i want to make userid and groupid as index.

I made index named as "grp_usr_id" in the sqlserver db (groups table)

I did like this:

<class name="com.meru.gmt.bo.groups.GroupsBean" table="usergroup">

<id name="groupId" type="java.lang.String" column="groupid" index="grp_usr_id">
<generator class="increment"/>
</id>

<property name="groupName" index="grp_usr_id">
<column name="groupName" />
</property>

<many-to-one name="users" insert="false" update="false" class="com.meru.gmt.bo.users.UsersBean" not-null="true" lazy="false" >
<column name ="userid"/>
</many-to-one>

</class>

but i got the mapping error on server displayed as:

ERROR [XMLHelper] Error parsing XML: XML InputStream(6) Attribute "index" must be declared for element type "id".

help me...

Thanks in advance..

arturotena · **Posted:** Fri Oct 31, 2008 7:38 pm

Code:

import org.hibernate.annotations.Table;
import org.hibernate.annotations.Index;

@Entity
@Table(
  appliesTo="MyTable", indexes=
    @Index(
      name="IDX_MyTable",
      columnNames = {"my_column_1", "my_column_2"}
    )
)
public class MyTable implements Serializable {
//...

melih · **Joined:** Fri Dec 04, 2009 4:42 am **Posts:** 5

I've found a tricy solution for that. But I don't know it is a bug or not :)
Anyways I am using hibernate version 2.X and have to create multiple index for one column. And tried that
If you define in hibernate mapping :

Code:

<property name="columnOne" column="column_one" type="long" not-null="true" index="INDEX_ONE">
</property>
<property name="columnTwo" type="long" not-null="true" index="INDEX_ONE">
       <column name="column_two" index="ANOTHER_INDEX">
</column>
</property>

And this created an index SQL like that :

Code:

“CREATE INDEX "INDEX_ONE" ON "SOME_TABLE" ("COLUMN_ONE", "COLUMN_TWO")”
“CREATE INDEX "ANOTHER_INDEX" ON "SOME_TABLE" ("COLUMN_TWO")”

http://melihbirim.com/2010/02/05/multip ... rnate-2-x/

brettryan · **Joined:** Fri Jul 09, 2010 4:52 am **Posts:** 4

If I were to use a multi-column index, how does it know the order of the columns? I actually want two indexes, as an example

IX_OrdCust
OrderID
CustomerID

IX_CustOrd
CustomerID
OrderID

Is this possible?