Oracle vs MySQL

MySQL:

is very light, lightning fast,
very easy to use,
cost effective for start-ups,
agile enough to cover any basic secure medium enterprise OLTP,
I’m used to manage 1-10T table before having some issue,
basically it’s divided into database and table (In the old MySQL 5.0, you can easily setup multiple instances in one daemon. In the new MySQL 5.5, it’s recommended to run multiple instances in multiple daemons),
replication is very easy to setup and easy to fix conflicts, however up until 5.6, there’s still many bugs,
hot backup/snapshot is not coming by nature, but you can easily do hot backup/snapshot from stopped replicated server,
MySQL Enterprise Admin is relatively useless for experienced DBA,
can only replicate from one source,
doesn’t have propagation checking like Oracle DataGuard,
easier to have conflict in multiple active master,
need to customize procedure to run similar Oracle materialized view,
need to customize trigger to run similar Oracle audit,
MySQL 5.6 starts to support account lock and password testing but through plugin method,
MySQL Cluster is having all data in memory, risk of loosing data during power/server outage is high.

heavyweight, resource consumed,
better OLTP in term of complete microseconds DR,
suitable for VLDB,
more features,
divided into database, schema (it’s more likely called database in MySQL) and table,
online hot backup is easy to do on archivelog mode,
Oracle Enterprise Manager supports more difficult tasks,
have proprietary ASM storage,
supports multiple active masters,
supports slave check before commit (DataGuard),
supports replication from multiple masters,
supports materialized view,
supports audit,
supports more account features like account lock, password expiry, etc,
supports function-based index and indexed-virtual column,
supports role for privilege administration (similar to group privilege),
Oracle cluster RAC is a real cluster on disc.

Similarity:

From OLAP perspective, in my opinion both are offering similar features. OLAP depends more on DBA ability, rather than DB Servers ability.
Both supports encrypted replication and communication.

How to run in MySQL vs in Oracle:

displaying x numbers of row, e.g.: MySQL# select … limit x,x; Oracle# select … where rownum<=x;
displaying a column in rows: MySQL has ‘\G’. Oracle has to use procedure like this: https://forums.oracle.com/thread/925884.
MySQL has binary and varbinary as an addition to char and varchar. Oracle has nchar and nchar2 as an addition to char and varchar2.
@>show tables in Oracle:@> select * from user_objects where object_type = ‘TABLE’;
@>show create table in Oracle: @>select dbms_metadata.get_ddl(‘TABLE’, ‘TABLENAME’,’SCHEMANAME’) from dual;
@>show index in Oracle: @>select dbms_metadata.get_ddl(‘INDEX’, ‘TABLENAME’,’SCHEMANAME’) from dual;

Note:

]]>

Personal Journey