Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 14 posts ] 
Author Message
 Post subject: hbm2ddl: Automatic creation of indexes on foreign keys
PostPosted: Wed Oct 19, 2005 5:53 am 
Beginner
Beginner

Joined: Wed Jan 21, 2004 10:15 am
Posts: 24
Location: Munich, Germany
Hibernate version: 2.1.7c, 3.0.5

Hello!

We have an application that runs with MySQL and Oracle. The used database schema is created using hbm2ddl out of the various hbm.xml files.

The problem is that indexes on foreign key columns are only created automatically with MySQL, but not with Oracle as the target database. When explicitly defining an index on the table using "index=xxx" in the column description of the many-to-one field, it is also not quite satisfactory: An index is created with Oracle (good), but two indexes are created with MySQL (bad; the automatically created one and the additional one).

Can the automatic creation of indexes on foreign key columns be somehow switched on? It seems that this is already an older issue: http://forum.hibernate.org/viewtopic.php?t=924910

Thanks!
Christian


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 19, 2005 7:48 pm 
Expert
Expert

Joined: Mon Jul 04, 2005 5:19 pm
Posts: 720
are you using the same dialect for both hbm2ddl generations?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 21, 2005 2:15 am 
Beginner
Beginner

Joined: Wed Jan 21, 2004 10:15 am
Posts: 24
Location: Munich, Germany
Hi!

You mean for Oracle and MySQL? No. With version 2.1.7c: for MySQL, I use net.sf.hibernate.dialect.MySQLDialect, and for Oracle, I use net.sf.hibernate.dialect.OracleDialect. For 3.0.5, I use org.hibernate.dialect.MySQLInnoDBDialect and oracle.jdbc.driver.OracleDriver, respectively.

But I'm not sure if I understood your question correctly.

Christian


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 21, 2005 3:11 am 
Expert
Expert

Joined: Mon Jul 04, 2005 5:19 pm
Posts: 720
oracle.jdbc.driver.OracleDriver is not a dialect . you might want to look at extending the Oracle dialect and overriding the methods related to DDL generation.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 21, 2005 3:25 am 
Beginner
Beginner

Joined: Wed Jan 21, 2004 10:15 am
Posts: 24
Location: Munich, Germany
Oops, copy/paste error. I of course meant org.hibernate.dialect.OracleDialect.

But the basic problem remains: Using the MySQL dialects, the indexes on foreign key columns aren created automatically, while this is not done with the Oracle dialects. Or does this happen automatically with your configuration?

Christian


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 21, 2005 3:40 am 
Expert
Expert

Joined: Mon Jul 04, 2005 5:19 pm
Posts: 720
If you would like to change the DDL generation of MySQL, extend the MySQL dialect and do some experimenting.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 21, 2005 4:12 am 
Beginner
Beginner

Joined: Wed Jan 21, 2004 10:15 am
Posts: 24
Location: Munich, Germany
Maybe I made my point a bit unclear: I do like the behaviour of MySQL dialect (automatic index creation on foreign keys), but I would like to have that also with the Oracle dialect.

Of course, I can extend the Oracle dialect and add the correct 'create index' statement into the getAddForeignKeyConstraintString method like in the MySQL dialect.

But I wonder whether this problem occurs only to me (then extending the dialect class is indeed the correct solution), or whether this should be solved in general. And when you consider the old post (see my initial posting), both Steve and Gavin seemed to agree that automatic creation of the index on a foreign key column is desirable. On the other hand, the posting was done two years ago, so maybe there is indeed no common need for this?

Christian


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 21, 2005 4:15 am 
Expert
Expert

Joined: Mon Jul 04, 2005 5:19 pm
Posts: 720
so ... make it happen and submit a patch.


Top
 Profile  
 
 Post subject: in Oracle you should really create indexes on foreign keys
PostPosted: Wed Dec 07, 2005 3:02 pm 
Newbie

Joined: Wed Dec 07, 2005 2:52 pm
Posts: 2
Location: Seattle WA
Read this to see why you need to create indexes for foreign keys for Oracle:

http://asktom.oracle.com/~tkyte/unindex/index.html

and:

http://asktom.oracle.com/pls/ask/f?p=49 ... 2016138754

Funny how that's not the default behaviour of the schema export code.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 02, 2008 9:50 am 
Newbie

Joined: Thu Feb 02, 2006 9:39 am
Posts: 19
I do have the same problem as the OP, and I'm quite sure it's a common pitfall for many applications using different RDMS.

I first use the trick of defining both a foreign key and a index in the hibernate mapping. Thus in Oracle and such RDMS, the foreign key is created without an index, but then hibernate explicitly ask for an index creation and it's cool.

The main drawback is that on MySQL, you will have both an index explicitly created by the mapping, and one other automatically created by the RDMS during the foreign key creation.

So I named both foreign key and index with an overlapping name like in the following mapping:

Code:
<hibernate-mapping>
<class table="document" name="xxx.model.Document">
[...]
  <set cascade="all-delete-orphan" lazy="true" name="sentences" inverse="true">
   <key column="idDocument" not-null="true" foreign-key="documentIdx" on-delete="cascade"/>
   <one-to-many class="xxx.model.Sentence"/>
  </set>
[...]
</class>

<class table="sentence" name="xxx.model.Sentence">
[...]
  <many-to-one class="xxx.model.Document" cascade="save-update" name="document" column="idDocument" not-null="true" foreign-key="documentIdx" index="documentIdx"/>
[...]
</class>


It definitely works on Oracle (you have both index and FK), and in MySQL the index is only create once. Cool ? Not really because the explanation is quite ugly: The index is created first. Then the Foreign Key creation failed because an index with the same name as the FK alread exists in the database. Too bad it's not the other waw around (FK first, then index fails).

In the end you have a MySQL database quite speedy, but with not so much integrity checks :/

I finally found the solution, using "<database-object>" in the mapping. You could manually create an index after table creation (thus after FK creation!). But even better, you could define a dialect scope in which apply the database object. You just have to name the dialect that you have problem with, and you will have a perfectly working database at least !!

Code:
<database-object>
<create>CREATE INDEX documentIdx ON sentence(idDocument)</create>
<drop>DROP INDEX documentIdx ON sentence</drop>
<dialect-scope name="org.hibernate.dialect.Oracle9Dialect"></dialect-scope>
</database-object>


I highly suggest someone write a definitive workaround for this problem and put it in the FAQ, in the documentation, or wherever it belongs to help other people who are getting stuck on this one. Because I hardly manage to find clues on the net about what I consider a basic and naïve topic.

Links that may help go further:

http://www.hibernate.org/hib_docs/nhibe ... ase-object
http://jira.nhibernate.org/browse/NH-1058


Top
 Profile  
 
 Post subject:
PostPosted: Sat Dec 06, 2008 5:16 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8611
Location: Neuchatel, Switzerland (Danish)
Thanks for writeup - about the wiki/faq/docs then it is on a wiki you are more than welcome to update and you can provide patches for docs in jira

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject: Re: hbm2ddl: Automatic creation of indexes on foreign keys
PostPosted: Wed Aug 12, 2009 1:31 am 
Newbie

Joined: Mon Jul 20, 2009 3:10 am
Posts: 9
I guess this issue still persists with the latest release of hibernate annotations 3.4.0.GA. If generating indexes is going to be part of foreign key generation, then we cannot have a single JPA entity model to seamlessly generate a DDL for different databases.

It would be nice to provide a different name for the index generated below in the Foreign Key annotation.
alter table user_im
add index fk_user_im_user_id (user_id),
add constraint fk_user_im_user_id
foreign key (user_id)
references user (id);

I would rather like to see an idx_ prefix for the generated index

alter table user_im
add index idx_user_im_user_id (user_id),
add constraint fk_user_im_user_id
foreign key (user_id)
references user (id);

Any pointers on how to achieve this?


Top
 Profile  
 
 Post subject: Re: hbm2ddl: Automatic creation of indexes on foreign keys
PostPosted: Wed Aug 12, 2009 6:46 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8611
Location: Neuchatel, Switzerland (Danish)
Tried tweaking Hibernate NamingStrategy ?

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject: Re: hbm2ddl: Automatic creation of indexes on foreign keys
PostPosted: Mon Nov 16, 2009 5:02 am 
Newbie

Joined: Mon Jul 20, 2009 3:10 am
Posts: 9
I briefly did look into the code responsible for schema generation and the naming strategy, but I guess the foreign key name is used to generate the mentioned index also for MySQL database. So I am not sure, if it would be possible to differentiate the foreign key name and the index name in the code. Appreciate any inputs on this topic.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 14 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.