Eclipse Community Forums: EclipseLink » Generate index for foreign keys in oracle

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)

Show: Today's Messages :: Show Polls :: Message Navigator

Generate index for foreign keys in oracle [message #820806]

Wed, 14 March 2012 15:35

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

Report message to a moderator

Re: Generate index for foreign keys in oracle [message #825210 is a reply to message #820806]

Tue, 20 March 2012 15:58

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

Report message to a moderator

Re: Generate index for foreign keys in oracle [message #825784 is a reply to message #825210]

Wed, 21 March 2012 09:01

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.

Report message to a moderator

Re: Generate index for foreign keys in oracle [message #848445 is a reply to message #825784]

Wed, 18 April 2012 08:23

Ga'bor Pe'ntek

Messages: 16
Registered: July 2009

Junior Member

I have finished the patch. It works, but needs review.

Attachment: foreign-key-index.patch
(Size: 4.66KB, Downloaded 341 times)

[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

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

Report message to a moderator

Re: Generate index for foreign keys in oracle [message #1440285 is a reply to message #849837]

Wed, 08 October 2014 06:54

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

Report message to a moderator

Previous Topic:	EclipseLink with dynamically defined object model?
Next Topic:	Exception Description: Missing descriptor for [class strategysvcs.entity.StrategyProfileDetails

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 18:07:03 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter