50 things to know before migrating from Oracle to MySQL

--=_32ce61706a64942b8048367d077df27b
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="UTF-8"

Hi folks,

Read a blog which states 50 things to know before migrating from Oracle to MySQL. Any comments on this?

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

Thanks in advance,
Chang


--=_32ce61706a64942b8048367d077df27b--

Re: 50 things to know before migrating from Oracle to MySQL

--005045013d2e620ac2047e3923b0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

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 wrote:

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


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

--005045013d2e620ac2047e3923b0--

Re: 50 things to know before migrating from Oracle to MySQL

changuno wrote:
> Hi folks,
>=20
> Read a blog which states 50 things to know before migrating from Or=
acle to MySQL. Any comments on this?
>=20
> ... list snipped ...

MySQL was never designed to be a clone of Oracle (the database). We=
=20
have distinct differences in design and implementation that make us a=
=20
wonderful product to use as a "general purpose database".

I agree with the other respondent: If your project cannot possibly=
=20
function without one or more of the features that MySQL does not=20
provide, then don't use it.

However, our feature set has been and continues to be complete and=
=20
powerful enough to be the storage engine behind some of the internet'=
s=20
most popular and heavily visited websites. I can see reasons why some=
of=20
the feature differences (aka overhead) may be useful in certain use=
=20
cases. However there is a long history of popular usage that indicate=
s=20
that not everyone, or every project, requires the full set of feature=
s=20
you describe:
http://www.mysql.com/customers/

I agree with some of the points you make and we are working to implem=
ent=20
some of the features you mentioned. On the other hand some of those=
=20
"deficiencies" that you mention are specific strengths of the MySQL s=
ystem:

23. There are fewer and less sophisticated tools for administration.

MySQL doesn't need them. That alone should tell you something about o=
ur=20
reliability.

24. There is no IDE and debugger that approaches the level of=20
sophistication you may be accustomed to. Youâ€=99ll probably be w=
riting your=20
stored procedures in a text editor and debugging them by adding=20
statements that insert rows into a table called debug_log.

Again, this is an indication that you don't *need* complex tools or a=
=20
GUI to work with MySQL. The simple solution is often the better=20
solution. It also allows you to develop for your server from practica=
lly=20
anywhere, not just a machine where your GUI tools are installed.

25. Each table can have a different storage backend (â€=9Dstorage=
engineâ€=9D).

Yes, we absolutely allow this.

Each engine brings a certain strength to the storage and retrieval=
=20
solutions you can create with MySQL. We explicitly recognize that the=
re=20
is no "one size fits all" approach that meets the needs of every=20
problem. This also allows for special-purpose solutions to be integra=
ted=20
into MySQL:
http://solutions.mysql.com/solutions/?type=3D29


28. The default storage engine is non-transactional and corrupts easi=
ly.

True: MyISAM is does not require the disk and CPU overhead of trackin=
g=20
changes transactionally. False: In my experience (I do work for Suppo=
rt)=20
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 cub=
e,=20
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=
=20
join more than 61 tables into the same query? If you do, I propose th=
at=20
you need to revisit your schema design choices or review how you writ=
e=20
your queries. In this case, I think we are discouraging bad practices=
..

39. MySQL supports a smaller subset of SQL syntax. There are no=20
recursive queries, common table expressions, or windowing functions.=
=20
There are a few extensions to SQL that are somewhat analogous to MERG=
E=20
and similar features, but are very simplistic in comparison.

Again, the vast majority of data storage and retrieval activities do =
not=20
require these features. If you absolutely cannot function without the=
m,=20
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 ass=
ume=20
to understand your business processes nor do we want you to design yo=
ur=20
process to support our procedures. The failover process is yours to=
=20
design and implement as you see fit.

45. Replication is asynchronous and has many limitations and edge cas=
es.=20
For example, it is single-threaded, so a powerful slave can find it h=
ard=20
to replicate fast enough to keep up with a less powerful master.

Yes, it is asynchronous. This is a distinct advantage to many read-he=
avy=20
applications and it allows MySQL to scale out better than most, if =
not=20
all, other RDBMS systems.

http://www.mysql.com/why-mysql/white-papers/mysql_wp_scaleou t.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 hav=
e=20
the overhead of two ways of performing essentially the same function?=
=20
This is just one less way to confuse your design.

--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg

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_scaleou t.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org

RE: 50 things to know before migrating from Oracle to MySQL

> -----Original Message-----
> From: John Meyer [mailto:johnmeyer@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=gcdmg-mysql-2@m.gmane.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-befor e-migrating-oracle-to-mysql/

Man, I love Google.

Thanks,

Carl
----- Original Message -----
From: "Daevid Vincent"
To:
Cc: "'changuno '"
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:johnmeyer@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=carl@etrak-plus.com
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: 50 things to know before migrating from Oracle to MySQL

--001636416c1f7ce54c047e41dc37
Content-Type: text/plain; charset=ISO-8859-1

Doesn't Google run MySQL ?

Hmmm....


On Thu, Jan 28, 2010 at 4:56 PM, Carl wrote:

> A quick Google turned up
>
>
> http://www.xaprb.com/blog/2009/03/13/50-things-to-know-befor e-migrating-oracle-to-mysql/
>
> Man, I love Google.
>
> Thanks,
>
> Carl
> ----- Original Message ----- From: "Daevid Vincent"
>
> To:
> Cc: "'changuno '"
> 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:johnmeyer@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=carl@etrak-plus.com
>>
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001636416c1f7ce54c047e41dc37--

Re: 50 things to know before migrating from Oracle to MySQL

On 1/28/10 5: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?

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=gcdmg-mysql-2@m.gmane.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"
To:
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-befor e-migrating-oracle-to-mysql/
>
> Man, I love Google.
>
> Thanks,
>
> Carl
> ----- Original Message -----
> From: "Daevid Vincent"
> To:
> Cc: "'changuno '"
> 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:johnmeyer@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=carl@etrak-plus.com
>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=jheim@math.wisc.edu
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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! :)


=2D-=20
J=F8rn 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=3Dgcdmg-mysql-2@m.gmane.o rg

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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org

Re: 50 things to know before migrating from Oracle to MySQL

On 1/29/10 5:03 PM, "mos" 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=gcdmg-mysql-2@m.gmane.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"
To: "mos" ;
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" 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=carl@etrak-plus.com
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.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.

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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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
- mdykman@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=gcdmg-mysql-2@m.gmane.org