Lesson 9

LOBs (Large Objects) are Oracle’s native mechanism for storing and managing large values such as documents, images, rich text, and other data that does not fit comfortably into standard row-column design. While many classic OLAP schemas rely primarily on numeric facts and relatively small dimension attributes, real systems often need to attach “payload” data to entities—PDFs, contracts, audit artifacts, JSON documents, generated reports, or application text that can grow over time. Oracle’s LOB architecture lets you do that without breaking relational design or forcing awkward workarounds.

In Module 7, you built a practical end-to-end understanding of LOBs in Oracle, from choosing the right datatype, to selecting the right storage strategy, to applying the server-side features that keep performance predictable as LOBs grow. In Oracle 23ai, the same fundamentals still apply, but SecureFiles LOBs and modern storage attributes make it easier to tune storage and I/O behavior so LOBs behave well in production.

Workflow recap (Module 7):

1. Introduction to Large Objects: Established when LOBs belong in a design and how they differ from “normal” relational columns.
2. LOB datatypes: Selected between BLOB, CLOB, NCLOB, and external references such as BFILE based on binary vs. character data and the ownership model of the content.
3. Selecting LOB storage: Chose storage attributes (SecureFiles, caching policy, chunking, retention/versioning strategy, tablespace placement) based on access patterns and operational constraints.
4. Converting LONG and LONG RAW: Migrated legacy LONG/LONG RAW columns to LOBs so the data becomes fully supported by modern SQL features, tooling, and maintainability expectations.
5. Managing LOB size: Used LOB storage clauses and the DBMS_LOB package to control growth patterns and manipulate LOB content safely.
6. Large VARRAYs stored as LOBs: Learned how Oracle stores large collections out-of-line when they exceed row limits, and how that impacts storage and performance.
7. Temporary LOBs: Used session-scoped LOBs for intermediate transformations and application workflows without permanently storing content.
8. Buffering decisions: Distinguished database-side caching (CACHE/NOCACHE/CACHE READS) from client-side buffering, and learned when buffering is justified versus when it introduces unnecessary concurrency and recovery risk.

Taken together, these topics expand what your Oracle database can manage. You can store and process a wider variety of application content while still preserving predictable performance and maintainable operational practices.

A key implementation detail is that LOB storage is usually split across two logical areas:

1. The base table row, which stores normal column data plus a LOB locator (control data that points to the LOB content).
2. The LOB segment, which stores the out-of-line LOB payload when the value is too large or when your storage clause directs Oracle to store it out of row.

In many designs, most rows do not need the full LOB payload to be read during typical OLTP queries. Oracle supports this by returning a LOB locator first, allowing applications to fetch the LOB content only when needed. This is one reason LOBs can scale well: “normal” queries can stay light, while content retrieval happens explicitly.

The boundary between in-row and out-of-row behavior matters. If LOB values are small, storing them in-row can improve locality and reduce LOB segment I/O. If LOB values can grow, you often want to plan for out-of-row storage and tune the LOB segment (tablespace placement, chunk size, caching policy, and retention strategy) so growth is controlled and predictable.

Oracle can store small LOB values inline in the base table row when the value is small enough; otherwise it stores the payload out of line in a separate LOB segment and keeps only the locator in-row. You can influence this behavior using ENABLE STORAGE IN ROW and DISABLE STORAGE IN ROW in the LOB storage clause.

Operationally, treat this as a design decision:

• Enable in-row storage when most values are small and frequently accessed, and you want fewer LOB segment reads.
• Disable in-row storage when values may grow, when rows should stay small for scan efficiency, or when you want the base table to be stable even as LOB payloads expand.

Once a LOB value grows beyond the inline threshold, Oracle can move it out of row. In practice, you should design for the “grown” state rather than assuming values will stay small forever.

If you are storing “short text that might become long,” consider whether a standard string type is sufficient. Oracle supports extended string sizes when configured appropriately, but LOBs remain the correct choice when the data is truly “large object” content or when you need LOB-specific capabilities and management patterns.