When to choose Oracle over MySQL?

Question

I am a MySQL fan , however i want to know in which situations choosing Oracle over MySQL seems like the way to go .. like what would be the indicators which would make you say .. "I need Oracle for this project"

Update: As another fellow SOer pointed out, dont limit your answers to Oracle ... if you know of something better , please point that out too.

Answer 1

There are certain things that bother me with Mysql

I have to choose between enforcing contraints and transactions vs Fulltext Index (InnoDb vs MyISAM). This is really the problem number 1 for me (enforcing contraints and transactions is what makes dbs cool, but you need the fulltext search too...)

• It is not easy to "simulate" transactions in client code.
• If you don't enforce the constraints it is really easy to get inconsistent state of the db
• Without Fulltext Search you might get crazy with OR X LIKE %y%
• You have to create BEFORE UPDATE TRIGGER with RAISE ERROR for CHECK CONSTRAINT
• Mysql has bad performace when data becomes too large(I mean really large).
• Mysql creates poor execution plans
• Mysql has problems with more than 3 joins(let's better say multiple joins).

Oracle is the solution for all these problems, it is a full DBMS (transactions, CHECK contraints, a lot of options for views, fulltext search and much more.. ) but after all it is a matter of money.

Answer 2

Good article why we migrate from mysql to oracle. http://www.iheavy.com/node/51

Answer 3

Maybe it's worth considering postgresql, which is also free (BSD instead of gpl).

For a nice review of choices, have a look at Wikipedia using "Comparison of relational database management systems".

Answer 4

Analytics ... do you need any other reasons ? ;)

Analytics are a blessing for all reports and datawharehouses. But then, if you just need a little database for a website, stick with MySQL, otherwise, if you need complicated reporting and performance is an issue, then i believe Oracle is the way to go.

Answer 5

Oracle bring a huge set of functionality with it, some of it optional extras that you pay for but much of it included for free in every edition.

http://www.oracle.com/database/product%5Feditions.html

Flashback query is a free include, and lets you query the database "as of" some time in the recent past.

Expression filters are another good example of a freebee, and the Rules engine is also powerful. http://www.oracle.com/technology/products/database/rules%5Fmanager/index.html

So when you make your choice you ought to consider what features you can leverage that will get you functionality that you would otherwise have to develop and support yourself.

Answer 6

I'm an Oracle guy myself, yet sometimes I find it hard to argue its use over PostgeSQL or even MySQL.
The short story is that there are companies/projects out there that manage huge amounts of data using some open source RDBMS.

If you are to come down to the features, that's another thing, but how can you argue against the success of those companies? True, they use a lot of boxes for achieving this, but it's still much cheaper. I doubt that most companies that use Oracle nowadays really need it, but there are certainly companies that need Oracle.

A nice quote:

"Move cpu-intensive work moved out of the database layer to applications applications layer: referential integrity, joins, sorting done in the application layer! Reasoning: app servers are cheap, databases are the bottleneck."

See here for lots of real world practice. And yes, they use Oracle.

And yes, I still love Oracle, it's what a DBMS done right should look like, but that doesn't mean its place is everywhere, at least not at the price it sells for.

Answer 7

Oracle offers a highly sophisticated, and complex, scheme for backing up and restoring live data, giving it true non-stop hot backup capability.

It offers lots of analytical and statistical functions built in for aggregating and summarizing data. You can do this stuff with mySQL, but in your app.

It has excellent scalability, if you need to run a large transactional system with transactional integrity (commit / rollback). If you're willing to spend a lot of money, it scales out with Real Application Clusters.

It offers schemes for handling embedded code (packages, stored procedures, stored functions) that scale up well to large quantities of code and complex schemas.

It does a good job with high transaction rates (tens of thousands per hour), especially when you use bound variable queries (PreparedStatement objects in JDBC). Most importantly, its performance for this sort of thing is predictable.

It's very expensive and requires a krewe of highly trained acolytes to keep it working properly. The good news is there are lots of expert people out there. The place I work spends money with an outfit called Pythian Group to look after our Oracle, which is a good way to go.

You can evaluate it with the Express Edition (free, limited to a couple of gigabytes of data).

If your system can work with Oracle Standard Edition, then use mySQL instead. If your system requires the Enterprise Edition features, then you should also evaluate IBM DB2, as both of these are designed for scaleout.

Answer 8

There are a variety of things that Oracle does very well, perhaps better than any other rdbms (I don't know DB2 well enough to leave that unqualified). The clustering (RAC) is very, very good. The stored procedure language, PL/SQL, is very solid and actually enjoyable to code in. There is good XML functionality, GIS, full-text, etc.

But the absolute deal breaker for me is that the optimizer works well. You give it a query and it returns you a result set in an efficient manner. Occasionally you know some detail that it does not and have to provide a hint accordingly, but that is the rare exception. You issue SELECT, INSERT, UPDATE and DELETE statements and the database works the way it ought to.

Answer 9

I know nothing about Oracle, but since the question was expanded to include, say, Postgres...

Some things I've personally used or seen used in Postgres that don't really exist in MySQL (AFAICT)!

• transactions and full-text search together

I know it's popular to use an external FTS since MySQL doesn't have one. Personally, I've had nothing but trouble using separate FTS solutions: if it's possible for the two data sources to get out of sync, I can guarantee that at some point they will. I could use BDB and write my own indices, too, but I don't, because I see no way in which it's better than a built-in index, and a bunch of ways in which it's worse. (OK, in one case I needed a weird custom index, and for that it's nice. If you need a weird custom FTS, then maybe Sphinx is more flexible. But I've never seen a real need for a weird custom FTS, and I'm not even sure that Sphinx is more flexible than Postgres FTS.)

• spatial queries (PostGIS)

I don't know if MySQL has an extension mechanism that allows this, but I'm pretty sure it doesn't have an extension like PostGIS. Let's say you want to query for all coffee shops within 300 meters of a park, and not within 100 meters of a landfill (given that your database has the boundaries of parks, landfills, and coffee shops). It's impressively easy with PostGIS. With MySQL, I think it would probably be a decent amount of work.

• object-relational tables

Rails people (hey, I used to be one!), especially, like to use STI and pretend that all subclasses have pretty much the same fields as the superclass. It's OK if you only have a couple subclasses or they're all very similar, but trying to map a class hierarchy to tables can get pretty crazy pretty quick. In Postgres, it's easy: make a new table that inherits from the first one, and adds its fields, just like subclassing in your programming language. A data model that actually matches my data! Not as nice as a real OODB but pretty friggin' close.

• better transactions

I know if you stick with InnoDB you get transactions for all data manipulation operations. Postgres also has transactions for all data definition operations. Take a common case: I need a migration to add a column, convert data from the old format to the new format, and remove an old column. In Postgres, I just do it all in one transaction, and there's no chance I'll end up with this transaction partially applied. In MySQL, it can do the data conversion step in a transaction, sure, but if it has to rollback, the new column has still been added, so you need to clean it up by hand, or write a more complex transaction to deal with that (and even then it's still not atomic in the db). Repeat every day, and enjoy the pain.

(The general theme I see here is the ability to say just what I mean, and therefore work at a higher level of abstraction. Want FTS on my data? Then create a FTS index. Want a spatial query? Then write a spatial query. Want to store subclass data? Then make a subclass table. Want a migration that's completely atomic? Then slap a transaction around it and call it a day. Sure, I can implement any of these in MySQL, but then I'm having to think about and implement and maintain that other thing, rather than just writing a single line of SQL. As a professional programmer there's nothing more valuable to me than being able to work at a higher level of abstraction, full stop.)

Now, I'm not sure if I'd say Postgres is generally "better" than MySQL -- there are certainly things that MySQL does much better and so it certainly has its uses -- but these are a few things I absolutely love about it.