15

What are the differences - features and performance - between MySQL and Oracle 11g? I would like to be able to make and educated choice between the two given a situation.

Note: Links to test will obviously help.

Jungle Hunter
  • 7,233
  • 11
  • 42
  • 67

3 Answers3

12

Things that MySQL is still lacking (that Oracle has)

  • deferrable constraints
  • check constraints
  • recursive queries
  • table functions
  • common table expressions
  • windowing functions
  • function based index
  • partial index
  • No MINUS (or INTERSECT) operator
  • very simple query optimizer when it comes to sub-selects
  • A lot of non-standard Syntax (|| is a logical or!)
  • no flashback
6

Very popular answer for StackOverflow:

Look at Wikipedia! :)

Another useful place to look for SQL features comparation:

Comparison of different SQL implementations by Troels.

Hope, you can find answers here ...

ThinkJet
  • 6,725
  • 24
  • 33
2

Apparently, MySQL can tell the difference between an empty VARCHAR and a NULL value :-)

That alone is enough for me to avoid Oracle.

Community
  • 1
  • 1
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • 1
    I always wonder why people need that distinction. There is no such thing as an "empty" integer or an "empty" date or an "empty" decimal value. So why is this needed for strings? And I see stuff like `if (string == null || string.length() == 0)` in a lot of applications all over the places. So apparently in the frontend a null string and an empty string are often treated equally. What I find more annoying is, that this behaviour is not consistent within Oracle. Sometimes it does not treat an empty string as null. –  Feb 10 '11 at 07:48
  • 3
    There is indeed a massive difference. The point of a NULL value is to indicate unknown or not-applicable. There's a difference between someone with no middle name and someone with an unknown middle name. These things may not matter to the DBA wanna-bes of the world but they're very important to those who understand the issues :-) – paxdiablo Feb 10 '11 at 08:24
  • So what's the equivalent of an empty string for an integer value? (And please don't say it's zero) –  Feb 10 '11 at 09:19
  • 1
    Well, there's no equivalent of an empty string for integers. Oracle only has that limitation for character data. By way of example with integers, consider the age of someone. NULL means you don't know, any other value means you do know and it's set to that value. If we extended Oracles limitations to integers, it would _indeed_ not be able to tell the difference between 0 and NULL but, thankfully, Oracle's not quite that bad :-) – paxdiablo Feb 10 '11 at 09:53
  • I still don't see the absolute necessity to have an "empty string" when none of the other datatypes have it. What's so special about strings that they need two different ways of storing the absence of a value - whereas all other datatypes are happily used by everybody with just a single way of storing "nothing" (don't get me wrong: I think Oracle should indeed comply with the standard here, but still I find myself always resetting empty strings to null on other databases) –  Feb 10 '11 at 10:54
  • "none of the other datatypes have it" - where did _that_ come from? I don't know of any other DBMS other than Oracle that treats empty strings as NULLs, all the ones I've used distinguish NULLs from known data (even empty data). – paxdiablo Feb 10 '11 at 12:14
  • 1
    This is a really old debate. The original Oracle approach, which was set long before the current standards, makes a lot of sense for form based applications. If someone enters nothing into a form field, it makes more sense to have it as a NULL rather than tons of empty strings making your queries more complex. I've read the reason there is a VARCHAR and VARCHAR2 type is because VARCHAR2 is guaranteed to keep this behavior while VARCHAR may change to the current standard behavior in the future. – JOTN Feb 10 '11 at 23:17