Starting in Oracle Database 12c Release 1 (12.1) Oracle introduced the ability to have “Extended Data Types” which support a maximum size of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. You can control whether your database supports this new maximum size by setting the initialization parameter MAX_STRING_SIZE as follows:
If MAX_STRING_SIZE=STANDARD, then the size limits for releases prior to Oracle Database 12c apply: 4000 bytes for the VARCHAR2 and NVARCHAR2 data types, and 2000 bytes for the RAW data type. This is the default.
If MAX_STRING_SIZE=EXTENDED, then the size limit is 32767 bytes for VARCHAR2, NVARCHAR2 and RAW data types.
For more information, see the Oracle documentation on Extended Data Types here and a blog on the topic by Andy Rivenes, Database In-Memory Product Manager here.
While this topic is not typically what I would discuss in my “everything compression” blog, I do think it is important to understand the consequences of the in-place migration of VARCHAR2, NVARCHAR2 or RAW columns to exceed 4000 bytes.
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 TABLE SPLIT [SUB]PARTITION, ALTER TABLE MERGE [SUB]PARTITIONS, and ALTER TABLE COALESCE [SUB]PARTITIONS.
It is important to note that Oracle recommends against excessively increasing the size of a VARCHAR2, NVARCHAR2, or RAW column beyond 4000 bytes since 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.
For more information about out-of-line storage of LOBS using SecureFiles, as well as more information on SecureFiles migration, please see these Oracle White Papers.
SecureFiles Migration White Paper
The database storage optimization adventure continues.