[crazy123]

What are the main differences between MySQL and ORACLE?

[mccabec123]

One of the main differences is that MySQL claims to be several times faster at executing certain queries, I've never used Oracle, so I'm going by what the MySQL manual says. It does this by using different engines etc, although they use the same syntax which is SQL, a fourth generation scripting language.

This post has been edited by mccabec123: 17 June 2012 - 03:17 PM

[crazy123]

mccabec123, on 17 June 2012 - 03:14 PM, said:

One of the main differences is that MySQL claims to be several times faster at executing certain queries, I've never used Oracle, so I'm going by what the MySQL manual says. It does this by using different engines etc, although they use the same syntax which is SQL, a fourth generation scripting language.

I also use MySQL. The instructions in ORACLE also is in SQL?
ORACLE has no advantage over MySQL?

[mccabec123]

crazy123, on 17 June 2012 - 03:52 PM, said:

mccabec123, on 17 June 2012 - 03:14 PM, said:

One of the main differences is that MySQL claims to be several times faster at executing certain queries, I've never used Oracle, so I'm going by what the MySQL manual says. It does this by using different engines etc, although they use the same syntax which is SQL, a fourth generation scripting language.

I also use MySQL. The instructions in ORACLE also is in SQL?
ORACLE has no advantage over MySQL?

As far as I'm aware, most database engines use SQL, it just stands for Structured Query Language. Not sure of any benefits that Oracle has to offer, just that MySQL claims that it's faster than Oracle. Oracle may have better features or what not, but when it comes down to it, speed is king, you'd much rather have the fastest.

[crazy123]

mccabec123, on 17 June 2012 - 04:19 PM, said:

crazy123, on 17 June 2012 - 03:52 PM, said:

mccabec123, on 17 June 2012 - 03:14 PM, said:

One of the main differences is that MySQL claims to be several times faster at executing certain queries, I've never used Oracle, so I'm going by what the MySQL manual says. It does this by using different engines etc, although they use the same syntax which is SQL, a fourth generation scripting language.

I also use MySQL. The instructions in ORACLE also is in SQL?
ORACLE has no advantage over MySQL?

As far as I'm aware, most database engines use SQL, it just stands for Structured Query Language. Not sure of any benefits that Oracle has to offer, just that MySQL claims that it's faster than Oracle. Oracle may have better features or what not, but when it comes down to it, speed is king, you'd much rather have the fastest.

OK. It is the most common database used is Oracle atualemente. Right?
Why? What are the advantages?

[macosxnerd101]

Have you done any research yourself on this?

I did a quick Google search and came up with these links:
https://blogs.oracle...s_functionality
http://www.oracle.co...ons-066501.html
http://en.wikipedia....agement_systems

[baavgai]

mccabec123, on 17 June 2012 - 06:14 PM, said:

One of the main differences is that MySQL claims to be several times faster at executing certain queries

This is BS.

Oracle is a huge database infrastructure. Oracle talks casually about handling petabytes of data. Data warehouse farms across international WANs, that sort of thing. There are people whose sole employment is maintaining Oracle databases. I'm an Oracle DBA for a small company, so I have other jobs. But I've met the people who just do the database. As part of a department of like people. Big big companies spend billions of dollars supporting Oracle.

MySQL is a far more basic beast. Like Oracle, it is a SQL based RDBMS. However, it will not scale to the mega sizes Oracle does. ( To be fair, MS SQL doesn't either. ) This is really all the database most web sites need. Even a company with a few thousand employees probably only needs MySQL for most of their applications.

If your query is simple and involves a few thousand rows, it's possible MySQL will beat Oracle. This is a function of overhead. If your query is complex or a few million rows, I believe Oracle will beat MySQL bloody.

[crazy123]

baavgai, on 18 June 2012 - 05:03 AM, said:

mccabec123, on 17 June 2012 - 06:14 PM, said:

One of the main differences is that MySQL claims to be several times faster at executing certain queries

This is BS.

Oracle is a huge database infrastructure. Oracle talks casually about handling petabytes of data. Data warehouse farms across international WANs, that sort of thing. There are people whose sole employment is maintaining Oracle databases. I'm an Oracle DBA for a small company, so I have other jobs. But I've met the people who just do the database. As part of a department of like people. Big big companies spend billions of dollars supporting Oracle.

MySQL is a far more basic beast. Like Oracle, it is a SQL based RDBMS. However, it will not scale to the mega sizes Oracle does. ( To be fair, MS SQL doesn't either. ) This is really all the database most web sites need. Even a company with a few thousand employees probably only needs MySQL for most of their applications.

If your query is simple and involves a few thousand rows, it's possible MySQL will beat Oracle. This is a function of overhead. If your query is complex or a few million rows, I believe Oracle will beat MySQL bloody.

Thank you. On security, I heard that Oracle is safer. You can retrieve data from a SQL bad made​​.
How is this done (possible)? If I give a:

Delete From dbtest

I can retrieve this data?

[Celerian]

Let's talk technical.

There are differences (read: advantages and disadvantages) to Oracle, MS SQL, and MySQL.

Overall Database size and table sizes
Oracle has no limit on total size of the database, but keeps tables separated in 4GB blocks.

MS SQL can go up to 524,258TB (32,767 files * 16TB file size). Incidentally, the max table size in MS SQL is also 524,258TB.

MySQL is also unlimited, but tables sizes are maxes at 256TB (MyISAM limit) or 64TB (Innodb limit).

What this means is that assuming you needed to store more than 524,258TB of data, you would want to go with Oracle or MySQL.

Looking at table sizes, MySQL can only go up to 256TB or 64TB, depending on what storage standard you are using. Oracle breaks their tables into 4GB chunks, so every time to retrieve data, it has to pull up each one of those chunks to display. MS SQL can contain its full database size in one table, however it still technically would break that into 16TB chunks.

Rows and columns

Oracle can only hold 8kb of data in a row. It may have 1000 columns per row.

MS SQL used to have the same rowsize limitations, but using varchar(max) as one of your columns can make this go larger. Also, you may have up to 30,000 columns per row.

MySQL allows 64kb unless your using innodb standards which limit it to 8kb. MySQL allows 4096 columns.

Personally, I think even 1000 columns is going to be a little unweidly, so I can't say that the column limit is really an advantage for any of the databases. However, MS SQL is the only one that could potentially have a row of unlimited size. That's pretty impressive, i guess.

Also, Oracle constrains column names to 30 characters, while MySQL and MS SQL allow up to 128 characters. I feel 30 is a little light, but 128 is a little much. Still, I'd rather have overhead than limits. The other bad thing about Oracle is that their Char limit (text fields) is only 4000B, while MS SQL is 2GB. Even MySQL is 64kb. The limit here seems pretty low as well.

Blobs

Another difference is the blob size. MySQL allows 4GB. MS SQL allows 2GB, and Oracle is unlimited. Clear winner is Oracle if your storing big files.

Indexes

This is where Oracle shines.

MS SQL supports Expression, spatial, partial and full-text indicies as well as clustered and non-clustered hash tables.

MySQL supports some of this, but only when tables are MyISAM.

Oracle supports just about every type of index type, except for GiST and GIN. Also its hash indexes are limited to clustered tables.

Datatypes

Oracles datatypes are simplified, so while MySQL and MS SQL store integers in multiple different formats (based on size), Oracle stores all integers as a NUMBER datatype that (if I remember correctly) has a dynamic size.

Oracle does not support Boolean in a traditional sense. You can always store it as a binary integer.

Lastly, each different database we're looking at includes some other datatypes. I am actually surprised to find that MySQL includes a whole slew of different datatypes for GIS functions.

Security

Oracle and MS SQL don't differ too greatly when it comes to security. MySQL, however, doesn't contain a lot of security measures like password complexity rules or brute force protection.

In Conclusion

Another thing to consider is that while all three of these products are "SQL" keep in mind that each version of the language is slightly different. It's a lot like choosing your own favorite flavor of LISP.

A lot of the choice will be a matter of personal preference, and design requirements. In the grand scheme of things, a majority of people looking for a database solution would never need anything near the capabilities of either of MS SQL or Oracle, but there is still the divide.

[baavgai]

Great collection of info. However:

Celerian, on 25 June 2012 - 09:49 AM, said:

Oracle can only hold 8kb of data in a row.

Oracle recons they can do 2GB in a row. Granted, their way of saying that is a little cryptic: http://docs.oracle.c...dard_sql011.htm

[Celerian]

Thanks for the update. Though I'm not sure that it makes too much difference in the long run. I can't imagine too many scenarios where you would need to have a single row that would contain that much textual data. Globs obviously not included because they have special rules.

[baavgai]

Oracle does something interesting with blobs. It holds the first 4000 bytes or so in the local tablespace, as if it were just a big varchar2. After that, it moves the rest to another location.

I agree, though. Giant binary storage is one of those features that's questionable. Of course, so is 1000 columns. I've seen commercial databases with nearly 200 columns that gave me fits.

[Celerian]

I've only used Blobs with Sharepoint. It's actually fairly cool to be able to deposit files into a database and have an easy version control.

Before I installed sharepoint, they had a manually maintained document repository with no version control. Someone manually kept a excel spreadsheet with links to every document on the server that would be brought up when ever someone went to their document control website. It was a horrid way of keeping track of things.