Lesson 3	Selecting LOB storage
Objective	Differentiate between the criteria for selecting one storage format over another for LOB

Selecting LOB storage in Oracle 23ai

LOB columns are easy to add to a schema, but the storage choices behind them can dramatically affect performance and operational behavior. In Oracle 23ai, most internal LOBs are implemented with SecureFiles, which supports modern features such as compression, deduplication, and encryption. Your job is to choose a storage approach that matches the workload: OLTP vs reporting, small vs large payloads, read-mostly vs write-heavy, and database-owned content vs external files.

Storage options (what you are really choosing)

Oracle offers three broad ways to store LOB content:

Type of storage	Characteristics	When it fits
In-row `ENABLE STORAGE IN ROW`	Stores small LOB values inside the table row (up to the in-row threshold). This can improve point-lookups for small LOBs because fewer physical reads are needed, but it increases row size and can degrade full scans and index range scans that touch many rows.	Many rows contain small LOBs and you frequently retrieve the LOB together with the row (tight OLTP access pattern).
Out-of-row `DISABLE STORAGE IN ROW` (or default)	Stores LOB data in a separate LOB segment (not inside the base table row). Rows remain smaller and scans can be faster when you rarely need the LOB payload. LOB reads become a separate access step when you do fetch the LOB.	LOBs are usually large, optional, or infrequently accessed (common in mixed workloads and reporting/analytics patterns).
External `BFILE`	Stores a locator (pointer) to an operating system file. Read-only from Oracle, not transactional, and the bytes are not protected by database-level features like redo/undo or RMAN backups unless you back up the filesystem separately.	You already manage files outside the database and need Oracle to reference them without storing the bytes inside the database.

Selection criteria (how to decide)

Use these criteria to choose between in-row, out-of-row, and external storage:

Access pattern: if most queries need the LOB value, in-row storage can reduce extra I/O for small payloads. If most queries do not need the LOB, out-of-row keeps the base table lean.
Payload size distribution: if LOBs routinely grow beyond the in-row threshold, design for out-of-row and place the LOB segment in an appropriate tablespace.
Workload type: OLTP tables benefit from smaller rows (out-of-row) when concurrency is high; read-mostly workloads can benefit from SecureFiles compression/dedup if the data is repetitive.
Operational requirements: if you need transactional consistency, backup/recovery, and database security controls, store the LOB internally (BLOB/CLOB/NCLOB) rather than using BFILE.
Security and compliance: internal SecureFiles LOBs can be protected with database encryption; external files require OS/filesystem controls and separate backup discipline.

SecureFiles features as part of “storage format”

When you store LOBs internally in Oracle 23ai, you are also choosing whether to enable SecureFiles options such as compression, deduplication, and encryption. These settings can reduce storage footprint and improve manageability, but they must be evaluated against CPU cost and workload behavior. The key takeaway is that “LOB storage” is not only where the bytes live—it is also how Oracle stores and protects them.

What’s next

In the next lesson, you will learn how to convert LONG and LONG RAW columns into modern LOB types so you can use current Oracle functionality and manage large payloads more reliably.

LOB storage quiz

Click the Quiz link below to test your knowledge of LOB storage.

LOB Storage - Quiz