How Oracle 23ai Stores Large VARRAYs Using LOB Segments

Oracle supports collections as part of its object-relational feature set, and one of the collection types is the VARRAY (variable-size array). A VARRAY is stored as a single logical value in a column or object attribute. That “single value” property is the reason Oracle sometimes stores the VARRAY inline in the row and sometimes stores it out-of-line in a LOB segment.

The sizing rule is straightforward in practice: if a VARRAY can fit in the row, Oracle can store it inline; if it becomes too large, it must be stored out-of-line. Out-of-line storage uses the same LOB infrastructure you’ve been learning in this module: a LOB segment plus a locator stored in the base table row.

In Oracle’s object-relational model, a collection represents a one-to-many relationship that is stored as a single attribute. A purchase order might contain a collection of line items; a customer object might contain a collection of phone numbers.

Oracle provides two main collection types:

• VARRAY — ordered, bounded (you specify a maximum number of elements), stored and retrieved as a single value.
• Nested table — stored more like a relational child table; better suited for querying individual elements.

A VARRAY is often the right choice when you typically fetch the entire collection as part of the parent row (for example, “give me the customer and all phone numbers”), rather than searching for individual elements inside the collection.

Oracle can store a VARRAY inline in the row when the serialized VARRAY value stays small. When the VARRAY grows past what is reasonable for inline row storage, Oracle stores the VARRAY out-of-line and uses a LOB segment. The row then contains a locator that points to the out-of-line data.

Conceptually, the shift looks like this:

1. Inline VARRAY: the row contains the VARRAY payload directly (fast for small arrays; increases row size).
2. LOB-backed VARRAY: the row contains a locator; the VARRAY payload lives in a LOB segment (keeps the row smaller; requires an extra hop to fetch the payload).

The practical implication (and the tuning connection) is that inline VARRAYs can make scans more expensive because the row is larger, while out-of-line storage can make point reads more expensive because Oracle must follow the locator to a separate segment. Your goal is to choose an approach that matches access patterns and expected collection size.

When Oracle stores a VARRAY out-of-line, it uses the same LOB mechanics you’ve already seen: a LOB segment, chunking, caching choices, and (in modern releases) SecureFiles features such as compression and deduplication.

That means a “large VARRAY” inherits all the LOB design decisions from earlier lessons:

• Storage placement: keep LOB segments in a dedicated tablespace when appropriate for capacity planning.
• Chunk size: tune the I/O unit used for reads/writes of the VARRAY payload.
• RETENTION: consider read consistency and undo pressure when the collection is updated frequently.
• CACHE/NOCACHE: decide whether VARRAY payloads should be cached like other LOB data.
• SecureFiles: optionally apply compression/deduplication/encryption if the payload benefits.

The base syntax for defining a VARRAY type is simple: you declare a maximum number of elements and the element datatype.

CREATE TYPE typename AS VARRAY (n) OF datatype;

CREATE TYPE	Required keywords for defining a type.
typename	Unique name for the type.
AS VARRAY	Required keywords for declaring a VARRAY collection.
n	Maximum number of elements allowed in the VARRAY.
OF	Required keyword.
datatype	Datatype of each element. This can be a built-in scalar datatype or an object type. It cannot be another VARRAY type or nested table type.

If the VARRAY can become large, you can be explicit and tell Oracle to store the VARRAY out-of-line using LOB storage. This makes the storage intent clear and gives you a place to attach LOB storage attributes.

CREATE TYPE NumberList AS VARRAY(100000) OF NUMBER;
/

CREATE TABLE big_array_table (
  id   NUMBER,
  data NumberList
)
VARRAY data STORE AS LOB (
  STORE AS SECUREFILE (
    CHUNK 8192
    NOCACHE
    RETENTION AUTO
  )
);

In this pattern:

• The column still appears as data NumberList to the application.
• Oracle stores the actual payload in a LOB segment when it is out-of-line.
• The base table row stores a locator, not the full payload.

If you need to verify how Oracle stored a given VARRAY-as-LOB implementation, the same metadata views you use for LOBs apply: USER_LOBS/DBA_LOBS for LOB configuration, and USER_SEGMENTS/DBA_SEGMENTS for space usage.

Since this is a tuning-focused module, it’s worth connecting storage mechanics back to design choices:

• Choose VARRAY when you want ordered data with a known upper bound and you usually read/write the collection as a whole. LOB storage becomes relevant as the VARRAY grows.
• Choose nested tables when you need to query or index individual elements and treat the collection like rows. This typically maps better to relational access patterns.

The “VARRAY stored as LOB” feature is essentially Oracle bridging object-style modeling (collections as attributes) with physical storage strategies that keep row sizes manageable when the collection grows beyond inline comfort.

Here is a small VARRAY type that fits naturally inline in many cases, but still illustrates the collection concept:

CREATE TYPE phone_array AS VARRAY(10) OF VARCHAR2(15);

This allows up to 10 phone numbers (15 characters each) to be stored as a single attribute in a table row or object type, such as a customer_type. If your design grows beyond small collections (for example, thousands of entries per row), you either explicitly store the VARRAY as a LOB or re-evaluate the design and consider a nested table or a child table.

In the next lesson, you will learn about temporary LOBs and why they matter for high-throughput LOB processing workflows.