Why Oracle Converts Extended VARCHAR2 to LOB and How It Affects Performance

Background

In Oracle databases VARCHAR2 stores character data in‑row, while LOB types (BLOB, CLOB, NCLOB) are used for large binary or textual objects. Starting with Oracle 12c the initialization parameter MAX_STRING_SIZE can be set to EXTENDED, which raises the maximum length of VARCHAR2, NVARCHAR2 and RAW columns from 4000 bytes to 32767 bytes (32 KB). When an extended column is created Oracle automatically creates a SecureFile LOB segment to hold the data.

Test environment

Oracle Database 12.1.0.2 on CentOS 7.5.

Step‑by‑step verification

Verify that the current schema has no LOB objects.

SQL> SELECT table_name, column_name, index_name, chunk, cache FROM user_lobs;

Result: no rows selected.

Create a table that contains an extended VARCHAR2 column.

SQL> CREATE TABLE test_extend_type (
       id          NUMBER,
       name        VARCHAR2(4000),
       detail_info VARCHAR2(32767)  -- extended data type
     );

Query USER_LOBS to confirm that Oracle created a LOB segment for the extended column.

SQL> SELECT table_name, column_name, index_name, chunk, cache FROM user_lobs;

Result shows a row for DETAIL_INFO with a chunk size of 8192 bytes and cache enabled.

Insert a row whose detail_info value is smaller than 4000 bytes and dump the data block that stores the row.

SQL> INSERT INTO test_extend_type VALUES (1111,'aaaa','bbbbbb');
SQL> COMMIT;
SQL> SELECT dbms_rowid.rowid_relative_fno(rowid),
             dbms_rowid.rowid_block_number(rowid)
      FROM test_extend_type WHERE id = 1111;
SQL> ALTER SYSTEM DUMP DATAFILE 2 BLOCK 133;
SQL> ORADEBUG SETMYPID;
SQL> ORADEBUG TRACEFILE_NAME;

The trace shows that DETAIL_INFO is stored as a SecureFile BLOB with the DataInRow flag because the value fits within 4000 bytes.

Insert a row whose detail_info exceeds 4000 bytes and dump the same block.

SQL> INSERT INTO test_extend_type VALUES (202020,'Sam_testing',LPAD('TEST',500000,'SAM'));
SQL> COMMIT;
SQL> ALTER SYSTEM DUMP DATAFILE 2 BLOCK 133;
SQL> ORADEBUG SETMYPID;
SQL> ORADEBUG TRACEFILE_NAME;

The trace now shows a locator pointer in the row and the actual data stored in a SecureFile BLOB segment.

Conclusions

Defining a column with the extended data type causes Oracle to create a SecureFile LOB segment automatically.

If the stored value is ≤ 4000 bytes, the data is kept in‑row (the DataInRow flag) inside the data block.

If the value exceeds 4000 bytes, Oracle stores the data in the LOB segment as chunks and the row contains only a locator pointer.

Unexpected conversion to extended types can generate large numbers of direct‑path LOB reads/writes; tuning the LOB segment (e.g., CACHE/NOCACHE) can reduce I/O overhead.

Enabling extended data types

Run the following commands as SYSDBA. The database must be started in upgrade mode to execute the script that creates the extended data type support.

SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> ALTER SYSTEM SET max_string_size=EXTENDED;
SQL> @?/rdbms/admin/utl32k.sql
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SHOW PARAMETER max_string_size;

The final query should return MAX_STRING_SIZE = EXTENDED.