ETA: translate Oracle SQL queries to MySQL

Question

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!

Answer 1

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.