Re: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. No, it's not a goal in itself, that's not what I said. A lot of the enterprise level features can be useful in certain cases, Normalizing data has nothing to do with enterprise level, it's a matter if keeping your data consistent, being able to create proper constraints at the database, for example. but it seems that a lot of times they are just used simply to use them. I cannot find justification for making databases unnecessarily complex, using subqueries when a simple join is all you need, using views, functions, stored procedures in cases that don't require such features, etc. I agree that a lot of people requiring more powerful hard- and software for their application are simply forgetting that they were supposed to produce a working application and not the most normalized database with all the fancy views and other stuff. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Tom, I noticed the article didn't say how much money you'll save by not paying through the nose for Oracle per server licensing, the cost of upgrading your hardware to get some speed out of Oracle, or the cost of having to hire one or more Oracle administrators to manage and tweak the database. how much does an oracle programmer who can maintain your queries with more than 61 joins cost, in, say, usd/hr? Views :-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. No, it's not a goal in itself, that's not what I said. I didn't say that you said that. You stated that Normalizing gets you -more- tables. It wasn't mentioned why you wanted to normalize the database in the first place. To me your statement looked like it said that normalizing a database would be a requirement for any database. This automatically would produce queries with 61+ joins in them. A lot of the enterprise level features can be useful in certain cases, Normalizing data has nothing to do with enterprise level, it's a matter if keeping your data consistent, being able to create proper constraints at the database, for example. Normalizing has nothing to do with enterprise level, but joining complex views has. Don't ask yourself why you've created the views, just use them in a join. So normalize each database because you may want to create constraints in some situations? This is the behaviour which causes unnecessarily complex databases, queries and applications. If you ask yourself if normalizing a column in a table is useful and if you really need the constraint and if the view, stored procedure, function or whatever you use is really useful, chances are that the application is a lot simpler, faster and easier to maintain. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. No, it's not a goal in itself, that's not what I said. I didn't say that you said that. You stated that Normalizing gets you -more- tables. It wasn't mentioned why you wanted to normalize the database in the first place. To me your statement looked like it said that normalizing a database would be a requirement for any database. Yes, that's a good thing, unless it's an OLAP database. It improves data consistency and avoids NULLs in storage, which is good. This automatically would produce queries with 61+ joins in them. A lot of the enterprise level features can be useful in certain cases, Normalizing data has nothing to do with enterprise level, it's a matter if keeping your data consistent, being able to create proper constraints at the database, for example. Normalizing has nothing to do with enterprise level, but joining complex views has. Don't ask yourself why you've created the views, just use them in a join. So normalize each database because you may want to create constraints in some situations? Constraints are a good thing. This is the behaviour which causes unnecessarily complex databases, queries and applications. Unless you don't value your data very much, I consider normalizing, database constraints etc a pro, not a con. If you ask yourself if normalizing a column in a table is useful and if you really need the constraint and if the view, stored procedure, function or whatever you use is really useful, chances are that the application is a lot simpler, faster and easier to maintain. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Oracle will sell it if they can convince the customer. Any one who has had the pleasure of using Oracle Application Server can attest to that. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
On 01/29/2010 07:24 PM, Shawn Green wrote: Rudy Lippan wrote: On 01/29/2010 02:57 PM, Chris W wrote: Hardcore stupid if you ask me. I suppose it is possible to have a valid reason (can't imagine what it might be) for using more than 61 How about complex data requirements? Depending on the resolution of your data set, I could see a simple person-type object that contained name, address, SSN, mother, and birth_info starting to approach the limit. In a simplified Object-to-Database map, most object types (classes) equate to a single table. Each table will contain several columns. Each column will represent one particular property of the object. For objects that contains lists of sub-values or sub-objects, you use another table (usually called a child) related to the first (often called a parent). You are speaking to a simplified mapping; whereas, I was suggesting one valid reason for needing many tables in a join, viz., you have a requirement for *high resolution* data set. Maybe think of a genealogy-type database where someone might have a good reason inquire whether 2 people lived within 5 miles of each other between July 23, 1843 and September 18, 1858. Simple right? One little query. But now, let us add that the city was renamed 4 times, lost in a war to another country, recaptured, annexed by another city which was eventually dissolved for lack of tax revenue. Or to get a few more tables in the mix: Could those people have lived on the same street, same block, within 15 houses? And let us not forget that the street has been renamed many times, renumbered a few (using different numbering schemes), some properties were subdivided, and two adjoining properties were held, at one point, in single and separate ownership but ended up being purchased by the same person during a time when the zoning laws forced a merger. Sure it is overkill for your shopping cart, but not for my database of all worldly knowledge :) It is just a matter of how you look it. BTA, if you were writing your shopping cart for a genealogy website that had the above database, you might just create a view, city(city_id, current_name), and use that id when storing user/credit card info. OK, after this last statement I will cut you some serious slack. However, and I hope you agree, unless someone is using some rather obscene normalization, most queries should not require joins of more than 10 or 12 tables to resolve. Or using multiple imported data sets that are each normalized, or using a code generator, or, or ,or. In general, I agree, but only in general. My personal thumbrule is that if I have more than about 7-9 tables in a single query, I should probably attack the problem in stages. I do this because the physical act of logically (internally) representing all of those columns across all of those row permutations in memory can become a burden to process. Here you are talking about working around limitations: Either yours or the database's. -r -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
On 1/29/10 5:03 PM, mos mo...@fastmail.fm wrote: I noticed the article didn't say how much money you'll save by not paying through the nose for Oracle per server licensing, the cost of upgrading your hardware to get some speed out of Oracle, or the cost of having to hire one or more Oracle administrators to manage and tweak the database. how much does an oracle programmer who can maintain your queries with more than 61 joins cost, in, say, usd/hr? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
I once was selling a system to an organization. I recommended an IBM AIX box for about $30,000. A competitor was charging $30,000 for the software and said it had to run on an AS/400 that would cost in excess of $200,000. I lost the sale because the IBM salesman said, quite candidly, 'I make more commission on the AS/400 so that's the one I am selling.' Oracle is very similar. They are managed to make money. I suspect we will see licensing fees and required support contracts because they can now charge them. And, an Oracle consultant to write a join with 100-200 joins? Oracle will sell it if they can convince the customer. Just some thoughts. - Original Message - From: Tom Worster f...@thefsb.org To: mos mo...@fastmail.fm; mysql@lists.mysql.com Sent: Sunday, January 31, 2010 7:39 PM Subject: Re: 50 things to know before migrating from Oracle to MySQL On 1/29/10 5:03 PM, mos mo...@fastmail.fm wrote: I noticed the article didn't say how much money you'll save by not paying through the nose for Oracle per server licensing, the cost of upgrading your hardware to get some speed out of Oracle, or the cost of having to hire one or more Oracle administrators to manage and tweak the database. how much does an oracle programmer who can maintain your queries with more than 61 joins cost, in, say, usd/hr? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. A lot of the enterprise level features can be useful in certain cases, but it seems that a lot of times they are just used simply to use them. I cannot find justification for making databases unnecessarily complex, using subqueries when a simple join is all you need, using views, functions, stored procedures in cases that don't require such features, etc. I agree that a lot of people requiring more powerful hard- and software for their application are simply forgetting that they were supposed to produce a working application and not the most normalized database with all the fancy views and other stuff. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Rudy Lippan wrote: How about complex data requirements? Depending on the resolution of your data set, I could see a simple person-type object that contained name, address, SSN, mother, and birth_info starting to approach the limit. Cities change, address changes, names change, and even mothers can change. The simple-looking street part of an address can have (at least) number, direction, name, suffix, any of which can change. Okay, so you want to link a person to an address table. I can justify that in the case of multiple addresses with a single person. But then you build a 'city' table to normalize that. Or no, better make a zip code table, link that to the 'city' table. Wait, streets can change names; a 'street' table too to link. Oh no! sometimes streets are split. So an address is a 'property' (a piece of ground), linked to a street, street linked to zip code, zip code linked to city. Damn (sorry), a 'property' can be divided... Oh my... Ever thought about updating a table by renaming a street? Or by selecting a group of street-number combinations and rename them? The real art is trying to balance the need of simplicity and ease of understanding with the need for flexibility, and that has nothing to do with relational theory. In real life the balance tends to go to unnecessary flexibility resulting in systems which are simply too heavy for the actual needs. Complex datasets are, by their nature, complex, and can only be simplified so much. You try to hide the complexity, you shift it, you move-it, you send it to its room, you pretend it is not there. And yet it still pops up at the most inopportune times and has to be dealt with. And still, in a lot of cases the complex datasets are even made more complex by normalization, trying to be ultimately flexible and creating a solution for problems which simply don't exist. In almost all cases a simple solution will be the best. Regards, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
On 1/28/10 5:21 AM, changuno chang...@rediffmail.com wrote: Hi folks, Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this? as a relatively unsophisticated dbms user (just dynamic web site back end), i thought it was very interesting to see the kinds of things oracle users do that i'd never have imagined. more than 61 joins in a query?! man, those guys are hardcore. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Hmmm... I find it suspicious that there are *exactly* 50 things you need to know before migrating from oracle to mysql. Not 49. Not 51. Exactly 50. Well, he did repeat that clustering is not what you think it is so I guess it technically is 49. But I wonder what would happen if he thunk up a 51st thing or if somebody emailed him one more thing. - Original Message - From: Carl c...@etrak-plus.com To: mysql@lists.mysql.com Sent: Thursday, January 28, 2010 4:56 PM Subject: Re: 50 things to know before migrating from Oracle to MySQL A quick Google turned up http://www.xaprb.com/blog/2009/03/13/50-things-to-know-before-migrating-oracle-to-mysql/ Man, I love Google. Thanks, Carl - Original Message - From: Daevid Vincent dae...@daevid.com To: mysql@lists.mysql.com Cc: 'changuno ' chang...@rediffmail.com Sent: Thursday, January 28, 2010 5:49 PM Subject: RE: 50 things to know before migrating from Oracle to MySQL -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Thursday, January 28, 2010 2:16 PM To: mysql@lists.mysql.com On 1/28/2010 3:21 AM, changuno wrote: Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this? would it have been too much to just link to it? Thought the same thing. Not only that, it would have been PREFERRED, so I can BOOKMARK it and SHARE it with my other colleagues. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jh...@math.wisc.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
25. Each table can have a different storage backend (”storage engine”). Yes, we absolutely allow this. Each engine brings a certain strength to the storage and retrieval solutions you can create with MySQL. We explicitly recognize that there is no one size fits all approach that meets the needs of every problem. This also allows for special-purpose solutions to be integrated into MySQL: http://solutions.mysql.com/solutions/?type=29 Actually, this is one thing that annoys me too, or actually, that not everything is supported in every storage engine. You get, for example, full text indices, but no transactions. And so on. 38. The number of joins per query is limited to 61. True, but why is this a problem? Do you frequently (or ever) need to join more than 61 tables into the same query? If you do, I propose that you need to revisit your schema design choices or review how you write your queries. In this case, I think we are discouraging bad practices. Bad practices? So, if you have too many joins, your schema design is wrong? This is just silly... if your data is split over different tables it's usually because it's normalized, and especially for more complex applications this is a pro, not a con. 49. There are no sequences. Please explain why auto_increment cannot meet this same need? Why have the overhead of two ways of performing essentially the same function? This is just one less way to confuse your design. Sequences are way easier to use in multi-table inserts. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
fsb wrote: as a relatively unsophisticated dbms user (just dynamic web site back end), i thought it was very interesting to see the kinds of things oracle users do that i'd never have imagined. more than 61 joins in a query?! man, those guys are hardcore. Hardcore stupid if you ask me. I suppose it is possible to have a valid reason (can't imagine what it might be) for using more than 61 joins. But I would be willing to bet that 99.99% of the time if you get even close to that many joins you have a very poorly designed database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
At 01:57 PM 1/29/2010, Chris W wrote: fsb wrote: as a relatively unsophisticated dbms user (just dynamic web site back end), i thought it was very interesting to see the kinds of things oracle users do that i'd never have imagined. more than 61 joins in a query?! man, those guys are hardcore. Hardcore stupid if you ask me. I suppose it is possible to have a valid reason (can't imagine what it might be) for using more than 61 joins. But I would be willing to bet that 99.99% of the time if you get even close to that many joins you have a very poorly designed database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Chris W I noticed the article didn't say how much money you'll save by not paying through the nose for Oracle per server licensing, the cost of upgrading your hardware to get some speed out of Oracle, or the cost of having to hire one or more Oracle administrators to manage and tweak the database. I guess they forgot to mention that. :-) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
as a relatively unsophisticated dbms user (just dynamic web site back end), i thought it was very interesting to see the kinds of things oracle users do that i'd never have imagined. more than 61 joins in a query?! man, those guys are hardcore. Hardcore stupid if you ask me. I suppose it is possible to have a valid reason (can't imagine what it might be) for using more than 61 joins. But I would be willing to bet that 99.99% of the time if you get even close to that many joins you have a very poorly designed database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. That being said, try joining several complex views and you'll get more joins... With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
... or 50 ways to leave your Oracle... ... or 50 ways to save your money... Choose mysql! :) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
On 01/29/2010 02:57 PM, Chris W wrote: Hardcore stupid if you ask me. I suppose it is possible to have a valid reason (can't imagine what it might be) for using more than 61 How about complex data requirements? Depending on the resolution of your data set, I could see a simple person-type object that contained name, address, SSN, mother, and birth_info starting to approach the limit. Cities change, address changes, names change, and even mothers can change. The simple-looking street part of an address can have (at least) number, direction, name, suffix, any of which can change. joins. But I would be willing to bet that 99.99% of the time if you get even close to that many joins you have a very poorly designed database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. I suspect otherwise. In my experience, most of the time when someone does not understand relational databases, there is a tendency towards fewer tables; and, in the few cases where I have seen too many tables, the joins were more likely to be done in the application code than in the database... Fun Times there The real art is trying to balance the need of simplicity and ease of understanding with the need for flexibility, and that has nothing to do with relational theory. Complex datasets are, by their nature, complex, and can only be simplified so much. You try to hide the complexity, you shift it, you move-it, you send it to its room, you pretend it is not there. And yet it still pops up at the most inopportune times and has to be dealt with. -r -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Rudy Lippan wrote: On 01/29/2010 02:57 PM, Chris W wrote: Hardcore stupid if you ask me. I suppose it is possible to have a valid reason (can't imagine what it might be) for using more than 61 How about complex data requirements? Depending on the resolution of your data set, I could see a simple person-type object that contained name, address, SSN, mother, and birth_info starting to approach the limit. You described one table with 5 columns. Cities change, address changes, names change, and even mothers can change. All of these would be tracked in different rows, not different tables. The simple-looking street part of an address can have (at least) number, direction, name, suffix, any of which can change. That's one more table for addresses. So far you are up to two whole tables. In a simplified Object-to-Database map, most object types (classes) equate to a single table. Each table will contain several columns. Each column will represent one particular property of the object. For objects that contains lists of sub-values or sub-objects, you use another table (usually called a child) related to the first (often called a parent). joins. But I would be willing to bet that 99.99% of the time if you get even close to that many joins you have a very poorly designed database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. I suspect otherwise. In my experience, most of the time when someone does not understand relational databases, there is a tendency towards fewer tables; and, in the few cases where I have seen too many tables, the joins were more likely to be done in the application code than in the database... Fun Times there The real art is trying to balance the need of simplicity and ease of understanding with the need for flexibility, and that has nothing to do with relational theory. Complex datasets are, by their nature, complex, and can only be simplified so much. You try to hide the complexity, you shift it, you move-it, you send it to its room, you pretend it is not there. And yet it still pops up at the most inopportune times and has to be dealt with. OK, after this last statement I will cut you some serious slack. However, and I hope you agree, unless someone is using some rather obscene normalization, most queries should not require joins of more than 10 or 12 tables to resolve. My personal thumbrule is that if I have more than about 7-9 tables in a single query, I should probably attack the problem in stages. I do this because the physical act of logically (internally) representing all of those columns across all of those row permutations in memory can become a burden to process. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
50 things to know before migrating from Oracle to MySQL
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
Re: 50 things to know before migrating from Oracle to MySQL
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 chang...@rediffmail.com 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
Re: 50 things to know before migrating from Oracle to MySQL
changuno wrote: Hi folks, Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this? ... list snipped ... MySQL was never designed to be a clone of Oracle (the database). We have distinct differences in design and implementation that make us a wonderful product to use as a general purpose database. I agree with the other respondent: If your project cannot possibly function without one or more of the features that MySQL does not provide, then don't use it. However, our feature set has been and continues to be complete and powerful enough to be the storage engine behind some of the internet's most popular and heavily visited websites. I can see reasons why some of the feature differences (aka overhead) may be useful in certain use cases. However there is a long history of popular usage that indicates that not everyone, or every project, requires the full set of features you describe: http://www.mysql.com/customers/ I agree with some of the points you make and we are working to implement some of the features you mentioned. On the other hand some of those deficiencies that you mention are specific strengths of the MySQL system: 23. There are fewer and less sophisticated tools for administration. MySQL doesn't need them. That alone should tell you something about our reliability. 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. Again, this is an indication that you don't *need* complex tools or a GUI to work with MySQL. The simple solution is often the better solution. It also allows you to develop for your server from practically anywhere, not just a machine where your GUI tools are installed. 25. Each table can have a different storage backend (”storage engine”). Yes, we absolutely allow this. Each engine brings a certain strength to the storage and retrieval solutions you can create with MySQL. We explicitly recognize that there is no one size fits all approach that meets the needs of every problem. This also allows for special-purpose solutions to be integrated into MySQL: http://solutions.mysql.com/solutions/?type=29 28. The default storage engine is non-transactional and corrupts easily. True: MyISAM is does not require the disk and CPU overhead of tracking changes transactionally. False: In my experience (I do work for Support) MyISAM is rarely corrupted. I dispute this claim. 29. Oracle owns InnoDB, the most advanced and popular storage engine. As of yesterday, this became a moot point. Oracle now owns MySQL, too. http://www.oracle.com/us/sun/index.htm 34. There are no integrated or add-on business intelligence, OLAP cube, etc packages. False. Please see: http://solutions.mysql.com/solutions/ 38. The number of joins per query is limited to 61. True, but why is this a problem? Do you frequently (or ever) need to join more than 61 tables into the same query? If you do, I propose that you need to revisit your schema design choices or review how you write your queries. In this case, I think we are discouraging bad practices. 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. Again, the vast majority of data storage and retrieval activities do not require these features. If you absolutely cannot function without them, then do not use MySQL. 44. There is no built-in promotion or failover mechanism. Again, we have no one size fits all approach to this. We do not assume to understand your business processes nor do we want you to design your process to support our procedures. The failover process is yours to design and implement as you see fit. 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. Yes, it is asynchronous. This is a distinct advantage to many read-heavy applications and it allows MySQL to scale out better than most, if not all, other RDBMS systems. http://www.mysql.com/why-mysql/white-papers/mysql_wp_scaleout.php http://www.mysql.com/why-mysql/scaleout/booking.html 49. There are no sequences. Please explain why auto_increment cannot meet this same need? Why have the overhead of two ways of performing essentially the same function? This is just one less way to confuse your design. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
Re: 50 things to know before migrating from Oracle to MySQL
Shawn Green wrote: 23. There are fewer and less sophisticated tools for administration. MySQL doesn't need them. That alone should tell you something about our reliability. This speaks to simplicity-- both in terms of easy to use and in terms of more limited features. It says nothing about reliability. 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. Yes, it is asynchronous. This is a distinct advantage to many read-heavy applications and it allows MySQL to scale out better than most, if not all, other RDBMS systems. http://www.mysql.com/why-mysql/white-papers/mysql_wp_scaleout.php http://www.mysql.com/why-mysql/scaleout/booking.html There is a lot of truth to what the original poster says about MySQL replication edge cases, including those involving data integrity/data loss. These edge cases are by design, since it is the binlogs replicated, and not the particular storage engine's commit logs. It's one thing to scale out well when we're talking about comments to cat videos, as there is no harm done if my comment is lost or is slow to replicate around. It's another when we're talking financial transactions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
On 1/28/2010 3:21 AM, changuno wrote: Hi folks, Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this? would it have been too much to just link to it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: 50 things to know before migrating from Oracle to MySQL
-Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Thursday, January 28, 2010 2:16 PM To: mysql@lists.mysql.com On 1/28/2010 3:21 AM, changuno wrote: Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this? would it have been too much to just link to it? Thought the same thing. Not only that, it would have been PREFERRED, so I can BOOKMARK it and SHARE it with my other colleagues. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
A quick Google turned up http://www.xaprb.com/blog/2009/03/13/50-things-to-know-before-migrating-oracle-to-mysql/ Man, I love Google. Thanks, Carl - Original Message - From: Daevid Vincent dae...@daevid.com To: mysql@lists.mysql.com Cc: 'changuno ' chang...@rediffmail.com Sent: Thursday, January 28, 2010 5:49 PM Subject: RE: 50 things to know before migrating from Oracle to MySQL -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Thursday, January 28, 2010 2:16 PM To: mysql@lists.mysql.com On 1/28/2010 3:21 AM, changuno wrote: Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this? would it have been too much to just link to it? Thought the same thing. Not only that, it would have been PREFERRED, so I can BOOKMARK it and SHARE it with my other colleagues. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Doesn't Google run MySQL ? Hmmm On Thu, Jan 28, 2010 at 4:56 PM, Carl c...@etrak-plus.com wrote: A quick Google turned up http://www.xaprb.com/blog/2009/03/13/50-things-to-know-before-migrating-oracle-to-mysql/ Man, I love Google. Thanks, Carl - Original Message - From: Daevid Vincent dae...@daevid.com To: mysql@lists.mysql.com Cc: 'changuno ' chang...@rediffmail.com Sent: Thursday, January 28, 2010 5:49 PM Subject: RE: 50 things to know before migrating from Oracle to MySQL -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Thursday, January 28, 2010 2:16 PM To: mysql@lists.mysql.com On 1/28/2010 3:21 AM, changuno wrote: Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this? would it have been too much to just link to it? Thought the same thing. Not only that, it would have been PREFERRED, so I can BOOKMARK it and SHARE it with my other colleagues. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
