Migration from ORACLE to MySQL - CLOB
I need to migrate about a dozen tables from ORACLE 10g to MySQL 5. I have manually migrated the schema to MySQL. I am able to write SQL*PLUS queries to extract ORACLE data into insert statements (including date conversions to MySQL format etc) that I can run against the MySQL database. The CLOB fields are tripping me because of single quotes, double quotes and carriage returns in the data. I can possibly change all single quotes to two single quotes and double quotes to two double quotes and MySQL will be happy ingesting that data into TEXT fields. The carriage returns are breaking the lines when the SQL*PLUS data is spooled into a flat file. How can I deal with quotes and carriage returns in CLOB data? Thanks, Rajesh
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
JOIN migration from Oracle to MySQL
Hello, I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like: select ... from... where ... and PT1.ID (+) = bl.PARENTTYPE_1 and PT2.ID (+) = bl.PARENTTYPE_2 ... MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted. example: select ... from tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1), tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2), ... - doesnt' work. Exits a solution for this example? Other syntax possibilities? regards, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN migration from Oracle to MySQL
[EMAIL PROTECTED] wrote: Hello, I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like: select ... from... where ... and PT1.ID (+) = bl.PARENTTYPE_1 and PT2.ID (+) = bl.PARENTTYPE_2 ... MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted. example: select ... from tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1), tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2), The exact error message would be helpful, but I'm seeing at least two problems: 1) you're aliasing two tables as 'bl'. The aliases need to be unique. 2) The second ON clause shouldn't start with AND. Otherwise you should have no problem doing this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN migration from Oracle to MySQL
Hello, thank you - now it works. d_parenttype PT1 LEFT OUTER JOIN t_booklists bl ON (PT1.ID = bl.PARENTTYPE_1), d_parenttype PT2 LEFT OUTER JOIN t_booklists bk ON (PT2.ID = bk.PARENTTYPE_2) I had to put the alias to all listet fields in the select. regards, Spiker Original-Nachricht Datum: Fri, 31 Aug 2007 09:30:13 -0400 Von: Baron Schwartz [EMAIL PROTECTED] An: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Betreff: Re: JOIN migration from Oracle to MySQL [EMAIL PROTECTED] wrote: Hello, I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like: select ... from... where ... and PT1.ID (+) = bl.PARENTTYPE_1 and PT2.ID (+) = bl.PARENTTYPE_2 ... MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted. example: select ... from tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1), tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2), The exact error message would be helpful, but I'm seeing at least two problems: 1) you're aliasing two tables as 'bl'. The aliases need to be unique. 2) The second ON clause shouldn't start with AND. Otherwise you should have no problem doing this. -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN migration from Oracle to MySQL
[EMAIL PROTECTED] wrote: Hello, thank you - now it works. d_parenttype PT1 LEFT OUTER JOIN t_booklists bl ON (PT1.ID = bl.PARENTTYPE_1), d_parenttype PT2 LEFT OUTER JOIN t_booklists bk ON (PT2.ID = bk.PARENTTYPE_2) I had to put the alias to all listet fields in the select. Unless you are relating PT1 to PT2 in some way, you should not expect this query to perform well because you will be generating a Cartesian product between PT1 and PT2. I doubt this is actually what you are trying to do (although it will eventually work). If you posted just a few more details about the query you are trying to write, we could try to help you to rewrite it in a way that will perform much better than the translation you just attempted. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ ___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
LOL - an entertaining read! Entertaining? I feel to see the humor in his post. I thought it was concise and well written, with an undertone of I know I'm swearing in church but So yes, I found it entertaining (I agree that it was not necessarily humorous or funny). Ah right :-) ... now I understand. One advantage of multiple storage engines that comes to mind is that you can streamline your setup for different workloads: - Innodb/Falcon for non-trivial concurrency workloads - Myisam for fairly static or bulk-loaded (mainly) read workloads. MyISAM never really got finished as a data storage engine and neither did InnoDB. MyISAM doesn't support referential constraints, so for any serious data storage, it's a no-go area for me. InnoDB, on the other hand, doesn't support Full Text Indices (Search), that's where MyISAM comes into play. That's the problem with the currently available non-alpha storage engines in MySQL: they just don't cut it. While your factual observations are undoubtedly correct, the conclusions bear some discussion. In particular for data warehousing constraints are not so important - as the ETL process that loads your data typically needs to check it anyway - and are often not practical - for instance enabling a foreign key constraint on a 10 billion row/10TB fact table is gonna just take too long ...(you tend to see ALTER TABLE ADD CONTRAINT xxx ... DERERRED/NOVERIFY or similar syntax with other database vendors to add the constraint but stop it doing anything except being a data point for the optimizer). Data warehousing always requires a slightly different strategy, I agree. When it comes to database application, I'm always talking about online transaction processing and the like. I agree that all the Mysql storage engines need work ... I assume that's being sorted (perhaps not as fast as we all would like) by the various developers. And just be be clear, the storage engines of most databases need work - for instance I work for a company that has used Postgres to make a parallel shared nothing data warehouse engine (sounds a bit like NDB huh?), and yep, the Postgres storage engine has areas we are wanting to improve! I don't consider the different storage engines in MySQL a strong point because none of them do the complete works. Now, if, for example, Falcon gets finished and it does full text indexing, transactions, check/unique/primary and foreign key constraints, then we're getting somewhere. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Storage engines are unique to MySQL? yes. Is that good? YMMV. Most of the purported benefits can be achieved with Oracle's features without the compromises of balkanised storage engines. You're right, they're not offered by Oracle, or anyone else ... there's a reason no other database bothers with storage engines - they got storage right the first time :-) (ooh ... the flames I'll get for that :-) ). Sure, non-volatile data in a MyISAM table can be read at the speed of light, and handle the odd insert. Funnily enough, a text file has the same properties. They both suck for non-trivial concurrent transactions. I'd suggest taking a look at parallel DML, nologging, MVs, partitioning, direct-path insert, appended insert, RAC, ASM, ASSM, etc. etc. etc. in Oracle for more perspective Grant, LOL - an entertaining read! Entertaining? I feel to see the humor in his post. One advantage of multiple storage engines that comes to mind is that you can streamline your setup for different workloads: - Innodb/Falcon for non-trivial concurrency workloads - Myisam for fairly static or bulk-loaded (mainly) read workloads. MyISAM never really got finished as a data storage engine and neither did InnoDB. MyISAM doesn't support referential constraints, so for any serious data storage, it's a no-go area for me. InnoDB, on the other hand, doesn't support Full Text Indices (Search), that's where MyISAM comes into play. That's the problem with the currently available non-alpha storage engines in MySQL: they just don't cut it. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Martijn Tonies wrote: LOL - an entertaining read! Entertaining? I feel to see the humor in his post. I thought it was concise and well written, with an undertone of I know I'm swearing in church but So yes, I found it entertaining (I agree that it was not necessarily humorous or funny). One advantage of multiple storage engines that comes to mind is that you can streamline your setup for different workloads: - Innodb/Falcon for non-trivial concurrency workloads - Myisam for fairly static or bulk-loaded (mainly) read workloads. MyISAM never really got finished as a data storage engine and neither did InnoDB. MyISAM doesn't support referential constraints, so for any serious data storage, it's a no-go area for me. InnoDB, on the other hand, doesn't support Full Text Indices (Search), that's where MyISAM comes into play. That's the problem with the currently available non-alpha storage engines in MySQL: they just don't cut it. While your factual observations are undoubtedly correct, the conclusions bear some discussion. In particular for data warehousing constraints are not so important - as the ETL process that loads your data typically needs to check it anyway - and are often not practical - for instance enabling a foreign key constraint on a 10 billion row/10TB fact table is gonna just take too long ...(you tend to see ALTER TABLE ADD CONTRAINT xxx ... DERERRED/NOVERIFY or similar syntax with other database vendors to add the constraint but stop it doing anything except being a data point for the optimizer). I agree that all the Mysql storage engines need work ... I assume that's being sorted (perhaps not as fast as we all would like) by the various developers. And just be be clear, the storage engines of most databases need work - for instance I work for a company that has used Postgres to make a parallel shared nothing data warehouse engine (sounds a bit like NDB huh?), and yep, the Postgres storage engine has areas we are wanting to improve! Cheers Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Grant Allen wrote: Storage engines are unique to MySQL? yes. Is that good? YMMV. Most of the purported benefits can be achieved with Oracle's features without the compromises of balkanised storage engines. You're right, they're not offered by Oracle, or anyone else ... there's a reason no other database bothers with storage engines - they got storage right the first time :-) (ooh ... the flames I'll get for that :-) ). Sure, non-volatile data in a MyISAM table can be read at the speed of light, and handle the odd insert. Funnily enough, a text file has the same properties. They both suck for non-trivial concurrent transactions. I'd suggest taking a look at parallel DML, nologging, MVs, partitioning, direct-path insert, appended insert, RAC, ASM, ASSM, etc. etc. etc. in Oracle for more perspective Grant, LOL - an entertaining read! One advantage of multiple storage engines that comes to mind is that you can streamline your setup for different workloads: - Innodb/Falcon for non-trivial concurrency workloads - Myisam for fairly static or bulk-loaded (mainly) read workloads. Is is hard - maybe impossible - to design one storage that engine does *everything* well (e.g Oracle is not that good for very large data warehouses, as any Teradata sales bloke will tell you...), so I think this is a useful feature unique to Mysql. Cheers Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
On Thursday 26 July 2007 Rajesh Mehrotra's cat, walking on the keyboard, wrote: Check out http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html Please note that PostgreSQL provides a gateway to other databases thanks to the DBI-Link extension, as well as it support more than only jdbc-4 driver (of course the 4 is the best). Luca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Tangirala, Srikalyan wrote: Hi All: Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Sure, let's play devil's advocate for a minute. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data Storage engines are unique to MySQL? yes. Is that good? YMMV. Most of the purported benefits can be achieved with Oracle's features without the compromises of balkanised storage engines. You're right, they're not offered by Oracle, or anyone else ... there's a reason no other database bothers with storage engines - they got storage right the first time :-) (ooh ... the flames I'll get for that :-) ). Sure, non-volatile data in a MyISAM table can be read at the speed of light, and handle the odd insert. Funnily enough, a text file has the same properties. They both suck for non-trivial concurrent transactions. I'd suggest taking a look at parallel DML, nologging, MVs, partitioning, direct-path insert, appended insert, RAC, ASM, ASSM, etc. etc. etc. in Oracle for more perspective - Fast connections Nope, not unique. Prespawned connections in Oracle are about as fast as it gets for any db, short of using a cached connection. - Easy replication We'll, if by unique and not offered by Oracle you mean you get to experience the MySQL pain of sync'ing the data to start with by any one of numerous half-baked manual methods, sure. Silly old Oracle totally automates that, even giving you several handy GUI or sql options depending on your preference. MySQL definitely wins on the does half the job criteria. - Overall ease of use Easy for who? You're absolutely right for simple installs; a quick db to support a simple web page; the persistence layer for a million and one open source apps that could have chosen any db (mysql, postgres, sqllite, jet, bdb, isam, you name it). But have you ever tried to reorg your physical storage in MySQL with the system online? Get the optimiser to do something intelligent with subselects? Handle transaction semantics across storage engines? Easy isn't the word that springs to mind. (OK, I'm all suited up with the asbestos ... flame away :-) ). Ciao Fuzzy :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migration from Oracle to MySQL
All well and good but that comparison is dated: March 14, 2005 Many enhancements all around since then. Simon -Original Message- From: Rajesh Mehrotra [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:32 AM To: Tangirala, Srikalyan; cluster Cc: mysql@lists.mysql.com Subject: RE: Migration from Oracle to MySQL Check out http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html Raj Mehrotra hccs - Experts in Healthcare Learning [EMAIL PROTECTED] -Original Message- From: Tangirala, Srikalyan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:11 AM To: cluster Cc: mysql@lists.mysql.com Subject: Migration from Oracle to MySQL Hi All: I am doing a study on the migration of databases from Oracle to MySQL. In this process, I gathered few points. Overall, I think arguments can be made in favor of MySQL in terms of performance, stability, ease of use, and cost. All of these things point to decreased TCO when using MySQL instead of Oracle. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data - Fast connections - Easy replication - Overall ease of use Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Thanks, Regards, Sri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Cluster Mailing List For list archives: http://lists.mysql.com/cluster To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration from Oracle to MySQL
Hi All: I am doing a study on the migration of databases from Oracle to MySQL. In this process, I gathered few points. Overall, I think arguments can be made in favor of MySQL in terms of performance, stability, ease of use, and cost. All of these things point to decreased TCO when using MySQL instead of Oracle. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data - Fast connections - Easy replication - Overall ease of use Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Thanks, Regards, Sri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Hi, Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? I thought it was your study? MySQL doesn't have (compared to Oracle): - check constraints - a procedural language as mature as Oracle PL/SQL - triggers on a per statement basis as Oracle - Java Stored Procedures or the ability to use .NET - Synonyms - Schemas - Type, Typed Tables, Object Tables, Object Types etc... - integrated PL/SQL Debugger interface Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
RE: Migration from Oracle to MySQL
Thanks for your input Raj. Regards, Sri -Original Message- From: Rajesh Mehrotra [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:32 AM To: Tangirala, Srikalyan; cluster Cc: mysql@lists.mysql.com Subject: RE: Migration from Oracle to MySQL Check out http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html Raj Mehrotra hccs - Experts in Healthcare Learning [EMAIL PROTECTED] -Original Message- From: Tangirala, Srikalyan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:11 AM To: cluster Cc: mysql@lists.mysql.com Subject: Migration from Oracle to MySQL Hi All: I am doing a study on the migration of databases from Oracle to MySQL. In this process, I gathered few points. Overall, I think arguments can be made in favor of MySQL in terms of performance, stability, ease of use, and cost. All of these things point to decreased TCO when using MySQL instead of Oracle. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data - Fast connections - Easy replication - Overall ease of use Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Thanks, Regards, Sri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migration from Oracle to MySQL
Check out http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html Raj Mehrotra hccs - Experts in Healthcare Learning [EMAIL PROTECTED] -Original Message- From: Tangirala, Srikalyan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 26, 2007 11:11 AM To: cluster Cc: mysql@lists.mysql.com Subject: Migration from Oracle to MySQL Hi All: I am doing a study on the migration of databases from Oracle to MySQL. In this process, I gathered few points. Overall, I think arguments can be made in favor of MySQL in terms of performance, stability, ease of use, and cost. All of these things point to decreased TCO when using MySQL instead of Oracle. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data - Fast connections - Easy replication - Overall ease of use Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Thanks, Regards, Sri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tool to migrate data from oracle to mysql
Hi Friends, Does any body know of any tool available for migrating data from oracle to mysql. Can you please pass on the url. regards anandkl
Re: tool to migrate data from oracle to mysql
http://www.mysql.com/products/tools/migration-toolkit/ There is a video presentation as well here for ORacle to MySQL migration. Thanks, - Arun On 3/12/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Friends, Does any body know of any tool available for migrating data from oracle to mysql. Can you please pass on the url. regards anandkl
RE: tool to migrate data from oracle to mysql
Hi All: We are trying to migrate the oracle database to mysql using the MySQL migration kit. During the process, when we have specified the source and target information and tried to establish a connection, the error we have got is the following: Connecting to source database and retrieve schemata names. Initializing JDBC driver ... Driver class Oracle Thin JDBC Driver using Service Opening connection ... Connection jdbc:oracle:thin:oracle/1oracle@//172.31.22.65:5500/GOES The list of schema names could not be retrieved (error: 0). ReverseEngineeringOracle.getSchemata :Io exception: Bad packet type Details: oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162) oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:274) oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:319) oracle.jdbc.driver.PhysicalConnection.init(PhysicalConnection.java:344) oracle.jdbc.driver.T4CConnection.init(T4CConnection.java:148) oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:3 2) oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:545) java.sql.DriverManager.getConnection(Unknown Source) java.sql.DriverManager.getConnection(Unknown Source) com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(ReverseEn gineeringGeneric.java:101) com.mysql.grt.modules.ReverseEngineeringOracle.getSchemata(ReverseEngineering Oracle.java:43) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) java.lang.reflect.Method.invoke(Unknown Source) com.mysql.grt.Grt.callModuleFunction(Unknown Source) What could be the possibility of the error? Any Suggestions? Best, Sri -Original Message- From: Arun Kumar PG [mailto:[EMAIL PROTECTED] Sent: Monday, March 12, 2007 3:37 AM To: Ananda Kumar Cc: mysql@lists.mysql.com Subject: Re: tool to migrate data from oracle to mysql http://www.mysql.com/products/tools/migration-toolkit/ There is a video presentation as well here for ORacle to MySQL migration. Thanks, - Arun On 3/12/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Friends, Does any body know of any tool available for migrating data from oracle to mysql. Can you please pass on the url. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tool to migrate data from oracle to mysql
Hi, Does any body know of any tool available for migrating data from oracle to mysql. Can you please pass on the url. You might want to try our Database Workbench tool: www.upscene.com It includes a Schema Migration and DataPump tool. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
Re: tool to migrate data from oracle to mysql
SQLWays also works, and it does stored procedures, etc, but it's thousands of dollars (depending on the # of objects in your database). http://www.ispirer.com/products/ David Arun Kumar PG wrote: http://www.mysql.com/products/tools/migration-toolkit/ There is a video presentation as well here for ORacle to MySQL migration. Thanks, - Arun On 3/12/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Friends, Does any body know of any tool available for migrating data from oracle to mysql. Can you please pass on the url. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oracle to Mysql Sync
Hello everyone, I had a quick question...I am looking to move away from our dependence on Oracle over to using a Mysql Cluster. Due to the complexity of the move it will have to happen over a period of time, what I would like to do is keep our mysql database in sync with our Oracle DBthis would allow us to prove that the MySQL db can deal with the load, provide the needed uptime,etc. Does anyone have any thoughts on this..I don't mean I need this done once..best case is every time something changes in an oracle table, the change will get send to Mysql...worst case...it needs to be out of sync by no more the 15 minutes...what do you thinkis it possible? Thanks in advance, SKM - Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta.
Re: Oracle to Mysql Sync
I'd given some thought to this a while ago. The only way you are going to be able to tell if a row changes is to have a date column on every oracle table that indicates the last time the data changed. You'll need some program to start up that knows the last time it ran, and the current date, and look for any changed rows between those two dates/times. All dates/times need to come from the database (not the OS). After it finishes, the current date that the code generated needs to be saved somewhere for the next iteration. This won't work when rows are deleted from the database. If you are lucky, and this never happens, it's not an issue. If it only happens on a few tables, you can put triggers on that table (ON DELETE) to generate delete-statements to store in some log-type-table that your program can then execute on the mysql database (and then remove the rows from the log-type-table). If you have 500 tables that can have rows deleted, then you have a bit of work on your hands. This isn't an elegant or simple solution, but I don't know of any application or tool that can be used to watch Oracle tables and apply the changes to a MySQL table in real-time or near real time. On the flip side, because you will be batching updates/inserts/deletes that happen over a period of time (even if it's just 10-15 minutes) and apply them to MySQL all at once, you will be putting 10-15 minutes worth of Oracle processing onto MySQL all at once. Unfort, this isn't an apples-to-apples test. The Oracle statement might be, update some_table set some_column = 'xyz' where some_other_column='abc' and some_third_column_id in (select some_value from some_other_table where some_column = 12) and the mysql statements would just be a bunch of updates keyed off the primary key. Oracle is doing way more work. If you need to do an oranges-to-oranges comparison, then unfortunately the only thing I can think of is to log statements in a centralized location (the Oracle database?) and then replay them in order on the MySQL database. Again, this could be quite a bit of work. The third option is to take the archived redo logs and extract the SQL from them, and replay that SQL (assuming your Oracle-SQL is ANSI-compliant and doesn't use (+) etc for outer joins, and isn't full of Oracle-specific functions, etc). This will only capture updates, inserts, deletes, however; any load on your database due to selects-statements won't be replayed on the MySQL cluster. Check out, http://download-east.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/logminer.htm Note that you might have a tough time running this every 15 minutes. All that said, MySQL Cluster is definitely a different beast. No foreign keys, and potentially slow selects if the query needs to hit multiple clusters to retrieve data. You also need a lot of RAM (2.1 times your dataset, if I remember correctly), etc, etc. Some of this goes away in MySQL 5.1. There is a book coming out in a few weeks on the MySQL Cluster, and the MySQL Conference in Santa Clara has a set of talks devoted to MySQL Cluster. David Shain Miley wrote: Hello everyone, I had a quick question...I am looking to move away from our dependence on Oracle over to using a Mysql Cluster. Due to the complexity of the move it will have to happen over a period of time, what I would like to do is keep our mysql database in sync with our Oracle DBthis would allow us to prove that the MySQL db can deal with the load, provide the needed uptime,etc. Does anyone have any thoughts on this..I don't mean I need this done once..best case is every time something changes in an oracle table, the change will get send to Mysql...worst case...it needs to be out of sync by no more the 15 minutes...what do you thinkis it possible? Thanks in advance, SKM - Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
comunication between Oracle and MYSQL
Hi All, I need some suggestions from you. I need a comunication between Oracle database with MySQL. In my application there is a situation is arising, where i need to take some data to MySql from a table which is in Oracle database (i am planning to maintain that data in MySQL also). And from MySQL my application will use it. This whole thing should be happen online.That is once some new data was inserted to Oracle table, that should update in MySQL table also. It is totally new situation for me and i am totally confused. Please help me folks. Thanks in advance. regards, Bala Raju Mandala.
Re: comunication between Oracle and MYSQL
--On August 14, 2006 9:11:30 PM +0530 balaraju mandala [EMAIL PROTECTED] wrote: Hi All, I need some suggestions from you. I need a comunication between Oracle database with MySQL. http://www.webmethods.com/ They sell software to do this. Or...well... ActiveSoftware/ActiveWorks did which was bought by them. Disclaimer, I worked at Active as the Unix Admin for the Demonstration center. Someone else might have written some software to do it as well. But you either need a third piece of software from someone else or it needs to be part of your app or you need to write it. The databases will not do it for you natively. In my application there is a situation is arising, where i need to take some data to MySql from a table which is in Oracle database (i am planning to maintain that data in MySQL also). And from MySQL my application will use it. This whole thing should be happen online.That is once some new data was inserted to Oracle table, that should update in MySQL table also. It is totally new situation for me and i am totally confused. Please help me folks. Thanks in advance. regards, Bala Raju Mandala. -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comunication between Oracle and MYSQL
balaraju mandala wrote: In my application there is a situation is arising, where i need to take some data to MySql from a table which is in Oracle database (i am planning to maintain that data in MySQL also). And from MySQL my application will use it. This whole thing should be happen online.That is once some new data was inserted to Oracle table, that should update in MySQL table also. Can't you just make your app connect directly to Oracle ? You'll drawn in lots of sync issues if you don't, specially if your app updates the database with data provided from Oracle. Take this example: the user updates something, it goes to Oracle, than MySQL. Your system update lots of things out of it. Than, the user realizes he's wrong and update again the record, and some of your updates might not work, other things were deleted that shouldn't according to your business model. I'm not saying you will have this problem, I'm just saying you might consider it before doing the Oracle-MySQL connector. Btw, I don't know any connector, and don't think a trigger on Oracle will work either. You'll probably have to live with a daemon replicating every 10 seconds or so. cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Avg row length is varying a lot from oracle to MySQL
I am using the show table status command to find the average length of row in a table. And it reported something about 686 bytes. But as I populated more data, this number has substantially decreased to 484 bytes. But one more thing I also learnt is the average row length returned by oracle is just the avg length of each row in the data files ignoring the space occupied by the indexes. But I think MySQL is giving this values taking the space used by indexes into consideration. So they are almost coming the same. :) sujay -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 3:59 AM To: Sujay Koduri Cc: mysql@lists.mysql.com Subject: Re: Avg row length is varying a lot from oracle to MySQL Sujay Koduri wrote: we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. How are you measuring the size of a row in mysql? What makes you think it is averaging 686 bytes? Sujay Koduri also wrote: Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. Umm, using about 50 bytes out of 400 in variable length columns saves about 350 bytes. 600 - 350 = 250, so you should expect about 250 bytes used _before_ indexes. On the other hand, you say it's only 180 in Oracle, so perhaps the estimate is off. OK, looking at your column definitions, I see 118 bytes worth of fixed-width columns, plus 11 to 436 bytes worth of varchar columns, yielding 129 bytes per row with empty varchars, 554 bytes per row with full varchars. With 40 to 50 chars used in the varchars, that would be around 180 bytes per row, just as in Oracle (not including any indexes). Of course, this is assuming you are using 1-byte chars. I can't imagine how that could take 686 bytes per row in mysql. It could just be a failure of my imagination, but you haven't yet shown us how you arrived at that number. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avg row length is varying a lot from oracle to MySQL
Jeff wrote: Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. If you have a varchar(50) but usually only use 10 in those fields MySQL still counts the unused 40 for the total byte count of the row, so you must count them. An empty varchar(50) field still uses 50 bytes. No, it doesn't. Storage for a VARCHAR is L + 1, where L is length * bytes per char. See the manual for details http://dev.mysql.com/doc/mysql/en/storage-requirements.html. Also, I believe text and blob fields are always counted as 255 bytes regardless of your settings. No, it's L + 2. Ordered indexes are 10bytes per column (in the index) per row. I believe there is also some paging overhead so generally take your calculated row size and multiply by 1.1. Where do you see that in the manual? I'm under the impression that index size is related to the size of the column being indexed http://dev.mysql.com/doc/mysql/en/key-space.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avg row length is varying a lot from oracle to MySQL
Sujay Koduri wrote: we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. How are you measuring the size of a row in mysql? What makes you think it is averaging 686 bytes? Sujay Koduri also wrote: Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. Umm, using about 50 bytes out of 400 in variable length columns saves about 350 bytes. 600 - 350 = 250, so you should expect about 250 bytes used _before_ indexes. On the other hand, you say it's only 180 in Oracle, so perhaps the estimate is off. OK, looking at your column definitions, I see 118 bytes worth of fixed-width columns, plus 11 to 436 bytes worth of varchar columns, yielding 129 bytes per row with empty varchars, 554 bytes per row with full varchars. With 40 to 50 chars used in the varchars, that would be around 180 bytes per row, just as in Oracle (not including any indexes). Of course, this is assuming you are using 1-byte chars. I can't imagine how that could take 686 bytes per row in mysql. It could just be a failure of my imagination, but you haven't yet shown us how you arrived at that number. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connectin from ORACLE to MYSQL
Hi Friends, Any help on this is highly appreciated. regards anandkl On 9/23/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Friends, I have set up all the drivers and configured the Hetrogenous service in oracle and set all the relevent parameter. Now when i am connecting to MYSQL from ORACLE i am getting the below error select counter from ebay_stats@test [EMAIL PROTECTED] where rownum 2 * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC][H006] The init parameter HS_FDS_SHAREABLE_NAME is not set. Please set it in initorasid.ora file. ORA-02063: preceding 2 lines from TEST I have set the HS_FDS_SHAREABLE_NAME also in the initmyodbc3.ora file located in $ORACLE_HOME/hs/admin path HS_FDS_CONNECT_INFO = myodbc3 HS_FDS_TRACE_LEVEL = off HS_FDS_TRACE_FILE_NAME = log file name HS_FDS_SHAREABLE_NAME = /usr/local/lib/libmyodbc3.so Please help me. regards anandkl
Re: connectin from ORACLE to MYSQL
Ananda Kumar [EMAIL PROTECTED] wrote on 09/23/2005 05:25:10 AM: Hi Friends, Any help on this is highly appreciated. regards anandkl On 9/23/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Friends, I have set up all the drivers and configured the Hetrogenous service in oracle and set all the relevent parameter. Now when i am connecting to MYSQL from ORACLE i am getting the below error select counter from ebay_stats@test [EMAIL PROTECTED] where rownum 2 * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC][H006] The init parameter HS_FDS_SHAREABLE_NAME is not set. Please set it in initorasid.ora file. ORA-02063: preceding 2 lines from TEST I have set the HS_FDS_SHAREABLE_NAME also in the initmyodbc3.ora file located in $ORACLE_HOME/hs/admin path HS_FDS_CONNECT_INFO = myodbc3 HS_FDS_TRACE_LEVEL = off HS_FDS_TRACE_FILE_NAME = log file name HS_FDS_SHAREABLE_NAME = /usr/local/lib/libmyodbc3.so Please help me. regards anandkl Please admit to yourself that you know you are asking an ORACLE question on a MYSQL mailing list Have you tried reading ORACLE manuals, researching ORACLE list archives, and searching for ORACLE articles or whitepapers that deal with creating, establishing, or troubleshooting heterogenous services? I think that we do not have many people on this list with the knowledge you seek. We may not be able to answer your question in the time you have left. May I encourage you to take the initiative and research this issue using other resources? You will probably get better results than continuing to ask this list. We are capable and willing to help with MySQL questions. However, this is not one. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: connectin from ORACLE to MYSQL
Hi Green, Thanks for the email. As you said, i am also trying other resources to find answers for this question. Just waiting for their answers. Since the answers were little urgent, i was little curious. I will let you all know once i find any solution to this. regards anandkl On 9/23/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Ananda Kumar [EMAIL PROTECTED] wrote on 09/23/2005 05:25:10 AM: Hi Friends, Any help on this is highly appreciated. regards anandkl On 9/23/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Friends, I have set up all the drivers and configured the Hetrogenous service in oracle and set all the relevent parameter. Now when i am connecting to MYSQL from ORACLE i am getting the below error select counter from ebay_stats@test [EMAIL PROTECTED] where rownum 2 * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC][H006] The init parameter HS_FDS_SHAREABLE_NAME is not set. Please set it in initorasid.ora file. ORA-02063: preceding 2 lines from TEST I have set the HS_FDS_SHAREABLE_NAME also in the initmyodbc3.ora file located in $ORACLE_HOME/hs/admin path HS_FDS_CONNECT_INFO = myodbc3 HS_FDS_TRACE_LEVEL = off HS_FDS_TRACE_FILE_NAME = log file name HS_FDS_SHAREABLE_NAME = /usr/local/lib/libmyodbc3.so Please help me. regards anandkl Please admit to yourself that you know you are asking an ORACLE question on a MYSQL mailing list Have you tried reading ORACLE manuals, researching ORACLE list archives, and searching for ORACLE articles or whitepapers that deal with creating, establishing, or troubleshooting heterogenous services? I think that we do not have many people on this list with the knowledge you seek. We may not be able to answer your question in the time you have left. May I encourage you to take the initiative and research this issue using other resources? You will probably get better results than continuing to ask this list. We are capable and willing to help with MySQL questions. However, this is not one. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: connect from oracle to MYSQL.
what about : http://dev.mysql.com/doc/mysql/en/dsn-on-unix.html 2005/9/22, Ananda Kumar [EMAIL PROTECTED]: Hi Pooly, I know your busy, but please help me, i need to submit this by today for the testing team. If you dont mind can you please guide to any url or documentation where i can find the complete steps to connect from oracle 8.1.7.4 on sun 5.8 to mysql 4.1.14 on Lunix fedaro with innodb engine Thanks in advance. regards anandkl On 9/21/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Pooly, Thanks for the help. I did that , where to find odbc.ini file. This pkg does not create this file. regards anandkl On 9/21/05, Pooly [EMAIL PROTECTED] wrote: 2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc /odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r- 3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect from oracle to MYSQL.
Hi Pooly, I tried this also, its not working. Also the odbc.ini file created by the package is empty. I found the same entires in /usr/etc/myodbc3-32.template file. So i copied the contents of this and created an odbc.ini file. After doing this isql is not connecting to MYSQL database.. regards anandkl On 9/22/05, Pooly [EMAIL PROTECTED] wrote: what about : http://dev.mysql.com/doc/mysql/en/dsn-on-unix.html 2005/9/22, Ananda Kumar [EMAIL PROTECTED]: Hi Pooly, I know your busy, but please help me, i need to submit this by today for the testing team. If you dont mind can you please guide to any url or documentation where i can find the complete steps to connect from oracle 8.1.7.4http://8.1.7.4on sun 5.8 to mysql 4.1.14 on Lunix fedaro with innodb engine Thanks in advance. regards anandkl On 9/21/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Pooly, Thanks for the help. I did that , where to find odbc.ini file. This pkg does not create this file. regards anandkl On 9/21/05, Pooly [EMAIL PROTECTED] wrote: 2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc /odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r- 3.51.06.so http://3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Avg row length is varying a lot from oracle to MySQL
hi ,, we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. This is a bit urgent. So any help is greatly appreciated. ID NOT NULL VARCHAR2(50) H0 NUMBER H1 NUMBER H2 NUMBER H3 NUMBER H4 NUMBER H5 NUMBER H6 NUMBER H7 NUMBER H8 NUMBER H9 NUMBER H10 NUMBER H11 NUMBER H12 NUMBER H13 NUMBER H14 NUMBER H15 NUMBER H16 NUMBER H17 NUMBER H18 NUMBER H19 NUMBER H20 NUMBER H21 NUMBER H22 NUMBER H23 NUMBER D1 NUMBER D2 NUMBER D3 NUMBER D4 NUMBER D5 NUMBER D6 NUMBER D7 NUMBER D8 NUMBER D9 NUMBER D10 NUMBER D11 NUMBER D12 NUMBER D13 NUMBER D14 NUMBER D15 NUMBER D16 NUMBER D17 NUMBER D18 NUMBER D19 NUMBER D20 NUMBER D21 NUMBER D22 NUMBER D23 NUMBER D24 NUMBER D25 NUMBER D26 NUMBER D27 NUMBER D28 NUMBER D29 NUMBER D30 NUMBER D31 NUMBER D32 NUMBER D33 NUMBER D34 NUMBER D35 NUMBER D36 NUMBER D37 NUMBER D38 NUMBER D39 NUMBER D40 NUMBER UPDATE_SECS NUMBER B_UPDATE_SECS NUMBER B1 NUMBER B2 NUMBER B3 NUMBER B4 NUMBER B5 NUMBER B6 NUMBER B7 NUMBER B8 NUMBER B9 NUMBER B10 NUMBER DATE_ADDED DATE DATE_MODIFIED DATE UPDATED DATE ORIGINAL VARCHAR2(50) COUNT NUMBER(10) IPADDR VARCHAR2(16) HI NUMBER IM VARCHAR2(15) ST VARCHAR2(20) BS NUMBER USERID NUMBER(10) PAGE NUMBER URL VARCHAR2(150) DESCRIPTION VARCHAR2(100) TAG VARCHAR2(4) NH NUMBER REFRESH NUMBER POPULATE VARCHAR2(6) LERY VARCHAR2(1) LIST VARCHAR2(1) LITE VARCHAR2(1) STING_ID VARCHAR2(20) YN VARCHAR2(1) RY_ID VARCHAR2(9) RATED VARCHAR2(1) CREATED DATE In mysql +---+-+--+-+ ---+---+ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+ ---+---+ | id | varchar(50) | | PRI | | | | H0 | tinyint(3) unsigned | YES | | NULL | | | H1 | tinyint(3) unsigned | YES | | NULL | | | H2 | tinyint(3) unsigned | YES | | NULL | | | H3 | tinyint(3) unsigned | YES | | NULL | | | H4 | tinyint(3) unsigned | YES | | NULL | | | H5 | tinyint(3) unsigned | YES | | NULL | | | H6 | tinyint(3) unsigned | YES | | NULL | | | H7 | tinyint(3) unsigned | YES | | NULL | | | H8 | tinyint(3) unsigned | YES | | NULL | | | H9 | tinyint(3) unsigned | YES | | NULL | | | H10 | tinyint(3) unsigned | YES | | NULL | | | H11 | tinyint(3) unsigned | YES | | NULL | | | H12 | tinyint(3) unsigned | YES | | NULL | | | H13 | tinyint(3) unsigned | YES | | NULL | | | H14 | tinyint(3) unsigned | YES | | NULL | | | H15 | tinyint(3) unsigned | YES | | NULL | | | H16 | tinyint(3) unsigned | YES | | NULL | | | H17 | tinyint(3) unsigned | YES | | NULL | | | H18 | tinyint(3) unsigned | YES | | NULL | | | H19 | tinyint(3) unsigned | YES | | NULL | | | H20 | tinyint(3) unsigned | YES | | NULL | | | H21 | tinyint(3) unsigned | YES | | NULL | | | H22 | tinyint(3) unsigned | YES | | NULL | | | H23 | tinyint(3) unsigned | YES | | NULL | | | D1 | tinyint(4) | YES | | NULL | | | D2 | tinyint(4) | YES | | NULL | | | D3 | tinyint(4) | YES | | NULL | | | D4 | tinyint(4) | YES | | NULL | | | D5 | tinyint(4) | YES | | NULL | | | D6 | tinyint(4) | YES | | NULL | | | D7 | tinyint(4) | YES | | NULL | | | D8 | tinyint(4) | YES | | NULL | | | D9 | tinyint(4) | YES | | NULL | | | D10 | tinyint(4) | YES | | NULL | | | D11 | tinyint(4) | YES | | NULL | | | D12 | tinyint(4) | YES | | NULL | | | D13 | tinyint(4) | YES | | NULL | | | D14 | tinyint(4) | YES | | NULL | | | D15 | tinyint(4) | YES | | NULL | | | D16 | tinyint(4) | YES | | NULL | | | D17 | tinyint(4) | YES | | NULL | | | D18 | tinyint(4) | YES | | NULL | | | D19 | tinyint(4) | YES | | NULL | | | D20 | tinyint(4) | YES | | NULL | | | D21 | tinyint(4) | YES | | NULL | | | D22 | tinyint(4) | YES | | NULL | | | D23 | tinyint(4) | YES | | NULL | | | D24 | tinyint(4) | YES | | NULL | | | D25 | tinyint(4) | YES | | NULL | | | D26 | tinyint(4) | YES | | NULL | | | D27 | tinyint(4) | YES | | NULL | | | D28 | tinyint(4) | YES | | NULL | | | D29 | tinyint(4) | YES | | NULL | | | D30 | tinyint(4) | YES | | NULL | | | D31 | tinyint(4) | YES | | NULL | | | D32 | tinyint(4) | YES | | NULL | | | D33 | tinyint(4) | YES | | NULL | | | D34 | tinyint(4) | YES | | NULL | | | D35 | tinyint(4) | YES | | NULL | | | D36 | tinyint(4) | YES | | NULL | | | D37 | tinyint(4) | YES | | NULL | | | D38 | tinyint(4) | YES | | NULL | | | D39 | tinyint(4) | YES | | NULL | | | D40 | tinyint(4) | YES | | NULL | | | UPDATE_SECS | int(10
RE: Avg row length is varying a lot from oracle to MySQL
-Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 15:23 To: mysql@lists.mysql.com Subject: Avg row length is varying a lot from oracle to MySQL hi ,, we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. Probably do to the way the two database store data and how much space they reserve for specific column types. For a way to calculate row size see this link. Relize you must also calculate the size of all indexes. http://dev.mysql.com/doc/mysql/en/storage-requirements.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Avg row length is varying a lot from oracle to MySQL
Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. sujay -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 1:12 AM To: mysql@lists.mysql.com Subject: RE: Avg row length is varying a lot from oracle to MySQL -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 15:23 To: mysql@lists.mysql.com Subject: Avg row length is varying a lot from oracle to MySQL hi ,, we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. Probably do to the way the two database store data and how much space they reserve for specific column types. For a way to calculate row size see this link. Relize you must also calculate the size of all indexes. http://dev.mysql.com/doc/mysql/en/storage-requirements.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Avg row length is varying a lot from oracle to MySQL
Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. If you have a varchar(50) but usually only use 10 in those fields MySQL still counts the unused 40 for the total byte count of the row, so you must count them. An empty varchar(50) field still uses 50 bytes. Also, I believe text and blob fields are always counted as 255 bytes regardless of your settings. Ordered indexes are 10bytes per column (in the index) per row. I believe there is also some paging overhead so generally take your calculated row size and multiply by 1.1. sujay -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 1:12 AM To: mysql@lists.mysql.com Subject: RE: Avg row length is varying a lot from oracle to MySQL -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 15:23 To: mysql@lists.mysql.com Subject: Avg row length is varying a lot from oracle to MySQL hi ,, we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. Probably do to the way the two database store data and how much space they reserve for specific column types. For a way to calculate row size see this link. Relize you must also calculate the size of all indexes. http://dev.mysql.com/doc/mysql/en/storage-requirements.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connectin from ORACLE to MYSQL
Hi Friends, I have set up all the drivers and configured the Hetrogenous service in oracle and set all the relevent parameter. Now when i am connecting to MYSQL from ORACLE i am getting the below error select counter from ebay_stats@test where rownum 2 * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC][H006] The init parameter HS_FDS_SHAREABLE_NAME is not set. Please set it in initorasid.ora file. ORA-02063: preceding 2 lines from TEST I have set the HS_FDS_SHAREABLE_NAME also in the initmyodbc3.ora file located in $ORACLE_HOME/hs/admin path HS_FDS_CONNECT_INFO = myodbc3 HS_FDS_TRACE_LEVEL = off HS_FDS_TRACE_FILE_NAME = log file name HS_FDS_SHAREABLE_NAME = /usr/local/lib/libmyodbc3.so Please help me. regards anandkl
Re: connect from oracle to MYSQL.
2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc/odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r-3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect from oracle to MYSQL.
Hi Pooly, Thanks for the help. I did that , where to find odbc.ini file. This pkg does not create this file. regards anandkl On 9/21/05, Pooly [EMAIL PROTECTED] wrote: 2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc/odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r-3.51.06.so http://3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect from oracle to MYSQL.
Hi Pooly, I know your busy, but please help me, i need to submit this by today for the testing team. If you dont mind can you please guide to any url or documentation where i can find the complete steps to connect from oracle 8.1.7.4 http://8.1.7.4on sun 5.8 to mysql 4.1.14 on Lunix fedaro with innodb engine Thanks in advance. regards anandkl On 9/21/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Pooly, Thanks for the help. I did that , where to find odbc.ini file. This pkg does not create this file. regards anandkl On 9/21/05, Pooly [EMAIL PROTECTED] wrote: 2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc/odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r-3.51.06.so http://3.51.06.so/ http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
connect from oracle to MYSQL.
Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc/odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so libmyodbc3.so* # *ln -s libmyodbc3_r-3.51.06.so http://3.51.06.so libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl
Fwd: connect from oracle to MYSQL.
Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc/odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r-3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl
ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?
Good morning, I have scripts to create constraints, sequences, storage..., tables from ORACLE and I don't know if I can create them in MySQL? Appreciated your help, Thank you, Phong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?
Hi, I have scripts to create constraints, sequences, storage..., tables from ORACLE and I don't know if I can create them in MySQL? MySQL doesn't have sequences or storage specifiers. You cannot run your script directly. Depending on your current Oracle structure, you might want to give our Schema Migration tool in Database Workbench a try. Download a copy here: www.upscene.com With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development 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/[EMAIL PROTECTED]
Re: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?
Nguyen, Phong wrote: Good morning, I have scripts to create constraints, sequences, storage..., tables from ORACLE and I don't know if I can create them in MySQL? You can try our Migration Toolkit which has pretty good support for Oracle now... http://www.mysql.com/products/migration-toolkit/ One of the ways I like to use it is to reverse engineer the Oracle schema, and then have the MySQL schema definition script created for further modification. Note we do not have support for sequences, and its typical to just use AUTO_INCREMENT columns for these. Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?
I recomend to you FabForce DBDesigner 4.x with this tool you can do Reverse Engineering to a database in oracle, and then once created a model, export sql to mysql and that's it! I have made this to some databases in oracle, is really easy do it! Saludos! -Original Message- From: Nguyen, Phong [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 26, 2005 7:55 AM To: mysql@lists.mysql.com Subject: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..? Good morning, I have scripts to create constraints, sequences, storage..., tables from ORACLE and I don't know if I can create them in MySQL? Appreciated your help, Thank you, Phong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oracle to MySQl conversion
Good evening all, I've got an Oracle text dump from a client for conversion to MySQL. Before I dive into SED or Perl does anyone know of a script to convert the dump file? I did the dev site by hand, not much to it, the site is fairly simple. Mostly NUMBER to INT, VARCHAR2 to VARCHAR, and CLOB to BLOB. Just don't want to duplicate the effort. Thanks, DAve -- Dave Goodrich Systems Administrator http://www.tls.net Get rid of Unwanted Emails...get TLS Spam Blocker! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrating from Oracle to mySql [online stats]
First of all a kind salute to the master yoda of SQL query's Shawn Green :) He saved me a couple of hours the other day with a linking a table to itself query ! I've been in the Telecommunications industry for more than 2 years now. I hope that I can provide feedback any questions regarding DB knowledge/query's in this area for this list. Now to the point. I'm researching how to do another query that I 've in the Oracle DB on pl/SQL. The task is to provide a report based on the call origin. We get last 4 numbers of each call origin and from that we can determine where the call was originated (in spain: 6% is mobile, 913% is madrid, and so on). My mission is to create something that will use the less possible resources for each query. This is IMDTAB: ++---+--+++- ---++ | IMDFLL | IMD906| IMDTER | IMDTAR | IMDDUR | IMDORI | IMDCLI | ++---+--+++- ---++ | 2004-08-01 | 80xxx |913xx | R | 2.280 | 6277 | 01 | | 2004-08-01 | 80xxx |913xx | R | 2.650 | 6290 | 01 | | 2004-08-01 | 80xxx |913xx | R | 8.3166670 | 9710 | 01 | In Oracle using PL /Sql I 've a select that looks like this: SELECT GRUPO_ORI(IMDTAB.IMDORI) AS ORI, COUNT(IMDTAB.IMDORI) AS CLL FROM IMDTAB WHERE (IMDTAB.IMRCLI =01 AND (IMD906=80xxx )) AND (IMDTAB.IMDFLL = '@date1' ) GROUP BY IMDTAB.IMDCLI,GRUPO_ORI(IMDTAB.IMDORI) And this is the GRUPO_ORI function: FUNCTION GRUPO_ORI (ORI IN IMDTAB.IMDORI%TYPE) RETURN VARCHAR2 IS STRORI CLITAB.CLIPOB%TYPE; GRPORI CLITAB.CLIPOB%TYPE; BEGIN STRORI := To_Char(ORI); GRPORI := SUBSTR(STRORI,1,1); IF GRPORI = '6' THEN return GRPORI; END IF; GRPORI := SUBSTR(STRORI,1,2); IF GRPORI = '91' OR GRPORI = '93' THEN return GRPORI; END IF; GRPORI := SUBSTR(STRORI,1,3); IF GRPORI = '920' OR GRPORI = '921' OR GRPORI = '922' OR GRPORI = '923' OR GRPORI = '924' OR GRPORI = '925' OR GRPORI = '926' OR GRPORI = '927' OR GRPORI = '928' OR GRPORI = '941' OR GRPORI = '942' OR GRPORI = '943' OR GRPORI = '945' OR GRPORI = '947' OR GRPORI = '948' OR GRPORI = '949' OR GRPORI = '950' OR GRPORI = '953' OR GRPORI = '956' OR GRPORI = '957' OR GRPORI = '958' OR GRPORI = '959' OR GRPORI = '964' OR GRPORI = '967' OR GRPORI = '968' OR GRPORI = '969' OR GRPORI = '971' OR GRPORI = '972' OR GRPORI = '973' OR GRPORI = '974' OR GRPORI = '975' OR GRPORI = '976' OR GRPORI = '977' OR GRPORI = '978' OR GRPORI = '979' OR GRPORI = '980' OR GRPORI = '981' OR GRPORI = '982' OR GRPORI = '983' OR GRPORI = '986' OR GRPORI = '986' OR GRPORI = '987' OR GRPORI = '988' THEN return GRPORI; END IF; return 'Another'; END GRUPO_ORI; I was thinking in doing this by query, striping the IMDORI to 3 numbers, but I 've also as seen in the code copied above also 1 number, and 2 numbers zone identifiers. Also I can build a table with this instead of doing all the IF's, and join depending on the starting numbers, but looks rather complicated and I think the query might use a lot of DB power (and as described, I want to use the less possible resources taking in account this is a CALL BY CALL table with ***lots*** of rows) So what do you think guys is the best way to go ? Thanks is advance, MARTIN Statistics Department -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrating from Oracle to mySql [online stats]
Hi, I am also doing migration from oracle 7.3 to mysql 4.0 classic. May i know how to handle Oracle Views, stored procedures/triggers in MySQL 4.0 ?. May i know how to create database, table space, roll back segments, users, allocating tables to table spaces (as we do in oracle) in mysql 4.0 with out innodb?. Can we write into multiple tables in mysql 4.0?. If so how?. I have a backup to my database, after that i added some new columns to some tables and created one more new table. Now, i want to restore the old data without distrubing the new changes. Could you please help me how to do this in mysql?. Thanks, Narasimha -Original Message- From: martin fasani [mailto:[EMAIL PROTECTED] Sent: Tue 10/5/2004 4:32 PM To: [EMAIL PROTECTED] Cc: Subject: Migrating from Oracle to mySql [online stats] First of all a kind salute to the master yoda of SQL query's Shawn Green :) He saved me a couple of hours the other day with a linking a table to itself query ! I've been in the Telecommunications industry for more than 2 years now. I hope that I can provide feedback any questions regarding DB knowledge/query's in this area for this list. Now to the point. I'm researching how to do another query that I 've in the Oracle DB on pl/SQL. The task is to provide a report based on the call origin. We get last 4 numbers of each call origin and from that we can determine where the call was originated (in spain: 6% is mobile, 913% is madrid, and so on). My mission is to create something that will use the less possible resources for each query. This is IMDTAB: ++---+--+++- ---++ | IMDFLL | IMD906| IMDTER | IMDTAR | IMDDUR | IMDORI | IMDCLI | ++---+--+++- ---++ | 2004-08-01 | 80xxx |913xx | R | 2.280 | 6277 | 01 | | 2004-08-01 | 80xxx |913xx | R | 2.650 | 6290 | 01 | | 2004-08-01 | 80xxx |913xx | R | 8.3166670 | 9710 | 01 | In Oracle using PL /Sql I 've a select that looks like this: SELECT GRUPO_ORI(IMDTAB.IMDORI) AS ORI, COUNT(IMDTAB.IMDORI) AS CLL FROM IMDTAB WHERE (IMDTAB.IMRCLI =01 AND (IMD906=80xxx )) AND (IMDTAB.IMDFLL = '@date1' ) GROUP BY IMDTAB.IMDCLI,GRUPO_ORI(IMDTAB.IMDORI) And this is the GRUPO_ORI function: FUNCTION GRUPO_ORI (ORI IN IMDTAB.IMDORI%TYPE) RETURN VARCHAR2 IS STRORI CLITAB.CLIPOB%TYPE; GRPORI CLITAB.CLIPOB%TYPE; BEGIN STRORI := To_Char(ORI); GRPORI := SUBSTR(STRORI,1,1); IF GRPORI = '6' THEN return GRPORI; END IF; GRPORI := SUBSTR(STRORI,1,2); IF GRPORI = '91' OR GRPORI = '93' THEN return GRPORI; END IF; GRPORI := SUBSTR(STRORI,1,3); IF GRPORI = '920' OR GRPORI = '921' OR GRPORI = '922' OR GRPORI = '923' OR GRPORI = '924' OR GRPORI = '925' OR GRPORI = '926' OR GRPORI = '927' OR GRPORI = '928' OR GRPORI = '941' OR GRPORI = '942' OR GRPORI = '943' OR GRPORI = '945' OR GRPORI = '947' OR GRPORI = '948' OR GRPORI = '949' OR GRPORI = '950' OR GRPORI = '953' OR GRPORI = '956' OR GRPORI = '957' OR GRPORI = '958' OR GRPORI = '959' OR GRPORI = '964' OR GRPORI = '967' OR GRPORI = '968' OR GRPORI = '969' OR GRPORI = '971' OR GRPORI = '972' OR GRPORI = '973' OR GRPORI = '974' OR GRPORI = '975' OR GRPORI = '976' OR GRPORI = '977' OR GRPORI = '978' OR GRPORI = '979' OR GRPORI = '980' OR GRPORI = '981' OR GRPORI = '982' OR GRPORI = '983' OR GRPORI = '986' OR GRPORI = '986' OR GRPORI = '987' OR GRPORI = '988' THEN return GRPORI; END IF; return 'Another'; END GRUPO_ORI; I was thinking in doing this by query, striping the IMDORI to 3 numbers, but I 've also as seen in the code copied above also 1 number, and 2 numbers zone identifiers. Also I can build a table with this instead of doing all the IF's, and join depending on the starting numbers, but looks rather complicated and I think the query might use a lot of DB power (and as described, I want to use the less possible resources taking in account this is a CALL BY CALL table with ***lots*** of rows) So
RE: Migrating from Oracle to mySql [online stats]
Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 05 October 2004 16:03 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Migrating from Oracle to mySql [online stats] Hi, I am also doing migration from oracle 7.3 to mysql 4.0 classic. May i know how to handle Oracle Views, stored procedures/triggers in MySQL 4.0 ?. If I remember correctly Views don't appear till 8.0 so why do you need them? Any way the specific answer to your question is you can't. Views and stored procedures aren't handled by MySQl till 5.x - see earlier thread. May i know how to create database, table space, roll back segments, users, allocating tables to table spaces (as we do in oracle) in mysql 4.0 with out innodb?. Your best bet is 1) Read the MySQL manual and understand what it does do and what it doesn't 2) Get a copy of MySQLCC and install this on your PC. 3) Use MySQLCC to create the database and modify your Oracle scripts to create the tables. As there and no transactions without innodb you don't need to worry about rollback segments etc. Can we write into multiple tables in mysql 4.0?. If so how?. What do you mean? I have a backup to my database, after that i added some new columns to some tables and created one more new table. Now, i want to restore the old data without distrubing the new changes. Could you please help me how to do this in mysql?. Depends what format your data is stored in. ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrating from Oracle to mySql [online stats]
Hi Narasimha, I'm pretty new to mysql, only one year doing development in this platform. As far as I know you will not 've stored procedures/triggers in MySQL 4.0 .ç Please check the documentation and what is coming in version 5.0 (http://dev.mysql.com/doc/mysql/en/MySQL_5.0_Nutshell.html ) I have a backup to my database, after that i added some new columns to some tables and created one more new table. Now, i want to restore the old data without distrubing the new changes. Could you please help me how to do this in mysql?. Check mysqldump in the documentation. You can do the inserts without the create table information. I don't think you will 've any problem if you just added new columns. Good luck, MARTIN -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: martes, 05 de octubre de 2004 17:03 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Migrating from Oracle to mySql [online stats] Hi, I am also doing migration from oracle 7.3 to mysql 4.0 classic. May i know how to handle Oracle Views, stored procedures/triggers in MySQL 4.0 ?. May i know how to create database, table space, roll back segments, users, allocating tables to table spaces (as we do in oracle) in mysql 4.0 with out innodb?. Can we write into multiple tables in mysql 4.0?. If so how?. I have a backup to my database, after that i added some new columns to some tables and created one more new table. Now, i want to restore the old data without distrubing the new changes. Could you please help me how to do this in mysql?. Thanks, Narasimha -Original Message- From: martin fasani [mailto:[EMAIL PROTECTED] Sent: Tue 10/5/2004 4:32 PM To: [EMAIL PROTECTED] Cc: Subject: Migrating from Oracle to mySql [online stats] First of all a kind salute to the master yoda of SQL query's Shawn Green :) He saved me a couple of hours the other day with a linking a table to itself query ! I've been in the Telecommunications industry for more than 2 years now. I hope that I can provide feedback any questions regarding DB knowledge/query's in this area for this list. Now to the point. I'm researching how to do another query that I 've in the Oracle DB on pl/SQL. The task is to provide a report based on the call origin. We get last 4 numbers of each call origin and from that we can determine where the call was originated (in spain: 6% is mobile, 913% is madrid, and so on). My mission is to create something that will use the less possible resources for each query. This is IMDTAB: ++---+--+++- ---++ | IMDFLL | IMD906| IMDTER | IMDTAR | IMDDUR | IMDORI | IMDCLI | ++---+--+++- ---++ | 2004-08-01 | 80xxx |913xx | R | 2.280 | 6277 | 01 | | 2004-08-01 | 80xxx |913xx | R | 2.650 | 6290 | 01 | | 2004-08-01 | 80xxx |913xx | R | 8.3166670 | 9710 | 01 | In Oracle using PL /Sql I 've a select that looks like this: SELECT GRUPO_ORI(IMDTAB.IMDORI) AS ORI, COUNT(IMDTAB.IMDORI) AS CLL FROM IMDTAB WHERE (IMDTAB.IMRCLI =01 AND (IMD906=80xxx )) AND (IMDTAB.IMDFLL = '@date1' ) GROUP BY IMDTAB.IMDCLI,GRUPO_ORI(IMDTAB.IMDORI) And this is the GRUPO_ORI function: FUNCTION GRUPO_ORI (ORI IN IMDTAB.IMDORI%TYPE) RETURN VARCHAR2 IS STRORI CLITAB.CLIPOB%TYPE; GRPORI CLITAB.CLIPOB%TYPE; BEGIN STRORI := To_Char(ORI); GRPORI := SUBSTR(STRORI,1,1); IF GRPORI = '6' THEN return GRPORI; END IF; GRPORI := SUBSTR(STRORI,1,2); IF GRPORI = '91' OR GRPORI = '93' THEN return GRPORI; END IF; GRPORI := SUBSTR(STRORI,1,3); IF GRPORI = '920' OR GRPORI = '921' OR GRPORI = '922' OR GRPORI = '923' OR GRPORI = '924' OR GRPORI = '925' OR GRPORI = '926' OR GRPORI = '927' OR GRPORI = '928' OR GRPORI = '941' OR GRPORI = '942' OR GRPORI = '943' OR GRPORI = '945' OR GRPORI = '947' OR GRPORI = '948' OR GRPORI = '949' OR GRPORI = '950' OR GRPORI = '953' OR GRPORI = '956' OR GRPORI = '957' OR GRPORI = '958' OR GRPORI = '959' OR GRPORI = '964' OR GRPORI = '967' OR GRPORI = '968' OR GRPORI = '969' OR GRPORI = '971' OR GRPORI = '972' OR GRPORI = '973' OR GRPORI = '974' OR GRPORI = '975' OR GRPORI = '976
Oracle 2 MySQL updates/replication?
Hello, I found a question about Oracle 2 MySQL replication in the archive on Sep. 2001 but no mention since? We have a department using Oracle 8.1.7 and I'm running MySQL 4.0 and neither of us wants to change :-) I could call a Perl, C++ or Java program from cron to periodically update the MySQL instance from Oracle but was hoping to use a trigger/stored procedure to initiate the update so it seems more real time. Does this seem possible? Of course it may turn out non-trivial to write the synchronization code so I'll take suggestions on that front also. Thanks for any ideas, -Carl Edwards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle 2 MySQL updates/replication?
On Tue, Jul 13, 2004 at 06:11:22PM -0700, Carl Edwards wrote: Hello, I found a question about Oracle 2 MySQL replication in the archive on Sep. 2001 but no mention since? We have a department using Oracle 8.1.7 and I'm running MySQL 4.0 and neither of us wants to change :-) I could call a Perl, C++ or Java program from cron to periodically update the MySQL instance from Oracle but was hoping to use a trigger/stored procedure to initiate the update so it seems more real time. Does this seem possible? Of course it may turn out non-trivial to write the synchronization code so I'll take suggestions on that front also. Golden Gate Software makes a product that does this. I'd have a look at what they offer. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle 2 MySQL updates/replication?
An option would be a log reader program that uses Oracle log miner to only show commited transactions from the redo logs. You could then replay the SQL that is being executed on the oracle box on the mysql server as long as the tables are defined the same. 9i has an enhanced log miner that can be used to read 8i redo logs as well, so you might want to use the newer 9i client if you go this way. Updates to the oracle database could be processed the same way using the mysql binary log as long as no mysql extensions were used like inserting multiple rows with a single insert statement. --- Jeremy Zawodny [EMAIL PROTECTED] wrote: On Tue, Jul 13, 2004 at 06:11:22PM -0700, Carl Edwards wrote: Hello, I found a question about Oracle 2 MySQL replication in the archive on Sep. 2001 but no mention since? We have a department using Oracle 8.1.7 and I'm running MySQL 4.0 and neither of us wants to change :-) I could call a Perl, C++ or Java program from cron to periodically update the MySQL instance from Oracle but was hoping to use a trigger/stored procedure to initiate the update so it seems more real time. Does this seem possible? Of course it may turn out non-trivial to write the synchronization code so I'll take suggestions on that front also. Golden Gate Software makes a product that does this. I'd have a look at what they offer. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Port SQL Query from Oracle to MySQL
Hello.
I've got a tricky problem on my hands. It seems very easy, but I can't figure it out
anyway,
and I've already spent quite some time trying to solve it! I'm doing the whole thing
under
MySQL 4.0, which seems to be the real problem here! It doesn't support my query I
wrote! Well, I didn't run the query, but I know it'd never work!
Anyway, I'll get to the point. Here's a very simplified version of the tables I'm
working on.
Car
-
| ID | IDBrand | IDModel | Year | Price |
-
Characteristic
| ID | Description |
CarCharacteristics
| IDCar | IDCharac |
These tables can be described the following way:
Car
* contains cars
Characteristic
* contains various characteristics that can be applied to a car
- A/C, Winter Tyres, Electric Windows, ABS Brakes, etc.
CarCharacteristics
* indicates what characteristics the various cars actually have.
What I'd like to do, is to find which cars have a specific SET of characteristics. I'd
like to
be able to say which cars have the Characteristics 'X', 'Y' and 'Z' in ONE query. I'm
using
PHP to run the queries, but that isn't all that important, as I intend to let all the
processing
being done by the DBMS, or, alternatively, run various queries only passing the
resulting
identifiers directly in the new query.
For now, the only real implementation I have is an OR query, that is, I can find which
Cars have AT LEAST ONE of the characteristics in the desired set.
I'm capable of solving the problem with N queries for each car, but this kind of
solution
seems very time consuming for a database with even as little as a few thousands of
cars
with around a dozen, or more, characteristics each.
I believe that the following query would solve my problem, but unfortunately it's not
supported by MySQL MyISAM tables, and there are a couple of things about the query
that I don't know how to translate into a valid MySQL Query. I think that this query
would run just fine on an Oracle or DB2 system (maybe changing MINUS with EXCEPT,
but that's all).
SELECT A.*
FROM car as A
WHERE (
(
SELECT B.ID
FROM Characteristic AS B
WHERE B.ID IN('X','Y','Z')
MINUS
SELECT C.IDCharac
FROM CarCharacteristics AS C
WHERE C.IDCar=A.ID
) IS NULL
);
I appreciate any help in advance!
Remi Mikalsen
PS. I'm sorry if tables and other aligned elements don't appear correctly!
--
Remi Mikalsen
E-Mail: [EMAIL PROTECTED]
URL:http://www.iMikalsen.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Port SQL Query from Oracle to MySQL
Remi Mikalsen wrote: Car - | ID | IDBrand | IDModel | Year | Price | - Characteristic | ID | Description | CarCharacteristics | IDCar | IDCharac | What I'd like to do, is to find which cars have a specific SET of characteristics. I'd like to be able to say which cars have the Characteristics 'X', 'Y' and 'Z' in ONE query. http://lists.mysql.com/mysql/157925 Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORACLE to MySQL migration
You have a number of options, but to name a couple you could: .. Use a tool called DBScriptor to export schema/data out of Oracle and with a few tweaks it can generate MySQL DDL/insert statements. .. Use an ODBC pump tool like those included with the Borland tools like Delphi, or like the EMS DataPump tool. Have fun! :-) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 16, 2004 9:48 AM To: [EMAIL PROTECTED] Subject: ORACLE to MySQL migration Hello colleagues, I have to migrate a lot of ORACLE 8.1.7 databases to MySQL 3.23 I'm looking for a tool or for an algorithm which fully supports any ORACLE structures ... If needed I may use a commercial tool, otherwise a free software / shared software should be very appreciated Can anyone give me some suggestions? thanks a lot Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
ORACLE to MySQL migration
Hello colleagues, I have to migrate a lot of ORACLE 8.1.7 databases to MySQL 3.23 I'm looking for a tool or for an algorithm which fully supports any ORACLE structures ... If needed I may use a commercial tool, otherwise a free software / shared software should be very appreciated Can anyone give me some suggestions? thanks a lot Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORACLE to MySQL migration
Try SQLyog - http://www.webyog.com/sqlyog Karam --- [EMAIL PROTECTED] wrote: Hello colleagues, I have to migrate a lot of ORACLE 8.1.7 databases to MySQL 3.23 I'm looking for a tool or for an algorithm which fully supports any ORACLE structures ... If needed I may use a commercial tool, otherwise a free software / shared software should be very appreciated Can anyone give me some suggestions? thanks a lot Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORACLE to MySQL migration
Perl/DBI is a possibility. --Walt -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 16, 2004 8:48 AM To: [EMAIL PROTECTED] Subject: ORACLE to MySQL migration Hello colleagues, I have to migrate a lot of ORACLE 8.1.7 databases to MySQL 3.23 I'm looking for a tool or for an algorithm which fully supports any ORACLE structures ... If needed I may use a commercial tool, otherwise a free software / shared software should be very appreciated Can anyone give me some suggestions? thanks a lot Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
oracle to mysql
Does anyone know any FREE tool to migrate Oracle databases and schema to MySQL 5.0? I know one such tool, Oracle-to-mysql which is not free though. Thanks Raza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: oracle to mysql
SQLyog - www.webyog.com is NOT FREE but has a very good ODBC import tool for $49 and it includes lot of other poerful tools. You might check it out. You can try it out for 30days before purchasing it. karam --- Ansari, Raza (GEI, GEFA) [EMAIL PROTECTED] wrote: Does anyone know any FREE tool to migrate Oracle databases and schema to MySQL 5.0? I know one such tool, Oracle-to-mysql which is not free though. Thanks Raza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: oracle y mysql
I have a connection problem about MySql vs Oracle, I put an enter in TnsNames of BBDD Mysql, using the object Oracle HS, and I get this error TNS Packet checksum transport, Can you help me?
Oracle to MySQL or ?
I have searched the archives but have not found an answer to this. Our company is moving from Oracle and is in a determination phase as to its replacement. (Platforms are Windows and Solaris, and better be Linux soon, too.) 1. The primary question/issue here is that with the complexity of our databases, Oracle's Intermedia was/is very necessary. IBM evidently offers a similar feature in its db2, which is a frontrunner for our database/RnD people here. How about MySQL? Is there a similar feature/method to InterMedia? Our RnD dba says we don't want to perform the indexing ourselves. That isn't written in stone, though. Myself, I am a SysAdmin here and not a dba, but am involved in this selection process. I realize this is a small amount of information, but I need to see if we can get around this issue. If more info is needed, I'll gladly give. 2. Secondly, I have seen scripts for migrating from MySQL to Oracle. Is it possible to export an Oracle db to MySQL for even a simple functionality test? (This idea is kind of being dismissed because of the information Oracle includes in its exports, but I'm asking here in case, as I would think you could query and obtain the data as you need). Thanks in advance! If I can be pointed to whitepapers and pertinent links that can help, please do! Ken Rice -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle to MySQL or ?
I have searched the archives but have not found an answer to this. My searches seem to come up dry the first time around lately, too. Our company is moving from Oracle and is in a determination phase as to its replacement. (Platforms are Windows and Solaris, and better be Linux soon, too.) 1. The primary question/issue here is that with the complexity of our databases, Oracle's Intermedia was/is very necessary. IBM evidently offers a similar feature in its db2, which is a frontrunner for our database/RnD people here. Intermedia. Hmm. I guess I could look it up. http://www.google.com/search?hl=enie=ISO-8859-1q=oracle+intermedia http://www.oracle.com/ip/deploy/database/oracle9i/index.html?cm_imedia.html Okay, it looks like a set of tools for managing multimedia databases. Could you explain how you use intermedia? ... -- Joel Rees [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The dreaded move from Oracle to MySQL
Hi The Oracle export function, exp, does not export data in a clean file format, and whilst it may be possible to tidy the output to load into another database, you'd do better writing a perl script to dump the tables required to disk. The time factors in moving data from db1 on one machine to db2 on another are problematic. The options are endless. The best option is to produce a file and use the LOAD INFILE method. This is the quickest way to get data into MySQL. Inserting individual rows will take to long. Simon On Wednesday 20 Nov 2002 11:00 pm, Andrew Braithwaite wrote: Wesley, Forgive me because I'm not overly familiar with Oracle, and this is an unproven idea If you can run the oracle export function from unix shell, you could do something like the following -- Install the mysql client on the oracle db server (will be quick and won't need any downtime) -- grant permissions on the new mysql server to allow the newly installed remote client access to it -- make sure the mysql client on the oracle server can write to the new mysql db ok -- create all the required tables on the new mysql server's db (leaving out indicies if you want to gain performance) -- In a script (using variables and loops), run something like the following from shell on the oracle server (where t is the table name): [shell]# Oraclexportutility --tabdelim t t; mysqlimport multimaps t;sleep 5;rm -f t (where Oraclexportutility --tabdelim t is the oracle export widget that I know nothing about!!) I know that the same method works with: [shell]# mysql -B -N --execute=select * from t multimaps t; mysqlimport multimaps t;sleep 5;rm -f t If you can find a way to pipe the output straight to the mysql import utility without writing a copy to disk then that's even better! (I drew a blank at this one..) Also, if your tables are small enough to use a ramdisk for the temp table t then that's good too. You could run several of these occurences at once (although it would hammer your disks) to make the most of the bandwith available. There are lots of options to mysqlimport such as: -C, --compress Use compression in server/client protocol. -f, --force Continue even if we get an sql-error. -i, --ignoreIf duplicate unique key was found, keep old row. -r, --replace If duplicate unique key was found, replace old row. And (I quote) If one uses sockets to connect to the MySQL server, the server will open and read the text file directly. As I said, these are ideas I hope it will at least give you some ideas too... Cheers, Andrew -Original Message- From: Wesley Brown [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 20 November 2002 20:24 To: Simon Windsor; [EMAIL PROTECTED] Subject: RE: The dreaded move from Oracle to MySQL Well, it looks like Simon has the best possible answer so far. I did a test dump into a delimited file from Oracle and imported that using the LOAD command in mysql and sustaned about 11,000 inserts a second. This is with no indexes on the mysql tables at all. I have also tried using the dbtools import function but it will ether time out or just hang on the big tables. --Simon Says To do this I would do the following, - on Oracle server - - Create a NFS device - - Create a FIFO pipe, using mknod - - export data from Oracle to FIFO pipe - on MySQL server - - Mount remote NFS device - - Use MySQL Load command to load data, or write your own loader in perl - - when load is finished, build the MySQL indexes Can you explain this procedure to me a little better or point me to something I can read? I still have to convince the person who manages the Oracle box to open up an NFS share. Another way we could increase load times would be to use 4 scripts to load data from 4 tables at once. We have the bandwith to handle that and the MySQL box seems to be handling request just fine at 11,000 inserts its still not tapping out both procs or killing the disk I/O. Thanks to everyone who responded so quickly! Cheers, Wes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Simon Windsor mail: [EMAIL PROTECTED] tel: 01454 617689 mob: 07720 447385
RE: The dreaded move from Oracle to MySQL
i'm running an oracle enterprise server in a test environment for corereader, and i've noticed that, although oracle sometimes takes a while to wake up, after you have its attention, it throws data at you very fast. sometimes a developer does not use connections properly. in your case, i would create a single connection and keep it open for the duration of the 45 million record move. Currently I open a connection and keep it open during the table move only. After the whole table is moved it destroys the object and checks to see if there is another table running. I wrote the app to spawn up to 10 clients one pIII550 w/256meg ram can handle 2 clients due to the large overhead. What I am seeing is on the very large tables, we have really three or four tables that make up the bulk of all the data, the connection eventually times out or has an error if the server has any kind of other load on it. records. instead, i would ask oracle for the biggest record set that the infrastructure can handle. it will come back to you very fast. The problem is the production machine is old and weak I had them beef it up to two whole gigs of ram this thing at idle sits at a 2 load rating or better. log into a local disk file. if the process crashes, you pick up from where it went down. I have written some error checking into the app including error logging but I don't want to spend another week writing an app just to move data and test. The load will only go as fast as the largest table in the Oracle database with 10 loaders the other tables get chewed through pretty quick transaction logging going on. There is no indexes on the mysql box and no logging of selects or the like on the oracle side. glad to hear that you had no errors before, but be careful of oracle's data typing. Thats part of what makes the app slow I have very strict data typing and conversions happening on very large text fields. additional boxes. run all of them simultaneously against the servers. they'll bump into each if you run multiple apps, increase the query timeout of all of the connections. that's the way that i would do it. Lol, that is the way I did it. I may finish the app in general it will move data from ms-sql, oracle, and my-sql into ms-sql, mysql, or a flat file. I though about setting up a couple of REAL beefy boxes with a couple of gig of ram a piece and have them store the recordset in a disconnected method, so once oracle is done tossing records its out of the loop completly. Right now using LOAD has been by far the fastest method multiple dataloaders has only yeilded about 2000 records a second and LOAD does almost 12k even on the big big tables it doesn't slow down to below 5 or 6k. Cheers, Wes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: The dreaded move from Oracle to MySQL
: ) excellent. i envy you. i'd noticed the same thing about oracle. seems to be a resource hog. the only thing worse that i've seen is ms access. they make mysql look very efficient. hate to say much, since it appears that you know what you're doing, but you might also take a look at rdo. it has an extremely small footprint within the infrastructure compared to ado. if you do, you may want to refer to the oracle notes in the corereader documentation (and oracle feedback outside this list would be appreciated). i'm running an oracle enterprise server in a test environment for corereader, and i've noticed that, although oracle sometimes takes a while to wake up, after you have its attention, it throws data at you very fast. sometimes a developer does not use connections properly. in your case, i would create a single connection and keep it open for the duration of the 45 million record move. Currently I open a connection and keep it open during the table move only. After the whole table is moved it destroys the object and checks to see if there is another table running. I wrote the app to spawn up to 10 clients one pIII550 w/256meg ram can handle 2 clients due to the large overhead. What I am seeing is on the very large tables, we have really three or four tables that make up the bulk of all the data, the connection eventually times out or has an error if the server has any kind of other load on it. records. instead, i would ask oracle for the biggest record set that the infrastructure can handle. it will come back to you very fast. The problem is the production machine is old and weak I had them beef it up to two whole gigs of ram this thing at idle sits at a 2 load rating or better. log into a local disk file. if the process crashes, you pick up from where it went down. I have written some error checking into the app including error logging but I don't want to spend another week writing an app just to move data and test. The load will only go as fast as the largest table in the Oracle database with 10 loaders the other tables get chewed through pretty quick transaction logging going on. There is no indexes on the mysql box and no logging of selects or the like on the oracle side. glad to hear that you had no errors before, but be careful of oracle's data typing. Thats part of what makes the app slow I have very strict data typing and conversions happening on very large text fields. additional boxes. run all of them simultaneously against the servers. they'll bump into each if you run multiple apps, increase the query timeout of all of the connections. that's the way that i would do it. Lol, that is the way I did it. I may finish the app in general it will move data from ms-sql, oracle, and my-sql into ms-sql, mysql, or a flat file. I though about setting up a couple of REAL beefy boxes with a couple of gig of ram a piece and have them store the recordset in a disconnected method, so once oracle is done tossing records its out of the loop completly. Right now using LOAD has been by far the fastest method multiple dataloaders has only yeilded about 2000 records a second and LOAD does almost 12k even on the big big tables it doesn't slow down to below 5 or 6k. Cheers, Wes -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
The dreaded move from Oracle to MySQL
Well, This is my first attempt at moving from Oracle to MySQL on such a large scale and so far it isn't going well at all. We are running oracle 7.3.4.0.1 and MySQL 4.0.4-0 standard. We are moving 101 tables with a total of 45 millon records over. I have tried several diffrent methods including using a MS-SQL server to act as go between using DTS services. We have run into two problems, one, we don't get all the data over we loose rows and the like. Two it is super slow on the order of 18 to 30 hours to do a pull. We need a way to move the data in a resonable amount of time around 8 hours is the goal. Things I have tried so far include using MS-SQL as a go between fast but with data loss, using a custom ADO application to move the data and handle any transforms we get the data but it REAL slow, and trying to get the oracledump.pl perl script to run with no success. If anyone has had a chance to do this type of migration please point me in the right direction. I would really hate to stay with Oracle for the only reason that we can't get the data off of it and on to another box :) Thanks, Wes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The dreaded move from Oracle to MySQL
Scott Pippin [EMAIL PROTECTED] Wesley Brown [EMAIL PROTECTED] 11/20/02 11:24AM Well, This is my first attempt at moving from Oracle to MySQL on such a large scale and so far it isn't going well at all. We are running oracle 7.3.4.0.1 and MySQL 4.0.4-0 standard. We are moving 101 tables with a total of 45 millon records over. I have tried several diffrent methods including using a MS-SQL server to act as go between using DTS services. We have run into two problems, one, we don't get all the data over we loose rows and the like. Two it is super slow on the order of 18 to 30 hours to do a pull. We need a way to move the data in a resonable amount of time around 8 hours is the goal. Things I have tried so far include using MS-SQL as a go between fast but with data loss, using a custom ADO application to move the data and handle any transforms we get the data but it REAL slow, and trying to get the oracledump.pl perl script to run with no success. If anyone has had a chance to do this type of migration please point me in the right direction. I would really hate to stay with Oracle for the only reason that we can't get the data off of it and on to another box :) Thanks, Wes Oracle has an export utility. Did you try that? We ran in to the same problems exporting our data from oracle to mysql. We used PowerBuilder(it has a data-pipeline feature under its database menu). This allowed us to connect through ODBC and create the table then import the data. Unfortunately we had to do this for each table. I hope this gives you a few more ideas. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The dreaded move from Oracle to MySQL
Hi I have moved huge amounts of data around in the past, from Oracle to Oracle, and Oracle to MySQL. The problems to consider are - export speed - load speed It can take hours to download a large database, nevermind re-loading it into another database. It is usually best to export the data into a simple file that can be read whilst you are writing to it. Load speed is tricky. Do not have any indexes. Use the MySQL LOAD command, it is fast. To do this I would do the following, - on Oracle server - - Create a NFS device - - Create a FIFO pipe, using mknod - - export data from Oracle to FIFO pipe - on MySQL server - - Mount remote NFS device - - Use MySQL Load command to load data, or write your own loader in perl - - when load is finished, build the MySQL indexes I hope that this helps Simon On Wednesday 20 Nov 2002 6:24 pm, Wesley Brown wrote: Well, This is my first attempt at moving from Oracle to MySQL on such a large scale and so far it isn't going well at all. We are running oracle 7.3.4.0.1 and MySQL 4.0.4-0 standard. We are moving 101 tables with a total of 45 millon records over. I have tried several diffrent methods including using a MS-SQL server to act as go between using DTS services. We have run into two problems, one, we don't get all the data over we loose rows and the like. Two it is super slow on the order of 18 to 30 hours to do a pull. We need a way to move the data in a resonable amount of time around 8 hours is the goal. Things I have tried so far include using MS-SQL as a go between fast but with data loss, using a custom ADO application to move the data and handle any transforms we get the data but it REAL slow, and trying to get the oracledump.pl perl script to run with no success. If anyone has had a chance to do this type of migration please point me in the right direction. I would really hate to stay with Oracle for the only reason that we can't get the data off of it and on to another box :) Thanks, Wes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Simon Windsor mail: [EMAIL PROTECTED] tel: 01454 617689 mob: 07720 447385 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
