Comparison: Oracle vs SQLite

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	Oracle	SQLite	Definition
intro	Oracle Database is a DBMS developed by Oracle Corporation.	SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.	in their own words - but I reserved the rights to remove some bold claims like "the best", unless it is widely recognized.
vendor	Oracle	SQLite Team
initial release	1980	2000
latested version	23ai	3.5	We don't put a release date here as the software is patching frequently. So tracking it is not much useful.
supported platforms	Windows, Linux, Solaris, HP-UX, AIX, z/OS	Android, *BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows	supported OS/CPU platforms
db-engines ranking	1	10	ranks from https://db-engines.com/en/ranking (06/25)
relational?	yes Oracle also has an open source nosql database.	yes	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?	no	yes	mainly the engine code
license	commercial	no license is needed The core SQLite database software is in the public domain. No license is necessary to use SQLite or to build SQLite into your products.
on-premise offering	yes	yes	if no means you can't buy "box" software from them
cloud offering	Oracle Cloud and other cloud vendors	None It is mainly in embedded usage.
technical doc	https://docs.oracle.com/en/database/	https://www.sqlite.org/docs.html
price: box software		0 free download

Data Types

Feature	Oracle	SQLite	Definition
int: 4-bytes int name	int	int
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
int: 1-bytes int name		int SQLite accepts names like TINYINT, SMALLINT, BIGINT, but only as type names. They’re purely aliases — they have no effect on storage size or strict typing, they will all converted to INTEGER type. Since the actual storage size is value-by-value basis, so the INT type you specified does not have any effect: it will pick up 0,1,2,3,4,6,8 bytes for your value.
int: 2-bytes int name		int
int: 8-bytes int name		int

SQL

Feature	Oracle	SQLite	Definition
SQL: standard complaince	high	medium
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
PL: main	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	yes		PL lanaguage other than PL/SQL, like PL/Java, PL/Rust etc
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
Object-Relational?	some

Storage and System

Feature	Oracle	SQLite	Definition
arch: server	C/S Oracle has a family of databases, even with nonsql one.	Embedded	Embedded or traditional C/S?
arch: run in browser?	no	yes	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: clustering/HA	RAC, Data Guard
arch: in-memory support	Oracle Database In-Memory option
arch: Multi-master support?	yes		if multi-master support?
arch: primary/read replica?	yes		if primary + mulitiple read replica supported
replication: sync/async	both		Can commits wait or w/o wait for replicas to acknowledge
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	yes/yes		for DML and DDL
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
ACID: durability	yes	yes
Materialized View: support?	yes	no

Benchmarking

Feature	Oracle	SQLite	Definition
any official TPC benchmarks?	yes	no	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	Oracle	SQLite	Definition
command line client		sqlite3	it means "sql client" for database supporting SQL. For embedded atabase, the client includes the server together.

Export Regulations

Feature	Oracle	SQLite	Definition
Jurisdiction	US	US	Which country controls export
ECCN	5D992.c	None or EAR99	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	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.	Not required There is no ECCN for open source software	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	TDE, SSL	SQLite Encryption Extension (SEE), SSL	Crypto functionality that triggers control

Internal

Feature	Oracle	Definition
concurrency control	MVCC
MVCC: implemented?	yes	if implement MVCC for concurrency control
MVCC: rollback segment	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	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	Oracle	Definition
CBO?	yes	if it employees a cost based optimizer
framework	System-R	System-R is more like a dynamic programming, bottom up optimizer, while Cascades/Volcano gebaseerd optimizer is more like top-down optimizer.
query hints?	complete	if it allows use query hints to guide the optimizer

Internal - Runtime

Feature	Oracle	Definition
resource management	complete	How execution memory is allocated and limited.
support intra-parallel query?	yes	meaning a single query can utilize multi hardware threads to run it
vectorization	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	all 3	Hash Join (HJ), Sort-Merge Join (MJ) and Nested loop Join (NLJ) are 3 major ones

Internal - Storage Engine

Feature	Oracle	SQLite	Definition
Page Size	2K-32K		The physical disk block size for storage and I/O.
partition table	complete		Partitioning capabilities for large tables.
partition table: prunning	both		Ability to eliminate partitions at query compile time and runtime
ACID: durability mechanism	WAL	WAL	How is it implements durability. Database's classic way is write ahead logging (WAL).

Comparison: Oracle vs SQLite

Table of Contents

General

Data Types

SQL

Storage and System

Benchmarking

Tools

Export Regulations

Internal

Internal - Optimizer

Internal - Runtime

Internal - Storage Engine