[eclipselink-users] query optimization for secondary keys

[David Bernstein <dbb.posting@xxxxxxxxx>]

Is there a generic way to have an EJB3 query take advantage of database
secondary composite keys?  If there's no generic way, then I'd accept an
EclipseLink-specific method, but it seems there ought to be a generic JPA
way to do this.
 
For example, suppose there were an SQL table
 
create table su (
  su_id int primary key,
  ri    int not null,
  rt    int not null,
  unique ( ri, rt )
  );
 
where I want su_id to be the primary key, but I also want efficient access
via the composite key (ri,rt) using EJB3/EclipseLink.
 
I think I may have a performance problem because the database is configured
for fast access via the unique composite secondary key, but the cache would
be searched linearly (and the entire table may be sucked in?) when querying
via the secondary key.
 
The @Table annotation has a uniqueConstraints element, but it appears to me
that it’s only used for schema generation, not query optimization.
 
It seems really basic to me to be able to do this so I think I must be
missing something really obvious. My googling and manual reading haven't
helped much.  At the moment the only thing I can think of is to do a native
named query, but that would seem to bypass the persistence provider's cache. 

*Apologies in advance if anybody receives this message twice.*
-- 
View this message in context: http://old.nabble.com/query-optimization-for-secondary-keys-tp29939281p29939281.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.