Hello -
I was wondering if others have had to deal with an Oracle to
MySQL migration and how you handled the implementation equivalent
of Oracle sequences in MySQL.
Our application uses a bunch of Oracle sequences to keep ID
uniqueness for each sequence type. For example, we have:
UserIDSequence
NodeIDSequence
etc.
When we create new records, we first ask the Oracle sequences
for all new IDs. Then we generate a bunch of insert statements
and ultimately insert the new records.
We've thought of 3 possible solutions from easiest to difficult
based on our source semantics and amount of work. I would
appreciate any helpful insights that others might have.
We are using MySQL 4.1.5-Gamma with the InnoDB engine.
1- Create a single table with a single auto-increment column
to hold a system-wide unique ID. Every time we want a unique
ID, we insert in this table and get the value with the
LAST_INSERT_ID() function.
This approach seems to create a bottleneck at this single
table.
2- Create a single table with 2 columns: sequencename, counter.
Every time we want a unique ID for a particular sequence, we
increment the counter and get it back immediately.
This approach seems to create a bottleneck also. But I would
imagine this approach is more costly than solution #1.
3- Redo our semantics by replacing our insert statements and
allowing AUTO_INCREMENTed columns to keep the IDs unique.
Thanks for you feedback.
-ken
Kenneth Lim
Software Engineer
Senvid, Inc.
2445 Faber Place, Suite #200
Palo Alto, CA 94303
phone: 650-354-3612
fax: 650-354-8890
email: [EMAIL PROTECTED]
http://www.senvid.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
