List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:January 28 2010 1:02pm
Subject:Re: 50 things to know before migrating from Oracle to MySQL
View as plain text  
Yes: YMMV. Caveat emptor. Don't switch to a product you don't know.

If you need nothing that MySQL doesn't offer, it may be a good fit for you.
If you need features that it doesn't offer, it may not be a good fit for
you. News at eleven.

On Thu, Jan 28, 2010 at 11:21 AM, changuno <changuno@stripped> wrote:

> Hi folks,
>
> Read a blog which states 50 things to know before migrating from Oracle to
> MySQL. Any comments on this?
>
> &nbsp;&nbsp; 1. Subqueries are poorly optimized.
> &nbsp;&nbsp; 2. Complex queries are a weak point.
> &nbsp;&nbsp; 3. The query executioner (aka query optimizer / planner) is
> less sophisticated.
> &nbsp;&nbsp; 4. Performance tuning and metrics capabilities are limited.
> &nbsp;&nbsp; 5. There is limited ability to audit.
> &nbsp;&nbsp; 6. Security is unsophisticated, even crude. There are no
> groups or roles, no ability to deny a privilege (you can only grant
> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; privileges). A user who
> logs in with
> the same username and password from different network addresses may be
> treated as a completely separate user. There is no built-in encryption
> comparable to Oracle.
> &nbsp;&nbsp; 7. Authentication is built-in. There is no LDAP, Active
> Directory, or other external authentication capability.
> &nbsp;&nbsp; 8. Clustering is not what you think it is.
> &nbsp;&nbsp; 9. Stored procedures and triggers are limited.
> &nbsp; 10. Vertical scalability is poor.
> &nbsp; 11. There is zero MPP support.
> &nbsp; 12. SMP is supported, but MySQL doesn’t scale well to more than 4
> or
> 8 cores/CPUs.
> &nbsp; 13. There is no fractional-second storage type for times, dates, or
> intervals.
> &nbsp; 14. The language used to write stored procedures, triggers,
> scheduled events, and stored functions is very limited.
> &nbsp; 15. There is no roll-back recovery. There is only roll-forward
> recovery.
> &nbsp; 16. There is no support for snapshots.
> &nbsp; 17. There is no support for database links. There is something
> called the Federated storage engine that acts as a relay by passing queries
> along to a table on a remote server, but it is crude and buggy.
> &nbsp; 18. Data integrity checking is very weak, and even basic integrity
> constraints cannot always be enforced.
> &nbsp; 19. There are very few optimizer hints to tune query execution
> plans.
> &nbsp; 20. There is only one type of join plan: nested-loop. There are no
> sort-merge joins or hash joins.
> &nbsp; 21. Most queries can use only a single index per table; some
> multi-index query plans exist in certain cases, but the cost is usually
> underestimated by the query optimizer, and they are often slower than a
> table scan.
> &nbsp; 22. There are no bitmap indexes. Each storage engine supports
> different types of indexes. Most engines support B-Tree indexes.
> &nbsp; 23. There are fewer and less sophisticated tools for administration.
> &nbsp; 24. There is no IDE and debugger that approaches the level of
> sophistication you may be accustomed to. You’ll probably be writing your
> stored procedures in a text editor and debugging them by adding statements
> that insert rows into a table called debug_log.
> &nbsp; 25. Each table can have a different storage backend (”storage
> engine”).
> &nbsp; 26. Each storage engine can have widely varying behavior, features,
> and properties.
> &nbsp; 27. Foreign keys are not supported in most storage engines.
> &nbsp; 28. The default storage engine is non-transactional and corrupts
> easily.
> &nbsp; 29. Oracle owns InnoDB, the most advanced and popular storage
> engine.
> &nbsp; 30. Certain types of execution plans are only supported in some
> storage engines. Certain types of COUNT() queries execute instantly in some
> storage engines and slowly in others.
> &nbsp; 31. Execution plans are not cached globally, only per-connection.
> &nbsp; 32. Full-text search is limited and only available for
> non-transactional storage backends. Ditto for GIS/spatial types and queries.
> &nbsp; 33. There are no resource controls. A completely unprivileged user
> can effortlessly run the server out of memory and crash it, or use up all
> CPU resources.
> &nbsp; 34. There are no integrated or add-on business intelligence, OLAP
> cube, etc packages.
> &nbsp; 35. There is nothing analogous to Grid Control.
> &nbsp; 36. There is nothing even remotely like RAC. If you are asking “How
> do I build RAC with MySQL,” you are asking the wrong question.
> &nbsp; 37. There are no user-defined types or domains.
> &nbsp; 38. The number of joins per query is limited to 61.
> &nbsp; 39. MySQL supports a smaller subset of SQL syntax. There are no
> recursive queries, common table expressions, or windowing functions. There
> are a few extensions to SQL that are somewhat analogous to MERGE and similar
> features, but are very simplistic in comparison.
> &nbsp; 40. There are no functional columns (e.g. a column whose value is
> calculated as an expression).
> &nbsp; 41. You cannot create an index on an expression, you can only index
> columns.
> &nbsp; 42. There are no materialized views.
> &nbsp; 43. The statistics vary between storage engines and regardless of
> the storage engine, are limited to simple cardinality and rows-in-a-range.
> In other words, statistics on data distribution are limited. There is not
> much control over updating of statistics.
> &nbsp; 44. There is no built-in promotion or failover mechanism.
> &nbsp; 45. Replication is asynchronous and has many limitations and edge
> cases. For example, it is single-threaded, so a powerful slave can find it
> hard to replicate fast enough to keep up with a less powerful master.
> &nbsp; 46. Cluster is not what you think it is. Maybe I already said that,
> but it bears repeating.
> &nbsp; 47. The data dictionary (INFORMATION_SCHEMA) is limited and very
> slow (it can easily crash a busy server).
> &nbsp; 48. There is no online ALTER TABLE.
> &nbsp; 49. There are no sequences.
> &nbsp; 50. DDL such as ALTER TABLE or CREATE TABLE is non-transactional. It
> commits open transactions and cannot be rolled back or crash-recovered.
> Schema is stored in the filesystem independently of the storage engine.
>
> Thanks in advance,
> Chang
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Thread
50 things to know before migrating from Oracle to MySQLchanguno 28 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLJohan De Meersman28 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLShawn Green28 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLpaul rivers28 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLJohn Meyer28 Jan
    • RE: 50 things to know before migrating from Oracle to MySQLDaevid Vincent28 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLCarl28 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLJohnny Withers29 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLfsb29 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLChris W29 Jan
      • Re: 50 things to know before migrating from Oracle to MySQLmos29 Jan
        • Re: 50 things to know before migrating from Oracle to MySQLTom Worster1 Feb
          • Re: 50 things to know before migrating from Oracle to MySQLCarl1 Feb
            • Re: 50 things to know before migrating from Oracle to MySQLMichael Dykman1 Feb
          • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies1 Feb
      • Re: 50 things to know before migrating from Oracle to MySQLRudy Lippan30 Jan
        • Re: 50 things to know before migrating from Oracle to MySQLShawn Green30 Jan
          • Re: 50 things to know before migrating from Oracle to MySQLRudy Lippan31 Jan
        • Re: 50 things to know before migrating from Oracle to MySQLJigal van Hemert30 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies29 Jan
      • Re: 50 things to know before migrating from Oracle to MySQLJørn Dahl-Stamnes29 Jan
      • Re: 50 things to know before migrating from Oracle to MySQLJigal van Hemert30 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies1 Feb
      • Re: 50 things to know before migrating from Oracle to MySQLJigal van Hemert1 Feb
    • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies1 Feb
  • Re: 50 things to know before migrating from Oracle to MySQLJohn G. Heim29 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies29 Jan