50 things to know before migrating Oracle to MySQL

1. Subqueries are poorly optimized.
2. Complex queries are a weak point.
3. The query executioner (aka query optimizer / planner) is less sophisticated.
4. Performance tuning and metrics capabilities are limited.
5. There is limited ability to audit.
6. Security is unsophisticated, even crude. There are no groups or roles, no ability to deny a privilege (you can only grant 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.
7. Authentication is built-in. There is no LDAP, Active Directory, or other external authentication capability.
8. Clustering is not what you think it is.
9. Stored procedures and triggers are limited.
10. Vertical scalability is poor. No longer true; with Percona XtraDB or MySQL 5.5 (unreleased) you get excellent scalability
11. There is zero MPP support.
12. SMP is supported, but MySQL doesn’t scale well to more than 4 or 8 cores/CPUs. No longer true; with Percona XtraDB or MySQL 5.5 (unreleased) you get excellent scalability
13. There is no fractional-second storage type for times, dates, or intervals.
14. The language used to write stored procedures, triggers, scheduled events, and stored functions is very limited.
15. There is no roll-back recovery. There is only roll-forward recovery.
16. There is no support for snapshots.
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.
18. Data integrity checking is very weak, and even basic integrity constraints cannot always be enforced.
19. There are very few optimizer hints to tune query execution plans.
20. There is only one type of join plan: nested-loop. There are no sort-merge joins or hash joins.
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.
22. There are no bitmap indexes. Each storage engine supports different types of indexes. Most engines support B-Tree indexes.
23. There are fewer and less sophisticated tools for administration.
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.
25. Each table can have a different storage backend (“storage engine”).
26. Each storage engine can have widely varying behavior, features, and properties.
27. Foreign keys are not supported in most storage engines.
28. The default storage engine is non-transactional and corrupts easily.
29. Oracle owns InnoDB, the most advanced and popular storage engine.
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.
31. Execution plans are not cached globally, only per-connection.
32. Full-text search is limited and only available for non-transactional storage backends. Ditto for GIS/spatial types and queries.
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.
34. There are no integrated or add-on business intelligence, OLAP cube, etc packages.
35. There is nothing analogous to Grid Control.
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.
37. There are no user-defined types or domains.
38. The number of joins per query is limited to 61.
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.
40. There are no functional columns (e.g. a column whose value is calculated as an expression).
41. You cannot create an index on an expression, you can only index columns.
42. There are no materialized views.
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.
44. There is no built-in promotion or failover mechanism.
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.
46. Cluster is not what you think it is. Maybe I already said that, but it bears repeating.
47. The data dictionary (INFORMATION_SCHEMA) is limited and very slow (it can easily crash a busy server).
48. There is no online ALTER TABLE.
49. There are no sequences.
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.

:: Useful web links List

Site Keyword tags

Abbreviations (1) Acting choices (1) actress-actors (2) addons (1) ads (33) adsense drawbacks (1) advantages (6) advantages of chennai (1) Advantages of Links directory (1) advantages of plastic cheppal stand (1) advices (2) affiliates (1) all ads (1) amazon (1) android (2) articles (34) asp (6) ASP dot Net design issues (1) astrolgy (2) autobiography (2) automobile (1) avoid hacking (1) avoid snake bite (1) backgrounds (1) banners (2) BCS (2) Best category for best collections (1) best PPT Presentations (1) best things (1) biodata (1) blog (1) Blog Advertising (1) blog stats (1) blogger (1) blogger drawbacks (1) blogs (3) Browser errors (2) Browser Issues (5) bus ticketing machines (2) business (2) business loss (11) Business Plan (1) Business rental (1) business wise drawbacks (1) Buying Expired Domains (1) capgemini (1) Car buying tips (1) certified professional (1) cheat frauds (7) Cinema industry (1) cities (15) clients (2) Closed IT/Software companies (1) closed websites (1) codings (21) Commented topics of my blog (1) comments (6) companies (11) Construction (1) contacts (1) Copyright (1) creativity (1) Credit Card Prefix Numbers (1) css (7) CSS drawbacks (1) CSS elements combination (1) CSS errors (2) css ID rules (1) css limitations (1) css links (1) css selectors (1) Cute babies (1) cv (1) demonetization (1) design category (2) disclaim (1) DNS error (1) Documentation (3) domain (2) drawbacks (9) Drawbacks in Madurai bus stand (1) drawbacks of legins (1) drawbacks of strike (2) drawbacks_others (3) duty (1) education (2) electronics (2) emails (1) embedded (2) empty post (1) emptyland (1) Error Code and Message (1) eshop (13) Extra expenses (1) Face detection apps use (1) family (1) FAQ (2) FENG OFFICE (1) finance loans (2) firecrackers (1) firewall nonblockable sites (1) food (3) For sales (1) Free (1) fresh content (6) friends (2) fun (1) general - new - temp (5) GHS (3) girls (8) google (5) google Ads (1) Google Adsense Revenue Sharing Sites (1) government (1) GPS merits (1) graphicsbg (1) Green earth (1) Hacking Methods (1) hand held machines (1) handicapped reservation (1) header wordings (1) health (2) Helpful Slogams (1) HM (1) home shifting (1) how to avoid social media spam (1) HTML (5) html encoder (1) HTML5 website layout (2) IBM (1) IDEA (1) IE (1) IE problems (1) india (1) infosys (1) internet (1) Internet/Social Media Marketing (1) interview questions (4) ISBN (1) IT hardware products for rental (2) IT Hardware-software (2) ja (1) jallikattu (1) javascripts (2) job (7) job analysis (1) joomla (2) KSRTC (1) kvic (1) links (33) links_background verification websites (1) links_blogs (8) Links_certifications (1) links_different websites (1) links_letterwise (1) Links_mobile websites (2) Links_My Designs (3) links_my SMM (2) links_other states (1) Links_Product_brand websites (1) links_Reciprocal Links (1) links_SEO/SEM (1) links_Tamilnadu (1) links_tenders (2) links_tools (2) links_UI design (1) links_use for all* (2) list (1) list of industrial estate (1) list of IT parks (1) list of toll free numbers (1) M2M (1) Madurai (1) Madurai 2020 (1) Mail box folder maintenance (1) Malayalam (1) Malayalam movie my reviews (1) manjula miss (1) Manufacturing Industries (1) marketing (3) marketing ideas (1) me at divisional sports (1) MNC site bugs (1) mobile (4) monthly extra (1) Multiple options in codings (1) my (2) my achievements (1) my achivements (1) My advt - ads (10) My all ads (1) My best collections (1) my best ideas (1) my comments (2) My cousin's post in FB (1) My donations (1) my friends with actor actress (1) My Google dashboard (1) My horoscope (2) my linkedin profile (1) My online interview (1) My Portfolio (2) my posts links (1) My questions (2) my SEO experience (1) my social media profiles (2) my stack overflow profiles (1) My Stackoverflow site answers (2) My works (1) Names (1) nested html (1) Netscape Navigator (1) new blogs (1) New build house for sale (1) New Concepts (1) news (4) online earnings (3) online generators (1) online scams (2) open office drawbacks (1) Open source (2) Opera (1) orkut drawbacks (1) Others view (21) our grandparents (1) Our ID and Numbers (1) outsource statistics (1) own ideas (15) own ideas_aboutme (13) own views (51) page pixel collapse when scrolling in firefox (1) Page Speed Performance (1) parallax scrolling websites (1) Partners (2) Personals (1) photos (23) Photos - art (3) Photos_actress (7) photos_screenshots (1) PHP (3) plans (1) Plots for sale (2) poems (3) policy (1) Pollution Facts (1) powercut (1) Powerpoint (1) press release (1) Problems and solutions - ATM (1) Problems and solutions - Education (2) Problems and solutions - Hardware (4) Problems and solutions - Mobilephones (4) Problems and solutions - NGO (7) Problems and solutions - Software (6) Problems and solutions - Website (7) problems in browsing center/internet cafe (1) Products (16) programmers problems (1) programs (7) proxy software (1) Quality guidelines (2) Rajamani (1) realesate (2) reasons for power shortage (1) Recent comments (1) relatives (2) relatives business (1) resume (1) RS 232 dotmatrix printers (1) rti (1) sales (6) SBI (1) search engine (2) search type websites (1) security (2) Security Alert regarding Serious CMS Attack (1) SEM portfolio (2) SEO Difficulty (1) SEO for blogs (1) SEO portfolio (1) SEO tools (2) SEO-SMM-ORM Plan (1) SEO/SEM (29) sil ads (1) Site Tags (4) siteworth (1) slogans (1) SMS benefits (1) Social Interest - public use (33) social media marketing (13) Social Media Optimization (1) social media widgets (1) softland-palmtec (2) software errors (1) Software Testing (6) Softwares (8) solairaja (1) sports (1) SQL (1) SQL queries (3) stars (1) statistics (10) stop software patents (1) tablet Grip and interaction (1) Tamil - Tamilar - Tamilnadu (22) TATA (1) TCS (1) technicals (11) Technology (5) tenders (4) Testing Tips (1) text magics (1) Thanks (1) Things to do before you die in India (1) Things to Watch in 2014 (1) thirumalai (1) Tips (5) Tips - Buseness tips (23) Tips - Health tips (8) Tips - life tips (18) tips for demo video preparation (1) Tips for doing SEO/SMM (3) Tips for Good-Employee (1) Tips for HR (2) Tips for old money (1) tours (1) Travels (1) TV (1) UI Certifications Q & A (1) UI design (4) useful best software latest versions (1) Useful websites for HTML Developer (1) videos (1) visa (1) vista icons (1) w3c standards (1) wallpapers (2) ways to find a job (1) Web design (9) Web design and developments (34) Web developments (6) Web Services (1) web UI design shapes (1) webhosting (10) Website Features (1) website templates (1) Widgets collections (4) Yahoo (1) சுயசரிதம் (1)