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:

1. 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.
2. 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.
3. 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.

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.

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

1. 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.
2. 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.
3. 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.
4. 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.

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.

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.