When generating a schema SQL via OpenJPA then we do omit the CREATE INDEX if there is a Forein Key CONSTRAINT generated.
For the following please consider an Entity CarLicensePlate which has a @ManyToOne on Customer.
Without giving any further generator options we generate the correct CREATE INDEX statement:
But once we switch on explicit foreign key constraints via
then the index is omitted.
This is ok for most databases as a foreign key constraint they usually automatically internally create an index as well. But sadly this is not the case for Oracle. Here we would actually need both, the constraint and the index.
There is even an own switch IndexLogicalForeignKeys in MappingDefaults. But this doesn't really do what we need right now in this case. It's more a switch to disable index creating even if there is no constraints.
We could either introduce a new flag in MappingDefaults or change the IndexLogicalForeignKeys from boolean to Boolean or even an enum with a FORCED.
Even better would be a DbDictionary specific handling. That way we could create the index automatically for Oracle while skipping it for others.