Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Generate index for foreign keys in oracle(How to enable automatic generation of indices for foreign key fields using Oracle database)
Generate index for foreign keys in oracle [message #820806] Wed, 14 March 2012 15:35 Go to next message
Ga'bor Pe'ntek is currently offline Ga'bor Pe'ntek
Messages: 16
Registered: July 2009
Junior Member
Hi All,

in our application we found that indexing foreign key fields in the database result in highly increased performance. Therefor I tried to find some eclipselink or JPA setting that enables the automatic generation of indices for foreign key columns.

Unfortunately I found nothing about the topic so I started reading the eclipselink code.

While searching for the proper setting, I found that overriding the
org.eclipse.persistence.internal.databaseaccess.DatabasePlatform
in my case
Oracle10DatabasePlatform
is quite easy. My class should be added to persistence.xml in the databasePlatform property.

However I found no documentation on overriding the
org.eclipse.persistence.tools.schemaframework.TableDefinition
class which handles table creation. I would like to add the foreign key creation code to the
createOnDatabase
method just like the
createIndexOnPrimaryKeyOnDatabase()
and
createIndicesOnUniqueKeysOnDatabase()
methods.

How can I override TableDefinition with my custom class?

Thanks in advance!
Gábor
Re: Generate index for foreign keys in oracle [message #825210 is a reply to message #820806] Tue, 20 March 2012 15:58 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I don't think you can do this without extending EclipseLink.
I think a mechanism to allow the platform, or user events to customize table generation would be useful, so please log a bug, and consider adding a patch if you are willing to.

You could just use your own table creation scripts to create your indexes, or use the @Index annotation in EclipseLink to index the foreign keys.


James : Wiki : Book : Blog : Twitter
Re: Generate index for foreign keys in oracle [message #825784 is a reply to message #825210] Wed, 21 March 2012 09:01 Go to previous messageGo to next message
Ga'bor Pe'ntek is currently offline Ga'bor Pe'ntek
Messages: 16
Registered: July 2009
Junior Member
Thanks!

I will try to make a patch and submit it if it works.
Re: Generate index for foreign keys in oracle [message #848445 is a reply to message #825784] Wed, 18 April 2012 08:23 Go to previous messageGo to next message
Ga'bor Pe'ntek is currently offline Ga'bor Pe'ntek
Messages: 16
Registered: July 2009
Junior Member
I have finished the patch. It works, but needs review.

[Updated on: Wed, 18 April 2012 08:27]

Report message to a moderator

Re: Generate index for foreign keys in oracle [message #849837 is a reply to message #848445] Thu, 19 April 2012 13:47 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Looks good, except for,

> if (!primKeyList.contains(foreignKey.getName())) {

The name of the foreign key is not the column name, but the constraint name. To do what you want you would need to check if any (or all) of the foreign key fields are in the primary key list.

Also, I would make the shouldCreateIndicesForForeignKeys a variable, as I don't think this is database specific, but application specific. Databases don't create indexes on foreign keys automatically as you do not normally query on foreign keys, foreign keys are use to query primary keys, which are indexed. Foreign keys however are still commonly queried in most applications, both for OneToMany as well as ManyToMany, which is why you see the performance boost. It might even be a good idea to make your new option true by default, and allow a persistence unit property "eclipselink.ddl-generation.index-foreign-keys" to disable it.


James : Wiki : Book : Blog : Twitter
Re: Generate index for foreign keys in oracle [message #1440285 is a reply to message #849837] Wed, 08 October 2014 06:54 Go to previous message
Luca Morettoni is currently offline Luca Morettoni
Messages: 1
Registered: October 2014
Junior Member
Hello, even this topic is quite old is the only one related to the "index-foreign-keys" feature.
Probably I found an issue over this fantastic feature: if I have an existing Oracle DB and I have the index-foreign-keys active the index are not created. If I try to use the same feature (with the existing tables) under MySQL missing indexes are added.
I opened a bug about that: https://bugs.eclipse.org/bugs/show_bug.cgi?id=445522
Previous Topic:EclipseLink with dynamically defined object model?
Next Topic:Exception Description: Missing descriptor for [class strategysvcs.entity.StrategyProfileDetails
Goto Forum:
  


Current Time: Sat Oct 25 09:50:18 GMT 2014

Powered by FUDForum. Page generated in 0.02112 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software