This blog discusses VARCHAR2(32767) data in relation to compression with Hybrid Columnar Compression (HCC) and Advanced LOB Compression (a feature of Advanced Compression).

The size of the LOB value, among other things, dictates where it is stored. The LOB value is either stored inline with the row data or outside the row. Data stored in a LOB is termed the LOB value. The value of an internal LOB may or may not be stored inline with the other row data. If you do not set DISABLE STORAGE IN ROW, and if the internal LOB value is less than approximately 4,000 bytes, then the value is stored inline. Otherwise, it is stored outside the row — the LOB value is automatically moved out of the row once it extends beyond approximately 4,000 bytes. (see here).

Advanced LOB Compression and VARCHAR2(32767)

Compression of VARCHAR2(32767) data is currently not supported for SecureFiles LOB segments using Advanced LOB Compression.

Hybrid Columnar Compression and VARCHAR2(32767)

When Hybrid Columnar Compression is enabled on a table/partition on a supported platform/storage*, Hybrid Columnar Compression will be applied to VARCHAR2(32767) data that is less than 4000 bytes (since that data will be inline).

The exception, for data that is larger than 4000 bytes, is the ALTER-COLUMN use case (see below) where Hybrid Columnar Compression will be applied to VARCHAR2(32767) data regardless of length.

The ALTER TABLE scenario is documented in the Oracle SQL Language Reference (see here):

When you increase the size of a VARCHAR2, NVARCHAR2, or RAW column to exceed 4000 bytes, Oracle Database performs an in-place length extension and does not migrate the inline storage to external LOB storage. This enables uninterrupted migration of large tables, especially after migration, to leverage extended data types. However, the inline storage of the column will not be preserved during table reorganization operations, such as CREATE TABLE … AS SELECT, export, import, or online redefinition. To migrate to the new out-of-line storage of extended data type columns, you must recreate the table using one of the aforementioned methods. The inline storage of the column will be preserved during table or partition movement operations, such as ALTER TABLE MOVE [[SUB]PARTITION], and partition maintenance operations, such as ALTER TABLESPLIT [SUB]PARTITION, ALTER TABLE MERGE [SUB]PARTITIONS, and ALTER TABLE COALESCE [SUB]PARTITIONS.

Note:

Oracle recommends against excessively increasing the size of a VARCHAR2, NVARCHAR2, or RAW column beyond 4000 bytes for the following reasons:

  • Row chaining may occur.
  • Data that is stored inline must be read in its entirety, whether a column is selected or not. Therefore, extended data type columns that are stored inline can have a negative impact on performance.

* Supported HCC platforms and storage includes: Exadata, SuperCluster, Pillar Axiom, ZFSSA storage, FS1 or Oracle Database Appliance (ODA)