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

James Sutherland
Messages: 1844
Registered: July 2009

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

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 05: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 04: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 72 times)

[Updated on: Wed, 18 April 2012 04: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 09:47

James Sutherland
Messages: 1844
Registered: July 2009

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

Report message to a moderator

Previous Topic:	serialUID mismatch on remote EJB call
Next Topic:	Unable to create query.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 26 04:41:04 EDT 2013