Re: Oracle vs. MySQL

[Quoted] In article <39735258.CC6781FA_at_knowledgeinenergy.com>,   mango <mhammonds_at_knowledgeinenergy.com> wrote:
> What is the difference between Oracle and MySQL?
>

The difference between Oracle and MySQL is fairly large:

Oracle supports a robust transaction model allowing session based commit, rollback, and savepoint operations, while MySQL does not. These transaction operations allow a fine degree of control over data concurrency and data integrity.

Oracle also provides replication, parallel processing options, support for extensible SQL and object types, stored procedures and packages (written and running natively in PL/SQL or Java), row-level locking, row and field-level security model, online backup, and a host of other advanced database features.

To my knowledge, MySQL does not support most of these features (though it may now support row-level locking). Many other enterprise databases do not support many of these features either - so though they are differentiating features for Oracle, your application may not require them. For instance, MS SQL Server did not support row-level locking until recently but it has certainly been used to build serious data applications.

There is an effort under way to implement a transaction model for MySQL but it is not a trivial task.

Transaction control is highly desirable on systems that support multiple users who need to see consistent views of data. The transaction mechanism allows independent users to see a snapshot of data that is protected from changes or updates that may be in process.

Transaction control is also very useful in systems where data integrity must be maintained across multiple tables, schemas, or databases - in the event that a process fails while modifying data, the entire process and all data changes can be "rolled back" on all affected systems returning the system to it's prior, consistent state. Transaction control across different databases requires support for a "Two-Phase commit" process. Oracle has support for this feature, while MySQL does not.

Unfortunately transaction control can cost a lot in performance and resources since maintaining multiple, disparate 'snapshots' of whole sets of data can be a very expensive operation.

MySQL is generally viewed as sufficient for systems that are small, only have a few users with limited concurrency requirements, and no need for true transaction control.

Most web server based applications attach to a database as a single database user and usually perform all select and data manipulation operations through this connection. The web server in effect serves to multiplex the users requests and serialize them through one or more database connections as the same user.

In this type of situation, data concurrency and integrity are at a low risk of being impacted, as user load will likely swamp the web server before the database experiences a collision.

Considering the new licensing policies for Oracle, I would continue MySQL unless you require the advanced enterprise features of Oracle for your application. ALso, recognize that there are other alternatives to Oracle, for instance IBM DB/2, Informix, Sybase SQL Anywhere and Sybase Adaptive Server to name a few.

Good luck

--
Mohan C. Kartha 			MindShare Incorporated
mohan_at_mindsinc.com

--
Mohan C. Kartha 			MindShare Incorporated
mohan_at_mindsinc.com 		210 Nickels Arcade
http://www.mindsinc.com 		Ann Arbor, MI 48104
(313) 509-0472 pager/Vox 		(734) 995-0915 fax


Sent via Deja.com http://www.deja.com/
Before you buy.