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.

task: replace Oracle 11g with MySQL

current setup:

  • Oracle 11g SE One
  • about 80 tables
  • stored procedures, PL/SQL are in place but out of scope for this question
  • client applications do not use a generic access technique like JDBC but OCI libs
  • applications have roughly 500 fixed SQL queries for Oracle

Management now wants to have outlined a migration draft. Don't bother about the schema or data migration.

I'm trying to figure out a rough estimate of time to spend for translating 500 queries. Naturally these queries range from very simple INSERTs/SELECTs to rather complex ones with subqueries, multiple JOINs and expressions like that:

TRUNC(EXTRACT(DAY FROM (afs.next - TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC',
 'YYYY-MM-DD HH24:MI:SS TZR'))) * 86400 + EXTRACT(HOUR FROM (afs.next - 
TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR'))) 
* 3600 + EXTRACT(MINUTE FROM (afs.next - TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC',
 'YYYY-MM-DD HH24:MI:SS TZR'))) * 60 + EXTRACT(SECOND FROM (afs.next - 
TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')))) as \"next\"

Hint: sense and purpose of such expressions has to be read out of the existing queries because at creation time no one bothered about documenting details of the implementation.

I'm completely aware that there may be queries that cannot be translated as is because MySQL perhaps does not support some things.

Question: can you give a rough hint about the expected "throughput" you think an expert could be capable of? The expert will be a person skilled with MySQL, Oracle and the applications. What do you think how much time is required to "translate" a single average query and simply manually test it in the target database?

Please ignore the required testing of the whole application. Don't regard that we have to setup the target schema beforehand. Hints to and stories of failed migrations and disastrous problems are welcome.

Thanks!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Why are you migrating to MySQL? Did you consider PostgreSQL? I only ask because PostgreSQL is pretty much feature-compatible with Oracle, and would make a migration an order of magnitude simpler.

That said, translating 80 table definitions and 500 queries will probably take a month or so. Simple CRUD statements will be limited to the expert's typing speed, but if you're doing any aggregation or other BI-type work it can take a day just to get one statement working. You might be better off simply documenting every query then re-writing them from scratch using MySQL idioms rather than trying to translate them.

share|improve this answer
    
MySQL was set by management, Postgres was checked off a few years ago when some project failed to use it correctly. There is a load of simple queries but I fear some of these one-day-effort monsters also. Thanks for your input. –  mdo Jul 23 '12 at 18:35

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.