VARCHAR2(32767) – Sense or Nonsense?

Maximum Columnsize for VARCHAR2 is now 32k!

With Oracle12c you can define a column of data type VARCHAR2, NVARCHAR2 or RAW with a maximum length of 32767 Byte (but not CHAR!). But why is that useful? Datatype CLOB or NCLOB are fit better for pure text fields of a certain size. They can be handled in chunks instead of manipulating the entire text at once and with the SecureFile option which is now default for CLOB and BLOB in Oracle12c the storage is much more efficient.

Simply look at this example: if you really define a column with VARCHAR2(32767) and you fill it entirely and assume the database block size is still 8k as per default 5 blocks are needed to store one row (because you cannot use the entire 8k of a single block). That’s for sure not efficient.

But why do you want to define a column like that?

In last December I’ve done a migration from US7ASCII to Unicode at a customer side (more details in Blog: Using Umlaute in US7ASCII – doesn’t work, does it?) and we were facing exactly the problem that several tables were defined with VARCHAR2(4000). With the Unicode Migration the length does not longer fit as German Umlaute and the Euro-Symbol and other special characters exteeded the boarder of 4000 Byte.

Now we had the the following choices:

1. Change the datatype t CLOB: This would have let in a tremendous development effort and the database would have grown to approx. double the size. The reason for this is that CLOB in a Unicode database is stored in a format similar to UCS-2 (see for example Oracle Globalization Support Guide 11g Page 6-15: “Data in CLOB columns is stored in a format that is compatible with UCS-2 when the database character set is multibyte, such as UTF8 or AL32UTF8. This means that the storage space required for an English document doubles when the data is converted.“
2. Take the risk that text is truncated after 4000 Byte and crosscheck all texts afterwards.

After some discussion we took the choice two but if Oracle12c would have been available at that time we would have used that version instead.

Usage of VARCHAR2(32767)

If you try to create a table within Oracle12c you probably encounter the following error message:

SQL> create table my_text (
  2  id number(10) generated by default as identity,
  3  mytext varchar2(10000 CHAR));
meintext varchar2(10000 CHAR))
                  *
Error in Line 3:
ORA-00910: specified length too long for its datatype

The reason is that per default even Oracle12c only allows a column length of 4000 byte for VARCHAR2 and has to be migrated (!) to use extended data types.

To migrate the database the instance has to be stopped and started in UPGRADE mode. After “OPEN MIGRATE” the parameter MAX_STRING_SIZE=EXTENDED can be set. Before you can continu the data dictionary must be changed as well to use 32k column length.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE 
SQL> ALTER DATABASE OPEN MIGRATE; -- Only the Pluggable Database
SQL> ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;
SQL> @?/rdbms/admin/utl32k.sql

Now the CREATE TABLE should work as expected!

But this is a one way approach. The reset o MAX_STRING_SIZE (STANDARD) does not work. This should be kept in mind if you are going to use pluggable database because the upgrade is done in the CDB not the PDB. If you want to plug in the Pluggable database into another CDB that one has to be migrated as well.

The reason for migration is that internally any string length which exceeds 4000 Byte will be stored as CLOB including a corresponding Index. But in contrast to an ordinary CLOB the character set is not set to UCS-2 so there is no increase in storage size. But keep in mind that the table now consists of two parts: the “ordinary” table and the CLOB segment so the size of the table as shown in DBA_TABLES is incorrect.

Due to the limitation of indexes (Oracle Database Reference: Total Size of indexed colums = 75% of the database block size minus some Overhead) you might not be able to create an index on the VARCHAR2 field but again “Sense or Nonsense” having an B*Tree Index on a column of size of 32k?

Enjoy the new feature and please share your experiences with me.