Re: oracle constraint problem, sequences for generating unique IDs

On 31-Mar-09, at 10:11 AM, Vicky C. Miller wrote:
> We are attempting to overcome an oracle constraint problem,  
> specifically:
>
> Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint
> (..SYS_C001711) violated
>
> We've resolved the error with a short term solution but we are  
> trying to get
> away from sequences for generating unique IDs since it is expensive  
> on the
> performance side.  Has anyone implemented WO such that the  
> capability to
> cache sequence numbers is functional and it is NOT necessary to  
> cache and
> query for the next sequence number before making an update to Oracle?


 From the bit above, it's hard for me to tell what that unique  
constraint is on.  I'm assuming that it's a default unique constraint  
on a primary key however I suppose that it could be a unique  
constraint on another column... Also, I'm assuming that you are  
inserting new Enterprise Objects (EO's) and that your concern is over  
how long it's taking to create each one.  If that's not the case, then  
the comments below may not be much help. ;-)

Anyway, if performance is your concern then you've got a couple of  
options:
1) have each application cache several sequence values and use them  
each time a new EO is inserted.
Pro: avoids the round trip on each insert - so it's faster
Con: sequence values are assigned in random order if multiple apps are  
inserting new records.

2) have a trigger on the database side assign the next sequence value  
on insert
Pro: avoids the round trip too so it's also faster
Con: the database is now out out sync with the EOF cache and so you  
need to invalidate the newly created EO

3) switch to using globally unique ID's
Pro: nicely avoid both of the problems with options 1 and 2 above
Con: these are binary and this a little more pain to deal with when  
manually writing SQL.

I hope that helps!
Mark
__
Mark Ritchie
Cocoa and WebObjects Developer
Diamond Lake Consulting Inc.
Toronto, Ontario, Canada