Thursday, April 30. 2009
Taking a stab at "50" things to know before migrating MySQL to Oracle
Robert Hodges has brought up the idea of MySQL->Oracle culture shock today; it's something I have run into. In my opinion, much of that gap is the difference between open source culture and closed source culture; Oracle DBA's often feel strongly that a single steward of the code is the right way to develop features, and are uncomfortable in the more "wild-west" environment that grows up around many open source projects. This is somewhat counter to the more fast and loose way of things common in todays Open Source driven communities.
Anyway, Robert also mentioned that he thought someone should come up with a MySQL->Oracle migration list, similar to Baron's excellent Oracle->MySQL migration list. Well, I don't claim to be a MySQL to Oracle migration expert (we're more likely to go the other way, and even more likely to go Oracle to Postgres), but if you want to be a DBA at OmniTI (we're hiring btw), you'll need to get familiar with Oracle and MySQL in heterogenous environments; so I figured I could take a stab at this.
Anyway, Robert also mentioned that he thought someone should come up with a MySQL->Oracle migration list, similar to Baron's excellent Oracle->MySQL migration list. Well, I don't claim to be a MySQL to Oracle migration expert (we're more likely to go the other way, and even more likely to go Oracle to Postgres), but if you want to be a DBA at OmniTI (we're hiring btw), you'll need to get familiar with Oracle and MySQL in heterogenous environments; so I figured I could take a stab at this.
Turns out it's hard. 
I didn't make it to 50; I barely made it past 25, so I cheated and asked around a little, and even then it was hard. I think you could easily get there if you just wanted to list syntactical differences, but in keeping with the spirit of the original article, I tried to focus on things that will cause the MySQL DBA to have to think differently about problems, and not because there is any benefit. For example, MySQL support a horrible non-standard quoting syntax; if you move to Oracle, you'll have to get used to standard quoting syntax. This might be annoying at first, but it's not terribly different; you just need to get used to it, and imho in the end you'll be better for it. I wanted to list things that would require a new / different approach to problems.
So with that, here's a list of 29 things you should know. If you've gone through some large scale MySQL->Oracle migrations, please post additional items in the comments.
I hope this is helpful, and I look forward to seeing other expand on the topic.
I didn't make it to 50; I barely made it past 25, so I cheated and asked around a little, and even then it was hard. I think you could easily get there if you just wanted to list syntactical differences, but in keeping with the spirit of the original article, I tried to focus on things that will cause the MySQL DBA to have to think differently about problems, and not because there is any benefit. For example, MySQL support a horrible non-standard quoting syntax; if you move to Oracle, you'll have to get used to standard quoting syntax. This might be annoying at first, but it's not terribly different; you just need to get used to it, and imho in the end you'll be better for it. I wanted to list things that would require a new / different approach to problems. So with that, here's a list of 29 things you should know. If you've gone through some large scale MySQL->Oracle migrations, please post additional items in the comments.
- Some say Oracle has no limits, what it really has is no LIMIT clause. You can use rownum(), but it's more cumbersome
- Oracle also doesn't have an offset clause.
- Oracle replication may be more robust than MySQL, but it is also more complex to set up.
- And for multiple master type systems, most people will want you to use RAC, which is complex, and expensive (though probably more robust than NDB)
- Partitionng support also very robust in Oracle, but it's not built in, you'll need to pay for it.
- Driver support on Unix/Linux will be less likely to work out of the box than with MySQL.
- INSERT ... ON DUPLICATE KEY UPDATE syntax will not work, you'll need to use the more complicated (but standard) MERGE syntax.
- The ROLE structure is fairly different under Oracle; you can't just do everything as root.
- Role accounts are tied to specific schemas (and vice versa), which are generally thought of as databases in the MySQL world.
- In fact, forget everything you knew about setting up connection access, Oracle uses a completly seperate system.
- Fulltext search is supported, but the syntax is rather different.
- Oracle is rich in documentation but if you want support from oracle professionals you need Metalink access.
- There is far less support and information available from non-database sources (like PHP Forums or Web Developer blogs).
- Autoincrement functionality is implemented via Triggers and Sequences.
- Many SHOW commands you are used to will not work; to get system information, you will want to learn the Oracle data dictionary or the Information Schema.
- MySQL adds several non-standard extension to information schema; that information will be harder to find under Oracle.
- You will need to become very familiar with Oracle licensing in order to manage roll-out of both production and non-production systems.
- Oracle's numeric data type support is simpilar in general, but if you really need the granularity of MySQL's multiple number types, you'll have to reimplement them as domains, or with different specified NUMBER(n,x) options.
- It is possible to set up Oracle to allow unbounded growth in your tables, but a lot of sources will push you toward fine grain management via tablespaces.
- Oracle doesn't support ALTER TABLE ADD COLUMN BEFORE|AFTER.
- If you like managing databases via GUI's, you'll love Oracle, but if you tend to use mysql command line client, you will likely be disapointed with sql*plus.
- Oracle requires tighter data correctness, which can lead to broken applications that rely on MySQL's fuzziness. Accepting '0000-00-00' for a date is the classic example.
- While the capabilities are more advanced, Oracle doesn't provide a standard PSM language for writing procedures, so you'll have to learn its non-standard versions.
- There is so support for the ENUM type; You will need to either use text with check constraints, or create FK lookup tables.
- Some of the more esoteric table types in MySQL (blackhole, csv) do not have equivilant functionality in Oracle.
- GROUP BY statements are required to be deterministic in Oracle, so it may require all columns listed in the SELECT clause to be included in the GROUP BY clause.
- Oracle export dumps cannot be hand modified like MySQL dumps.
- The underlying implementation has more moving parts; you'll need to learn about UNDO & REDO segments, ARCHIVE, DBWR processes
- Oracle is not open source, so you can't tinker/tune/fix/implement things on your own.
I hope this is helpful, and I look forward to seeing other expand on the topic.
Comments
Display comments as
(Linear | Threaded)
Some corrections:
3. Advanced replication is easier to setup. Streams is harder (but not by much).
4. Avdvanced Replication and Streams both do multi-master and are part of your license.
5. Partitioning IS built-in but it will still cost you.
6. Doubtful. Oracle is built on Unix/Linux. Sounds like FUD.
9. Job roles, such as sysdba, are tied to specific accounts. Roles (which are similar to users) are not. Just wanted to clarify.
13. Oracle has the richest environment of blogs and forums of any database. Bar none.
15. Oracle doesn't have an information schema. I am creating one as an open source project though.
25. Does anyone actually use blackhole? Oracle does have external tables which are sweet.
29. I tinker,tune, and tweak on my own all the time!
Good list.
LewisC
3. Advanced replication is easier to setup. Streams is harder (but not by much).
4. Avdvanced Replication and Streams both do multi-master and are part of your license.
5. Partitioning IS built-in but it will still cost you.
6. Doubtful. Oracle is built on Unix/Linux. Sounds like FUD.
9. Job roles, such as sysdba, are tied to specific accounts. Roles (which are similar to users) are not. Just wanted to clarify.
13. Oracle has the richest environment of blogs and forums of any database. Bar none.
15. Oracle doesn't have an information schema. I am creating one as an open source project though.
25. Does anyone actually use blackhole? Oracle does have external tables which are sweet.
29. I tinker,tune, and tweak on my own all the time!
Good list.
LewisC
The example of usage of Blackhole engine that most readily comes to mind is sort of a hack to perform master side binlog filtering using a mysqld as a log proxy.
Say you have A->B->C Server A has multiple databases, and C wants to be a slave of one of them. Under regular replication the master has no way to limit which log records are allowed to be sent to which slave. The slave then must transfer the entire log with all updates from all databases. It then must be trusted to filter out what it is supposed to have access to. B can be setup to read the log from the master, load those through a blackhole engine and log-slave-updates so the server C only gets access to those updates.
It can be used also to merge the logs of two servers into one source into a multi-master-esque replication setup.
Say you have A->B->C again but this time there is a database on A and a database on B that C wants. You do the same operation, binglog read, update blackhole and log-slave-updates on B and C will get the updates to both instances. This works even if it is two copies of the same database on A and B. You just need to use --replicate-rewrite-db= to map the database name as it passes through B to another name so its updates can be sent to the blackhole. and C rewrites it back to the original name.
Say you have A->B->C Server A has multiple databases, and C wants to be a slave of one of them. Under regular replication the master has no way to limit which log records are allowed to be sent to which slave. The slave then must transfer the entire log with all updates from all databases. It then must be trusted to filter out what it is supposed to have access to. B can be setup to read the log from the master, load those through a blackhole engine and log-slave-updates so the server C only gets access to those updates.
It can be used also to merge the logs of two servers into one source into a multi-master-esque replication setup.
Say you have A->B->C again but this time there is a database on A and a database on B that C wants. You do the same operation, binglog read, update blackhole and log-slave-updates on B and C will get the updates to both instances. This works even if it is two copies of the same database on A and B. You just need to use --replicate-rewrite-db= to map the database name as it passes through B to another name so its updates can be sent to the blackhole. and C rewrites it back to the original name.
Thanks. Very interesting stuff. I haven't been involved in a setup like that.
LewisC
LewisC
WRT #6, It does sound like FUD doesn't it? But what I am referring to is not the ability to run Oracle, but the likely hood that some 3rd party item will work easily with Oracle. For example, looking on my ubuntu at php pdo packages, I have postgres, mysql, sqlite, sybase, and odbc. Now odbc will probably work, but wouldn't an oci be better? If you've been running MySQL for a long time, it can be a culture shock when you get to Oracle and there isn't a package for everything.
WRT #13, Oracles blogs/forums are very insular. MySQL get's tons of support in non-mysql arenas. I often see chatter in php forums giving mysql support. RoR is another environment is another where database support = mysql support more often than not. While it's nice to be able to go to a community a get good support (go postgres!) it's also very nice to be able to find users of your database in other communities.
WRT #13, Oracles blogs/forums are very insular. MySQL get's tons of support in non-mysql arenas. I often see chatter in php forums giving mysql support. RoR is another environment is another where database support = mysql support more often than not. While it's nice to be able to go to a community a get good support (go postgres!) it's also very nice to be able to find users of your database in other communities.
Hi Robert--thanks for starting the list!
My additions:
30. Oracle query tuning is a job for experts. The optimizer is far more capable than MySQL, which means query plans are correspondingly harder to interprete. If you have a large application, plan on hiring somebody who knows how to do this productively.
31. The Oracle cost-based query optimizer needs accurate statistics to function correctly. For tables that change, you need to rerun statistics regularly. Batch load processes often need to estimate/compute statistics at intervals during processing to get adequate performance.
32. Creating a database connection in Oracle is a heavy-weight operation. Well-behaved Oracle applications tend to use connection pools in order to minimize logins.
33. Oracle applications must use prepared statements to get reasonable performance. If your applications don't use prepared statements, you need to change them. This is standard programming practice on Oracle.
34. Applications need to close result sets or they will end up (quickly) with the infamous "ORA-01000 Too many open cursors" error. This is considered a user error in Oracle and you need to fix your code.
35. Long queries on busy databases are subject to "ORA-01555 Snapshot too old" errors. This can be cured by tuning redo segment size (and sometimes application changes) but you need to look out for it.
36. There's no such thing as non-transactional table types in Oracle. Most Oracle users think this is a good thing.
37. Oracle temporary table definitions are permanent SQL objects that are visible to all users. This is different from the lightweight tables you may be used to in MySQL that are created and discarded within a single user session.
Some of these could a little out of date (in particular I think #31 is helped by better management in newer releases).
My additions:
30. Oracle query tuning is a job for experts. The optimizer is far more capable than MySQL, which means query plans are correspondingly harder to interprete. If you have a large application, plan on hiring somebody who knows how to do this productively.
31. The Oracle cost-based query optimizer needs accurate statistics to function correctly. For tables that change, you need to rerun statistics regularly. Batch load processes often need to estimate/compute statistics at intervals during processing to get adequate performance.
32. Creating a database connection in Oracle is a heavy-weight operation. Well-behaved Oracle applications tend to use connection pools in order to minimize logins.
33. Oracle applications must use prepared statements to get reasonable performance. If your applications don't use prepared statements, you need to change them. This is standard programming practice on Oracle.
34. Applications need to close result sets or they will end up (quickly) with the infamous "ORA-01000 Too many open cursors" error. This is considered a user error in Oracle and you need to fix your code.
35. Long queries on busy databases are subject to "ORA-01555 Snapshot too old" errors. This can be cured by tuning redo segment size (and sometimes application changes) but you need to look out for it.
36. There's no such thing as non-transactional table types in Oracle. Most Oracle users think this is a good thing.
37. Oracle temporary table definitions are permanent SQL objects that are visible to all users. This is different from the lightweight tables you may be used to in MySQL that are created and discarded within a single user session.
Some of these could a little out of date (in particular I think #31 is helped by better management in newer releases).
Good List!
My Additions..
38. In oracle, multiple ALTER TABLE operations does not work in same statement i.e alter table emp modify name varchar(64) not null, add gender char(1) not null;
39. By default, Oracle does not auto commit.
40. KILL command does not work in oracle. it uses Alter System Kill.
41. oracle does not support minus sign(-) in ORDER BY clause
42. Oracle SQLPLUS command line interface does not come with Redline support
My Additions..
38. In oracle, multiple ALTER TABLE operations does not work in same statement i.e alter table emp modify name varchar(64) not null, add gender char(1) not null;
39. By default, Oracle does not auto commit.
40. KILL command does not work in oracle. it uses Alter System Kill.
41. oracle does not support minus sign(-) in ORDER BY clause
42. Oracle SQLPLUS command line interface does not come with Redline support
Hi!
nice list. Few additions:
43) Oracle does not support GROUP_CONCAT or anything like that. You'll have to roll your own using either a cursor, or you might be able to achieve the result you need by combining XMLAGG and XMLQUERY
44) Oracle supports only one expression for COUNT(DISTINCT exp), whereas MySQL supports a list of expressions
45) Oracle's support for subqueries is pretty good - don't shun them out of habit
46) Oracle does not support user variables (@num). If you are using those to calculate running totals, take a look at what oracle calls "analytic functions" aka window functions. If you are using user variables to implement certain aggregate functions, you may be lucky and find that Oracle supports it natively
47) Oracle does not have separate TIME and DATE datatypes, and Oracle DATE is effectively a DATETIME (but with a much larger range than what is supported in MySQL)
48) Oracle stored procedure and trigger features and performance is really good sa compared to MySQL. Don't shun them out of habit
49) If you do need to write stored procedures for oracle, take some time to look into the built-in packages before you start coding. You will find a lot of problems have been solved for you, or at least many building blocks are already there.
50) If you are using BLOB or TEXT types in MySQL you will probably be migratng them to BLOB and CLOB in Oracle. However, unlike MySQL Oracle does not transparently materialize the data. In many cases this is actually a good thing, but it does mean that if you simply want to treat your BLOB/CLOB data as text, you are in for a lot of fiddling with LOB functions which may be a bit discouraging the first few times.
nice list. Few additions:
43) Oracle does not support GROUP_CONCAT or anything like that. You'll have to roll your own using either a cursor, or you might be able to achieve the result you need by combining XMLAGG and XMLQUERY
44) Oracle supports only one expression for COUNT(DISTINCT exp), whereas MySQL supports a list of expressions
45) Oracle's support for subqueries is pretty good - don't shun them out of habit
46) Oracle does not support user variables (@num). If you are using those to calculate running totals, take a look at what oracle calls "analytic functions" aka window functions. If you are using user variables to implement certain aggregate functions, you may be lucky and find that Oracle supports it natively
47) Oracle does not have separate TIME and DATE datatypes, and Oracle DATE is effectively a DATETIME (but with a much larger range than what is supported in MySQL)
48) Oracle stored procedure and trigger features and performance is really good sa compared to MySQL. Don't shun them out of habit
49) If you do need to write stored procedures for oracle, take some time to look into the built-in packages before you start coding. You will find a lot of problems have been solved for you, or at least many building blocks are already there.
50) If you are using BLOB or TEXT types in MySQL you will probably be migratng them to BLOB and CLOB in Oracle. However, unlike MySQL Oracle does not transparently materialize the data. In many cases this is actually a good thing, but it does mean that if you simply want to treat your BLOB/CLOB data as text, you are in for a lot of fiddling with LOB functions which may be a bit discouraging the first few times.
I find this a strange list. From the title I tend to read that it's bad to migrate to Oracle, I also read this from some comments.
However, not all items listen show a bad side of migrating.
So, what is this article really trying to say???
-) Just a list of differences between the two systems?
-) Try to say why MySQL is better?? (It really isn't for some applications).
RIes
However, not all items listen show a bad side of migrating.
So, what is this article really trying to say???
-) Just a list of differences between the two systems?
-) Try to say why MySQL is better?? (It really isn't for some applications).
RIes
Quicksearch
This is the weblog of Robert Treat. I lead the Database Operations Group at OmniTI, where we work on some of todays largest database challenges.
bio | writings
Hire me! Need help with your database? We are available for large scale or short term engagements.
Hire you! If you have experience with Postgres, MySQL, or Oracle, we are looking for people to join our team.
bio | writings
Hire me! Need help with your database? We are available for large scale or short term engagements.
Hire you! If you have experience with Postgres, MySQL, or Oracle, we are looking for people to join our team.
| 
| 
| 
| 
| 
Recent Musings
You were saying?
about typically I recommend you disable SELinux...
Thu, 31.12.2009 14:06
yum install php-pgsql; should work on most distros that use yum.
Thu, 31.12.2009 14:06
yum install php-pgsql; should work on most distros that use yum.
about typically I recommend you disable SELinux...
Sun, 27.12.2009 23:24
What command did you mean when you wrote: "Step one was inst alling PostgreSQL support into PHP, which was a simple [...]
Sun, 27.12.2009 23:24
What command did you mean when you wrote: "Step one was inst alling PostgreSQL support into PHP, which was a simple [...]
about LISA 2009 Wrap-up
Mon, 09.11.2009 12:26
I did field a couple of "why s hould I use Postgres instead o f MySQL?" questions, but they were in a different form [...]
Mon, 09.11.2009 12:26
I did field a couple of "why s hould I use Postgres instead o f MySQL?" questions, but they were in a different form [...]
Syndicate This Blog
Powered by
Welcome to the 145th edition of Log Buffer, the weekly review of database blogs. MySQL Since MySQL was surely the belle of the bloggers’ ball this week—why, everyone was talking—let’s begin with it. Baron Schwartz started someth...
Tracked: May 08, 16:17