Lesson 6 | Large varrays and LOBs |
Objective | Describe how Oracle uses LOBs to store large varrays |
Oracle 23ai RDBMS continues to use LOBs (Large Objects) to store large VARRAYs
Yes, Oracle 23ai RDBMS continues to use LOBs (Large Objects) to store large VARRAYs when their size exceeds certain limits.
✅ Key Points:
- VARRAYs and Inline Storage:
- By default, VARRAYs are stored inline in the row (within the table) if their total size is within the limit — typically up to 4,000 bytes for SQL types or 32 KB for PL/SQL in older versions.
- When LOB Storage Is Used:
- If the total size of the VARRAY data exceeds the inline threshold, Oracle will automatically store the VARRAY as a LOB.
- This is typically automated via the clause
STORE AS LOB
in the object or table definition.
- Oracle 23ai Enhancements:
- Oracle 23ai, like Oracle 21c/19c, supports securefiles LOBs, deduplication, and compression, which can be used to optimize large VARRAY storage.
- It also improves LOB performance via background optimizations, making storage of large VARRAYs more efficient.
🔍 Example:
CREATE TYPE large_array_type AS VARRAY(10000) OF NUMBER;
/
CREATE TABLE test_varray (
id NUMBER,
data large_array_type
)
LOB (data) STORE AS SECUREFILE data_lob;
In the example above:
- Oracle knows that the VARRAY can exceed inline storage limits.
- It will use LOB storage for the
data
column with SecureFile optimization.
📘 Related Views/Tools:
- Use
DBA_LOBS
to check how LOBs (including VARRAYs stored as LOBs) are configured.
- The
USER_LOBS
and USER_TYPES
views help inspect LOB/Type metadata.
Utilization of Large Objects (LOBs) for Storing Large Varrays in Oracle 12c Database
Describe how Oracle uses LOBs to store large varrays in Oracle 23ai.
In Oracle 23ai, large `VARRAY`s (variable-size arrays) that exceed a certain size threshold are stored as LOBs (Large Objects) to manage their data more efficiently.
📌 Overview of VARRAYs in Oracle
- A
VARRAY
is a type of collection in Oracle that stores an ordered set of elements.
- Each VARRAY is stored as a single object in a column or object attribute.
- When the total size of the VARRAY exceeds a certain threshold (usually around 4,000 bytes), Oracle stores it out-of-line in a LOB segment.
🔍 How Oracle Uses LOBs for Large VARRAYs
- In-line vs Out-of-line Storage:
- In-line: Small VARRAYs (typically <4,000 bytes) are stored directly within the row (like regular column data).
- Out-of-line: Large VARRAYs are stored as BLOBs (Binary LOBs) in separate LOB segments.
- LOB Locator:
- When stored out-of-line, the actual VARRAY data is not inside the row.
- Instead, the table column stores a LOB locator, which points to the location of the LOB data in the LOB segment.
- LOB Segment Allocation:
- The associated LOB segment is created automatically by Oracle when the table is created with a VARRAY column that may exceed the in-line threshold.
- You can define storage parameters for this LOB segment using the
STORE AS LOB
clause in CREATE TYPE
or CREATE TABLE
.
- Performance Implications:
- Oracle uses automatic caching, chunking, and logging options to optimize access to LOBs.
- SecureFiles LOBs (default in newer versions) offer deduplication, compression, and encryption for efficient storage.
✅ Example
-- Define an object type with a VARRAY
CREATE OR REPLACE TYPE NumberList AS VARRAY(100000) OF NUMBER;
/
-- Use the type in a table
CREATE TABLE big_array_table (
id NUMBER,
data NumberList
)
LOB (data) STORE AS SECUREFILE (
ENABLE STORAGE IN ROW
CHUNK 8192
NOCACHE
LOGGING
);
In this example:
- The
NumberList
can grow large.
- Oracle will store
data
as a LOB once it exceeds the in-line size limit.
- The LOB is stored in a SecureFile LOB segment with specified chunk size and logging behavior.
📘 Notes
- Oracle 23ai uses the same core LOB infrastructure as Oracle 19c and 21c, with enhancements for performance and AI integration, but the mechanism for storing large VARRAYs as LOBs remains the same.
- You can query
DBA_LOBS
and DBA_SEGMENTS
to inspect where and how the VARRAY LOBs are stored.
The Object Relational Model and Collection
One of the concepts behind the object-relational model implemented by Oracle is the idea of a collection. A collection is a model of a one-to-many relationship. An object can contain a collection of other entities, such as a purchase order containing a collection of line items.
Two types of collections are supported by Oracle varrays and nested tables. As their names imply, a varray is very much like an array,
in that it is stored as a complete entity, whereas a nested table is stored like a collection of rows in a table. A varray is appropriate for
storage of information that will not be subjected to individual queries, whereas a nested table is more appropriate for efficient retrieval of
individual members of a collection. In addition, a varray is stored in order and has distinct boundaries, whereas a nested table has neither of
these characteristics.
Varrays and LOBs
Within Oracle, varrays must be stored in BLOBs. If a varray has fewer than 4,000 bytes, it could also be stored as an in-line BLOB or in
a separate BLOB out of line. When you define an object type with a varray in it, you specify the LOB that will hold the varray with the syntax explained in the following diagram.
CREATE TYPE typename
AS VARRAY (n)
OF datatype;
CREATE TYPE |
Required keywords for type definition. |
typename |
Unique name for type. |
AS VARRAY |
Required keywords for varray definition |
n |
Integer that sets the boundary for the number of entries in the varray |
OF |
Required keyword |
datatype |
Data type specification for varray. This can be a built-in data type or an object type. It cannot be another varray type, a nested table type, or a LOB type.
|
A VARRAY (Variable-size array) in Oracle does not have to be stored in a BLOB, but it can be stored in a LOB (Large Object)—such as a BLOB or CLOB—depending on its size and the table definition.
Here’s how Oracle handles storage of VARRAYs:
✅ 1. VARRAY Stored Inline (Default)
* By default, a VARRAY is stored inline in the table row if the total size is within the row size limit (typically < 4,000 bytes for non-CLOB data types).
* This is efficient for small arrays.
CREATE TYPE scores_t AS VARRAY(10) OF NUMBER;
CREATE TABLE students (
student_id NUMBER,
name VARCHAR2(100),
scores scores_t
);
In this example, `scores` is stored directly in the row.
---
### ✅ 2. VARRAY Stored as LOB (BLOB or CLOB)
* If you explicitly specify `STORE AS LOB`, or if the VARRAY exceeds the inline size limit, then Oracle stores the VARRAY out-of-line in a LOB segment.
CREATE TYPE scores_t AS VARRAY(10000) OF NUMBER;
CREATE TABLE students (
student_id NUMBER,
name VARCHAR2(100),
scores scores_t
) VARRAY scores STORE AS LOB;
```html
- Internally, Oracle stores it as a BLOB or securefile LOB, depending on configuration.
- Useful when working with large collections.
🔍 Notes
- You must use LOB storage if the VARRAY size could exceed the maximum row size.
- Oracle supports LOB locators for efficient VARRAY access when stored out-of-line.
- Storing VARRAYs as LOBs can improve I/O performance and scalability for large collections.
Summary
Storage Option |
Must Use BLOB? |
Inline/Out-of-Line |
Best Use Case |
Default (no LOB) |
❌ |
Inline |
Small arrays (< 4000 bytes total) |
STORE AS LOB |
✅ Internally |
Out-of-Line |
Large arrays, high scalability |
So while you can store a VARRAY in a BLOB, it is not required,Oracle supports both inline and LOB storage depending on size and definition.
The varray type is defined with the following syntax.
CREATE TYPE PHONE_ARRAY AS
VARRAY(10) OF VARCHAR2(15);
This object type allows for the storage of up to 10 phone numbers, which can be up to 15 characters in length. You could use the PHONE_ARRAY data type in another table or object type, such as the
CUSTOMER_TYPE
object.
In the next lesson, you will learn about temporary LOBs.

