2

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.
Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
9
  • 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
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 at 21:17
1

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.

Improve this answer
1
  • 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
    Commented Jul 30, 2012 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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