Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been developing an application against MySQL while a new development server has been slowly being set up. The new dev server will use oracle, and since we're getting close to it being ready, I want to remove the auto_increment attribute from my primary keys, and simulate Oracle's sequences. I haven't used oracle for several years.

Since every pre-insert trigger is going to do the same thing - get the next id for table foo out of *foo_sequence*, does Oracle make any allowances for implementing some form of dynamic/lexical triggering ?

Any tips, tricks or gotchas?

EDIT #1: 1550 - this is not a heavy-use app. a single use will cause 13 inserts of small, simple records in the biggest transaction. Concurrency is unknown, but the likely hood of one user causing lock starvation of any significance is estimates very low IMO.

EDIT #2 1612: the intent is to simulate oracle sequences in mysql, and then use oracle sequences on oracle.

Thanks.

Resolution:

  • I'm implementing a class to act as my interface to the sequence.
    • on mysql, I'm implementing a table to hold sequence values, and a stored procedure to get the next value and update the row. The class will call this stored proc and return the value.
    • on oracle I'll just get the next value from an oracle sequence. The class will make this request. The Sequence class will request the next value from the sequence.
  • the application code will use the Sequence class to obtain the next sequence value for the insert, abstracting the "nextval" implementation details away from the insert statement that consumes the value.
share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

The 'gotcha' is that in nearly every case you really don't want to take the performance and maintenance hit for triggers just to hide the pk insert.

You really should write the queries to insert sequence.nextval with the INSERT. Do it during the migration before you regret it a few years from now.

share|improve this answer
    
So your suggestion is to put the sequence manipulation at the application level instead of hiding the DB specific ID/sequence manipulation inside the database? –  Len Jaffe Jul 30 '12 at 19:45
    
The suggestion is to put it where you have your SQL - whether thats the application or stored procs or whatnot. Putting nextval directly in the SQL is common for oracle. –  rfusca Jul 30 '12 at 19:48
    
I really do not like the idea of domain logic having to manipulate database structure that are pure DB infrastructure. I understand why one would want to do it that way, and we used to do it that way when we didn't have stored procs and triggers (Unify RDBMS with Accell/4GL circa 1989). I am slightly bummed about the idea that I'll need a trigger per table, instead of having a lexically defined trigger, but I'm a firm believer that separating the non-domain PK form the domain data will make the application code cleaner and more maintainable. Thank you for taking the time to answer. –  Len Jaffe Jul 30 '12 at 20:01
    
@Len - Just to let you know the performance hit you're taking - hoopercharles.wordpress.com/2011/03/25/… (not my site, just a blogger who compared the two) –  rfusca Jul 30 '12 at 20:43
    
@Len putting the sequence.nextval directly in the INSERT is definitely not 1989 Oracle - its the standard way of doing it, even today. –  rfusca Jul 30 '12 at 21:17
show 4 more comments

There was a past post on how someone jerryrigged a sequence paradigm for a MySQL app because of MySQL lacking sequence objects.

I answered that question back on March 16, 2012.

Think about it from two perspectives

PERSPECTIVE #1

If you have any special tables designed around sequences that would be rendered obsolete by using Oracle Sequences, you do not need to migrate those tables. Your codebase would have to change to replace updateing your sequence table with summoning NEXTVAL from an Oracle Sequence.

PERSPECTIVE #2

If you wish to keep special tables designed around sequences you control, your code would not need to change, but how will Oracle perform? IMHO Using an Oracle Sequence is like using a MyISAM table: A full lock is issued to increment and return a value. Is your method better that an Oracle Sequence? You will have to benchmark this. The results will determine whether you keep your codebase or replace all sequencing code.

EPILOGUE

Whatever you decide you will have to live with. I say this because your sequencing code may be great today with Oracle 11g only to be outpaced by the latest upgrade of Oracle or by the amount of increasing data traffic imposed on the database.

share|improve this answer
    
My plan is to simulate the O sequence now, and use O sequences when we migrate to oracle. Thanks for taking the time to answer. –  Len Jaffe Jul 30 '12 at 19:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.