Comparison: MySQL vs Oracle

Modified date: Monday, June 30, 2025

Introduction
General
Data Types
SQL
Storage and System
Benchmarking
Tools
Export Regulations
Internal
Internal - Optimizer
Internal - Runtime
Internal - Storage Engine

General

Feature	MySQL	Oracle	Definition
intro	MySQL is an open source relational database management system (RDBMS) that’s used to store and manage data.	Oracle Database is a DBMS developed by Oracle Corporation.	in their own words - but I reserved the rights to remove some bold claims like "the best", unless it is widely recognized.
vendor	Oracle	Oracle
initial release	1995	1980
latested version	9	23ai	We don't put a release date here as the software is patching frequently. So tracking it is not much useful.
db-engines ranking	2	1	ranks from https://db-engines.com/en/ranking (06/25)
relational?	yes	yes Oracle also has an open source nosql database.	Is it a relational database? (1) Most database are actually with some extensions, for example, nested data types, graph support, etc, which we usually called "multi-model". (2) Some of them are product family, meaning they have more than one database. Here we focus on the main one but explain others when needed.
open source?	yes	no	mainly the engine code
license	commerical, GNU a dual-license model: an open-source license (GPL) and commercial licenses	commercial
price: box software	$0 ~ $32,100 (2023) MySQL pricing by edition (annual subscriptions): （1）Community Edition: Open-source and free — from $0 (2) Standard Edition: Starts at $2,140/year for a two-core server; can scale up to $12,840/year as you add more cores portable.io (3) Enterprise Edition: Starts at $5,350/year for two cores; increases up to $32,100/year depending on core count
on-premise offering	yes	yes	if no means you can't buy "box" software from them
cloud offering	None	Oracle Cloud and other cloud vendors
technical doc	https://dev.mysql.com/doc/	https://docs.oracle.com/en/database/
supported platforms		Windows, Linux, Solaris, HP-UX, AIX, z/OS	supported OS/CPU platforms

Data Types

Feature	MySQL	Oracle	Definition
int: signess	both		if differentiate signed and unsigned int
int: 1-bytes int name	tinyint
int: 2-bytes int name	smallint
int: 3-bytes int name	mediumint
int: 4-bytes int name	int	int
int: 8-bytes int name	bigint
char(n): max bytes		2000
BLOB: max size		(4B-1) * pages Page size is defined by DB_BLOCK_SIZE in Oracle, which is ranging from 2K to 32K. This gives BLOB size from 8T to 128TB.
JSON: max size		32MB
Literal: max size		4000	characters or numbers in SQL or PL/SQL

SQL

Feature	MySQL	Oracle	Definition
SQL: standard complaince	medium	high
PL: main	SQL + SP	SQL + PL/SQL	main programming lanage: most database suports SQL because SQL is a well established standard. However, each database would like to extend SQL more or less.
PL: other language support	no	yes	PL lanaguage other than PL/SQL, like PL/Java, PL/Rust etc
Object-Relational?	no	some
vector search	no native		does it support vector search
max SQL length		undefined The actual limit may depends on limits of the SUT, for example, its memory/system swap settings: Oracle internally must use a malloc'ed buffer to hold the SQL string.	maximal SQL statement length
SQL: query hints		complete	if it allows use query hints to guide the optimizer
SQL: explicit locking		yes	Locking is usually an internal matter - so does it allow explicit locking? What levels do they support?
Triggers?		yes	If support triggers

Storage and System

Feature	MySQL	Oracle	Definition
arch: server	C/S	C/S Oracle has a family of databases, even with nonsql one.	Embedded or traditional C/S?
arch: run in browser?	no	no	It also known as a client-side database, is a database that is stored and managed within a user's web browser, rather than on a remote server.
arch: Multi-master support?	no	yes	if multi-master support?
arch: primary/read replica?	yes	yes	if primary + mulitiple read replica supported
replication: sync/async	both	both	Can commits wait or w/o wait for replicas to acknowledge
ACID	yes/atomic DDL (non-transactional) Atomic DDL is not transactional DDL. DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement. This means that DDL statements cannot be performed within another transaction, within transaction control statements such as START TRANSACTION ... COMMIT, or combined with other statements within the same transaction.	yes/yes	for DML and DDL
ACID: durability	yes	yes
Materialized View: support?	no	yes
arch: clustering/HA		RAC, Data Guard
arch: in-memory support		Oracle Database In-Memory option
replication: WAL shipping		yes	Uses write-ahead log (WAL) shipping for replication
sessions: max		262143
tables: max number per database		undefined
tables: max number of columns		4096	Max number of columns per table
contraints: max per column		undefined
partitions: methods		Range, List, Hash, Composite (sub-partitions supported).	Supported partitioning strategies (range, list, hash, etc.).
partitions: global index		yes	index across partitions
constraints: max per database		4,294,967,293
rows: max rows per table		undefined	The actual number depends on storage etc
index: max allowed index		undefined	Max number of indices allowed per table
index: max allowable size		6400	Max index record size (bytes). This constraint is mainly coming from the fact of the database page size: if we exclude blob data types, database engine usally do not allow a record expand more than one page.
index: max number of fields		32	Max number of columns allowed in one index
partition: max allowed partitions		1M-1	Meta data challenge: 1M partitions just like 1M tables, system have to hold them in memory. Optimizer challenge: O(N) algorithm may lead to very long planning time if there are excessive partitions.
partition: max allowed key columns		16
partition: max number of subpartitions		1M-1
ACID: max isolation level		SI The Snapshot Isolation (SI) implemented by Oracle allows some anomalies, including write skew. But the SI does satisfies ANSI's serializable definition.
ACID: max ANSI isolation level		Serializable

Benchmarking

Feature	MySQL	Oracle	Definition
any official TPC benchmarks?	no	yes	The TPC benchmark includes a set of tests simulating real-world scenarios to evaluate database performance.
TPCC: most recent tpmC		8,552,523 System cost: 4,663,073 USD
TPCC: most recent submit date		3/26/2013
TPCC: most recent per thread perf		8352
TPCC: best tpmC		30,249,688 System cost: 30,528,863 USD
TPCC: best perf submit date		12/2/2010
TPCC: best perf per thread perf		2188

Tools

Feature	MySQL	Oracle	Definition
command line client	mysql		it means "sql client" for database supporting SQL. For embedded atabase, the client includes the server together.
admin(GUI)	MySQL workbench

Export Regulations

Feature	MySQL	Oracle	Definition
Jurisdiction	US	US	Which country controls export
ECCN	None	5D992.c	An Export Control Classification Number (ECCN) is a five-character alphanumeric code used to categorize items on the Commerce Control List (CCL) for export control purposes. Most database may fall into 5D992.c category, "mass market encryption", which means it has some ordinary encryption related code, for example, the SSL connection code.
Eligible License Exception / CCATS	Not required There is no ECCN for open source software	NLR NLR essentially means that a commodity has been classified within the Commodity Classification Automated Tracking System and determined to not require a BIS export license for its export.	A License Exception is an authorization that allows you to export or reexport items subject to the EAR without needing to obtain a specific export license, provided certain conditions are met. CCATS stands for Commodity Classification Automated Tracking System. The BIS assigns a CCATS number to products that it has classified under the Commerce Control List (CCL).
Encryption Components	SSL	TDE, SSL	Crypto functionality that triggers control

Internal

Feature	MySQL	Oracle	Definition
concurrency control	InnoDB: MVCC	MVCC
MVCC: implemented?	yes	yes	if implement MVCC for concurrency control
MVCC: rollback segment	yes MySQL supports multiple storage engines, with InnoDB is popular. So we focus on InnoDB here.	yes	if uses rollback segment (RS) to store old versions. Without RS, old versions and new versions are mixed stored, then the database engine has to find a way to efficiently drop the old versions at certain point.
Implementation language	C++	C	A DBMS may use mulitple programming languages, for example, supports its stored procedure. The major programming language used to implement the engine.

Internal - Optimizer

Feature	MySQL	Oracle	Definition
CBO?	yes	yes	if it employees a cost based optimizer
framework	System-R	System-R	System-R is more like a dynamic programming, bottom up optimizer, while Cascades/Volcano gebaseerd optimizer is more like top-down optimizer.
stats: multi column	no		Multi-column stats may cause storage space bloat: for example, if one dimension has 100 buckets, then three dimensions will require 1M buckets - but reducing the total number of buckets will result in reduced accuracy.
plan guide?	no		Can we use plan guide to correct the plan? This is a more systematic and accurate way to repair the plan than plan hints. Especially during system upgrades, if the plan becomes worse, we can use plan guide to force a query to use a previous plan.
query hints?		complete	if it allows use query hints to guide the optimizer

Internal - Runtime

Feature	MySQL	Oracle	Definition
spilling support	yes		"spilling" refers to the process of writing temporary data or intermediate results of a query to disk when the available memory is exhausted. This is crucial for handling large datasets or complex queries that require more memory than available.
model	Volcano		The operator scheduling model: pull-gebaseerd (Volcano), push etc.
support intra-parallel query?	no	yes	meaning a single query can utilize multi hardware threads to run it
adaptive execution (AQP)?	no		Traditionally, after the optimizer determines the plan, the runtime must execute it completely without any room for adjustment, such as which of the two tables should be built. The adaptive method allows the runtime to make some adjustments based on the actual situation, and the optimizer must also prepare for this uncertainty, such as preparing an alternative plan.
vectorization	no native. HeatWave supports it (not open source).	yes	Speed up OLAP queries using vectorized execution. A vectorized runtime exchange a bunch of rows between iterators, and these rows are physically sotre with column-oriented order.
iterator: join methods	except MJ	all 3	Hash Join (HJ), Sort-Merge Join (MJ) and Nested loop Join (NLJ) are 3 major ones
Error: out-of-range and overflow	strict mode: abort. Non-strict mode: continue with warning.		To maintain atomic requirement of ACID, database engine usually fail the statement and abort the transaction.
resource management		complete	How execution memory is allocated and limited.

Internal - Storage Engine

Feature	MySQL	Oracle	Definition
Page Size	4K, 8K, 16K, 32K, 64K Every tablespace in a MySQL instance has the same page size. By default, all tablespaces have a page size of 16KB; you can reduce the page size to 8KB or 4KB by specifying the innodb_page_size option when you create the MySQL instance. You can also increase the page size to 32KB or 64KB.	2K-32K	The physical disk block size for storage and I/O.
Storage Compression	ROW_FORMAT=COMPRESSED
ACID: durability mechanism	InnoDB: WAL	WAL	How is it implements durability. Database's classic way is write ahead logging (WAL).
partition table		complete	Partitioning capabilities for large tables.
partition table: prunning		both	Ability to eliminate partitions at query compile time and runtime

Comparison: MySQL vs Oracle

Table of Contents

General

Data Types

SQL

Storage and System

Benchmarking

Tools

Export Regulations

Internal

Internal - Optimizer

Internal - Runtime

Internal - Storage Engine