Monday, July 13, 2009

Moving from MySQL to an Oracle Database Server

I was asked recently in one of my blogs what are some of the pure technical advantages of moving from MySQL to Oracle. I kind of laughed when I read the question because nothing is likely to stir up more perspectives and emotions than asking about moving from one database server vendor to another. So here goes ten technical considerations of moving to an Oracle database server from a MySQL server. I'm keeping the list to pure database server technology. There are also a lot of reasons to move to MySQL as well from other database server vendors (my whole blog is on this).

Technical Advantages moving from a MySQL Server to an Oracle database server
  1. Scalability: Much higher vertical scalability and higher OLTP transaction capability. Ability to set up shared server architecture for more user connection scalability.
  2. Metadata: Lots of very detailed metadata information as well as wait events for tuning.
  3. Stored Routines: Compiled stored procedures with much higher scalability. Java and .NT stored procedure capability.
  4. SQL functionality: Lots of SQL functions and analytical SQL functions. Lots of XML functionality built into database server.
  5. Backups: Hot online backups with Oracle's Recovery Manager
  6. Automatic Management: Lots of automatic management with Automatic Database Diagnostic Management (ADDM), Advisors, Automatic Workload Repository (AWR) , Automatic Segment Management, Shared Memory Management, etc.
  7. Data Warehousing: Bitmap indexes, materialized views, analytical functions, star joins, sort-merge joins, hash joins, etc.
  8. Grid Control: Oracle Enterprise Manager is a very robust enterprise tool for managing and monitoring multiple tiers of an Oracle infrastructure.
  9. Inexpensive disk strategy: Striping and mirroring capability with Oracle's Automatic Storage Management (ASM).
  10. Flashback technology: Lots of recovery capability with Flashback technology such as Flashback Database, Flashback Table, Flashback Versions Query, etc.

Anyway, a few things to talk about over coffee and cookies. :)

2 comments:

Morgan said...

George,

I thought the list was pretty fair. Two more came to mind:

1) GIS functions are weak in MySQL.
2) Integrity features (check constraints, the foreign keys implementation in InnoDB is average at best).

Stefan Lasiewski said...

Nice article.

Can you provide another list of technical disadvantages when moving from MySQL to Oracle? I'm sure there are a few disadvantages ;)