Looking at MySQL and Oracle

I am going to build a list of features, commands and concepts from MySQL and Oracle. This comparison will not go into the pros and cons or which database has more options or features. The goal is to give someone a feel for the differences. I am a big evangelist for Oracle and MySQL technologies. This is a list I will build over a period of time.

The following are websites for downloads, documentation, code, blogs, forums, whitepapers, newsletters, etc. Be careful about going to these sites you will get hooked and spend hours on these two great sites.

• dev.mysql.com (MySQL)
• otn.oracle.com (Oracle)

Feature/Functionality

The Oracle Database Server is the flagship of large enterprise databases. Oracle excels at the level of data warehouses or running large enterprise applications like the financials for a Fortune 100 company. At the same time MySQL is generating a lot of momentum because customers are seeing they can implement it at the fraction of the cost and complexity of Oracle. Each database has their place in the market in which they fit well.

MySQL does not have all the functionality of the Oracle database server, but that is also what makes it so attractive. Not everybody needs all the functionality that comes with the Oracle database server. Most companies I go to are paying for all of Oracle's functionality but only using a very small percentage of what it can offer. Also, as web applications become more popular more functionality is being built in the middle-tier versus using database functionality.

With MySQL it is either free or its commercial licenses are a fraction of Oracle's cost. MySQL environments usually require less hardware, resources and money to get them up and running. The cost comparison makes it a great option for Small and Medium Businesses (SMB), government and state organizations and for projects that do not need the enterprise flagship functionality of Oracle. As more and more web applications are being built, J2EE and Internet developers are building more functionality in the middle-tier, negating a lot of the functionality that comes with an enterprise database server.

I've been very impressed with how fast MySQL can process different types of SQL statements and how good of a job the optimizer does in the MySQL environment. The code is lighter and more efficient because it does not have as many options to wade through. Sometimes being light and nimble can offer tremendous advantages for application developers that understand how to leverage the functionality of MySQL.

Products

Below is a list of popular products used with MySQL and Oracle:

MySQL is an open source solution. Products like Eclipse, Apache and JBoss are used to offer a multi-tiered web solution using MySQL. MySQL usually uses other open source products to provide a full multi-tiered offering. LAMP or a derivative of LAMP is seen in MySQL multi-tiered environments.

Oracle offers a complete line of products with JDeveloper, Oracle Application Server, SQL Developer, etc. Third party products are available as well (i.e. Toad, SQL Navigator, UniCenter, ...). Open source products such as JBoss and Eclipse can also be used in Oracle environments. The big difference is that Oracle offers a large line of vendor commercial products. The table below only shows Oracle specific products under the Oracle section.


This table will provide an example of what a MySQL and Oracle environment may look like.

MySQL	Oracle
Eclipse	Oracle JDeveloper
Struts, Spring	Application Development Framework
J2EE, PHP, Ruby on Rails, Groovy, .NET	J2EE
Hibernate	TopLink
Apache, JBoss	Oracle Application Server
TCP/IP, Unix Sockets, Shared Memory, NT Pipes	Oracle Net Services
Mysqladmin, MySQL Administrator, Phymyadmin Enterprise Monitor	OEM Database Control
MySQL Query Browser, Toad, Navicat	Oracle SQL Developer
mysql client tool mysqlimport mysqlcheck mysqldump myisamchk myisampack	SQL*Plus client tool SQL*Loader dbverify Data Pump, RMAN dbms_repair package
MySQL (GPL) MySQL Enterprise	Oracle Express Oracle Standard Oracle Enterprise
OpenLDAP, Fedora Directory Server	Oracle Internet Directory

Note: The reader needs to understand the products are usually not always equivalent. Some examples include:

• Oracle environments also support Ruby on Rails, .NET, Groovy, .NET, etc. However, the big push by Oracle is to use J2EE since Oracle Fusion applications run using J2EE. Also, organizations usually do not purchase a 100K or 1M Oracle license to run Ruby on Rails for their enterprise. Yet, Oracle does offer Oracle Express, a free database up to 4GB of storage where PHP, Ruby on Rails become a better option.
• Mysqldump is a very simple tool for creating a backup. Oracle Data Pump and Recovery Manager (RMAN) are tools that are more enterprise level tools.
• MySQL does not need additional networking software to work. It uses built-in networking protocols like TCP/IP. Oracle Net Services runs on top of TCP/IP. Oracle Net Services may or may not be free depending on what you are using on the client side. I realize Oracle has EZ Connect but it is not a replacement for Oracle Net Services.
  

Databases

One of the differences between MySQL and Oracle is how they treat the term database. A MySQL database is closer to an Oracle schema and all the storage for that schema. I’ll add an architectural picture of MySQL and Oracle that will help with this.

MySQL

1. The MySQL physical server environment is made up of all the individual databases, binary log, and additional files and logs.
   
2. A MySQL database is how to organize a group of database objects such as tables, views, triggers, etc.
3. There would be a separate MySQL database for a human resource and a sales system to organize each set of objects separately.
   

Oracle:

1. In Oracle the physical server environment is a database that encompasses all the physical files (storage) for a specific Oracle server (instance). This includes all files for data files, index files, redo log files, archive log files, etc.
2. Oracle organizes database objects in a schema. A schema is an Oracle user and all tables, views, sequences, synonyms, stored procedures, etc. the objects and resources owned by the user.
3. Oracle would use a separate “schema” for storing information for a human resource and a sales system if they were in the same database.
   

Summary:
A MySQL server environment is made up of server memory, background processes associated with multiple databases.

An Oracle server environment is made up of server memory and background processes (instance) associated with a single database organized with multiple schemas.

Startup Files
MySQL - The my.cfg or my.ini is the startup file for a MySQL server environment.

Oracle - An init.ora or spfile.ora file is used to start an Oracle server instance.

Metadata
The way MySQL and Oracle DBAs access metadata is similar but totally different. :)

With MySQL there are a large number of SHOW commands that will display detailed information on MySQL databases (data schemas), table structures etc. With Oracle the data dictionary views of USER_%, ALL_%, DBA_%, V$, etc. are used. The SHOW command in Oracle is only or the SQL*Plus client interface that will display local SQL*Plus variables in terms of how the SQL*Plus client is configured.

Both environments allow GLOBAL (instance) and LOCAL (session) variables to be defined. Below MySQL commands (mysql>) and Oracle commands (SQL) for accessing metadata will be compared.

-- Display server parameters.
mysql> SHOW VARIABLES;
SQL> SELECT name, value FROM V$parameter;

-- Set the sort buffer size.
mysql> SET GLOBAL sort_buffer_size=20 * 1024 * 1024;
SQL> ALTER SYSTEM SET sort_area_size=20971520;

-- Display information on triggers.
mysql> SHOW TRIGGERS LIKE 'fin%'\G
SQL> SELECT trigger_name, trigger_body FROM dba_triggers
WHERE trigger_name LIKE 'FIN%';

Storage Options

MySQL uses storage engines. Each of these storage engines deals with transactions, memory allocations, etc. differently. Very important a DBA understand the ramifications between the different types of storage. Some of the more popular storage engines include

• MyISAM
• InnoDB
• Memory
• Federated
• Archive
• Blackhole
• Falcon - New transactional storage available in MySQL 6.0.

MySQL stores data for tables in flat files that reside on the disk. RAW is available but not used much any more.
Oracle does not have the equvalent of storage engines. Oracle supports some of the following types of table storage:

• HEAP - regular table (default). Supports ACID transactions as well as being used able to be used for data warehouses.
  
• IOT - Index organized table. Storage is in a b-tree structure.
• External - Table definition that is mapped to an external file.

Oracle storage is managed in tablespaces. Tablespaces can store multiple data segments (tables) and index segments (indexes). Tablespaces organize Oracle storage structures. Tablespaces can store their data using RAW devices, operating system files and Automatic Storage Management (ASM). ASM uses Oracle internal storage mechanisms to write directly to physical devices without a file system. ASM can perform striping and mirroring using an Oracle Logical Volume Manager (ASM instance).

Creating Tables
Both MySQL and Oracle both support:
Regular tables
Indexes
Temporary tables
Partitioning

The big difference is that MySQL storage engines determine the functionality of MySQL tables. Another big difference is that MySQL uses AUTO_INCREMENT functionality to increment primary keys and unique identifiers. Oracle uses sequences to increment unique identifiers.

Stored procedures and triggers
MySQL and Oracle both support stored procedures and triggers. The MySQL stored procedure functionality is fairly new. However, the core of the language for writing stored procedures is available in MySQL. Oracle's procedural language has been around since 1991, so it obviously is more mature.

What's nice is that both of them use an ADA like syntax, so the core syntax of both languages looks almost exactly alike. Going from Oracle PL/SQL to MySQL's stored procedures would be a very easy transition.