Hi,

we switched from Hibernate 1.2.3 to Hibernate 2.0.2 and noticed that the SchemaExport tool is not any longer automatically generating indexes for foreign key columns. This was very convenient under Hibernate 1.2.x. I haven't found any reference to this fact in the release notes of Hibernate 2, the migration guide or the forums.

Before I hack something into the SchemaExport tool myself I wanted to ask if I missed something. Do I have to do something special to activate fk index generation under Hibernate 2?

Thanks for any hint,
Kay.

P.S. This is my first post to Hibernate Forum and I just wanted to congratulate you guys on such a great project!

The foreign key itself adds an implicit index in most databases.

[quote="gavin"]The foreign key itself adds an implicit index in most databases.[/quote]

Interesting. We use Oracle 9.2 and it apparently doesn't. Without manually generated indexes for FKs the performance is really bad. Can anyone confirm this for Oracle?

If I needed to reintroduce Hibernate 1.2.x's ability to automatically generate indexes for FKs, is it simply switched off in Hibernate2 or would I have to recode the logic? (I'll look at the code, of course, but I'd appreciate any hint that might avoid unnecessary double work).

Thanks,
Kay

OK, I don't understand this. Either this is so trivial that there is no entry in the FAQ or even any post in the Forum or nobody needs indexes any more !? I would be very thankful for any help!

As opposed to Hibernate 1.2 the code in Hibernate2 does not automatically add Indexes to mapped foreign-key / one-to-many Collections. In Hibernate 1.2 this was done at the end of method "secondPassCompile" of cirrus.hibernate.map.Collection. In Hibernate2 this method was moved to net.sf.hibernate.cfg.Binder and is now called "bindCollectionSecondPass" but the index creation part was discarded. So it's no wonder the SchemaExport tool does not create SQL for FK index creation in Hibernate2.

How do other people cope with this? The only Dialect subclass that still creates indexes is MySQLDialect by combining index creation with FK definition in a single SQL statement. According to Gavin in most DBMSs there is no need to specify an index if the FK is defined. So this must be true then for all DBs except MySQL? But I definitely need to create them for Oracle 9.2, without them the performance hit is very big (as one would expect). I also did not find any way to at least manually specify the need for an index on the FK column in the XML mapping files.

Thanks for any hints. I don't really know where to look next.

Kay

I can definitely confirm that this is not true on Oracle. Oracle will only create indexes automatically for unique columns and PK constraint columns.


Yeah, in the case of FK constraint without an index, Oracle locks the entire child table while updating the value of a FK column in the parent table. Ouch...

Its pretty easy to script this index creation, though, using the ALL_CONSTRAINTS and ALL_CONS_COLUMNS views along with some dynamic SQL.

Ah. Well, I guess I suck :(

shit.

We should auto-generate indexes for collection <key> columns, no doubt.


As a temporary workaround, use mappings like this:



but I guess I need to figure out which dbs do this implicitly or whether I am just completely wrong and none do!

OK, i don't completely suck. Turns out that this is platform specific. Shit I will have to do some research!

Thanks a lot for the confirmation and the suggested workarounds. Still looking forward to automatic index generation though.

Kay

what's up with the language, Gavin? Are you frustrated?

:-)

I'm annoyed with myself because I didn't properly check my facts before changing something. :-(