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)

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

Wed, 14 March 2012 11:35

Eclipse User

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 11:58

Eclipse User

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.

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

Wed, 21 March 2012 05:01

Eclipse User

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 04:23

Eclipse User

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

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

[Updated on: Wed, 18 April 2012 04:27] by Moderator

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

Thu, 19 April 2012 09:47

Eclipse User

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.

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

Wed, 08 October 2014 02:54

Eclipse User

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:

-=] Back to Top [=-

Current Time: Tue Jun 17 15:11:16 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter