04 February, 2017

Oracle LOB storage in row

Do you know how Oracle stores LOB data with "ENABLE STORAGE IN ROW" and "COMPRESS"/"NOCOMPRESS" options?

According to the Oracle documentation:

The maximum amount of LOB data stored in the row is the maximum
VARCHAR2 size (4000). This includes the control information as well as
the LOB value. If you indicate that the LOB should be stored in the row,
once the LOB value and control information is larger than approximately
4000, then the LOB value is automatically moved out of the row.

run with multiplier = 1 to fill table with 1000 rows size of 1000 bytes

check tablespaces size

run with multiplier = 10 to fill table with 1000 rows size of 10000 bytes

check tablespaces size

run with multiplier = 5 to fill table with 1000 rows size of 5000 bytes

check tablespaces size

Actual results:

With NOCOMPRESS option:

Tablespace name

Tablespace size

Tablespace size

Tablespace size

Tablespace size

Inserted 1 row sizeof 1 byte

+ 1000 rows size of 1K

+ 1000 rows size of 10K

+ 1000 rows size of 5K

XTEST_LOB

196608

196608

20185088

27525120

XTEST_DAT

65536

2097152

2097152

2097152

All records size of 1K (with size less than 4000 bytes) are stored in the DAT tablespace.
All records size of 10K and 5K (with size more than 4000 bytes) are in the LOB tablespace.

With COMPRESS option:

Tablespace name

Tablespace size

Tablespace size

Tablespace size

Tablespace size

Inserted 1 row sizeof 1 byte

+ 1000 rows size of 1K

+ 1000 rows size of 10K

+ 1000 rows size of 5K

XTEST_LOB

196608

196608

10747904

10747904

XTEST_DAT

65536

1048576

2097152

9437184

All records size of 1K (with size less than 4000 bytes) are stored in the DAT tablespace.
All records size of 10K (with compressed size more than 4000 bytes) are in the LOB tablespace and partially in DAT.
All records size of 5K (with compressed size less than 4000 bytes) are stored in the DAT tablespace.