Extended Datatypes

Prior to Oracle 12c, VARCHAR2/NVARCHAR2 datatypes  allowed storing variable length characters of up to 4000 bytes whereas RAW data type  allowed storing variable length binry data of up to  2000 bytes.  In Oracle 12c, the maximum size for data types VARCHAR2, NVARCHAR2  and RAW is increased to 32767 bytes.  These data types are now referred to as extended data types. Extended data types are implicitly implemented using concept of LOBs;  Just like LOB’s, any data stored in excess of  4000 bytes for VARCHAR2/NVARCHAR2/RAW is stored out-line. If the data stored is within pre-Oracle12c limits , then they are stored in-line.  So be aware about the restrictions of LOBs since all most all of the restrictions of lobs are applicable to  extended datatypes (at least for now).

The first thing that came to mind about extended data types is what purpose does it actually serve since it is implemented as LOB; Why would you introduce LOB restrictions to your columns by increasing the limits of VARCHAR2, NVARCHAR2  and RAW. As most of you are aware that all versions of databases including Oracle 12c do  not support changing VARCHAR2/NUMBER/RAW  to LOBs directly with ALTER ..MODIFY command. (Would result in ORA-22858: invalid alteration of datatype); I thought that  instead of  extended datatype ,Oracle could have removed the restriction to convert VARCHAR2/NUMBER/RAW  to LOBs directly and provided some restricted form of index support.

Being a new feature, I did not find much useful information about the feature and so decided why not be one of the earliest blogger to explore this topic.

So let us make sure that  the database is  enabled for extended datatypes.

SQL> show parameter max_string_size

NAME                                 TYPE        VALUE

———————————— ———– ——————————

max_string_size                      string      EXTENDED

Please refer to following blog on instructions of how to set up your database for extended datatypes. https://twelvec.com/2014/02/08/oracle-12c-how-to-setup-your-database-to-support-extended-datatypes/

Now lets create a table with two columns , one with VARCHAR2 and other with LOB

SQL> CONN SHAN/SHAN

Connected.

SQL> CREATE TABLE EXT_DATATYPE (

2  EMPLOYEE_NO VARCHAR2(32767) ,

3  EMPLOYEE_COMMENTS  BLOB);

Table created.

 Ok now lets trying adding primary key to this table. Ooops , It failed because of the restrictions on  index lengths.

SQL> ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_NO);

ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_NO)

*

ERROR at line 1:

ORA-01450: maximum key length (6398) exceeded

Now lets modify the column size to 6398 bytes and add primary key to the table. The command still fails; So what is the secret column length that supports indexes? It is actually  6389 bytes.

SQL>  ALTER TABLE EXT_DATATYPE MODIFY  (EMPLOYEE_NO VARCHAR2(6398));

Table altered.

SQL>  ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_NO);

ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_NO)

ERROR at line 1:

ORA-01450: maximum key length (6398) exceeded

For VARCHAR2/NVARCHR2/RAW columns more that 6389 bytes , you can create indexes using function based indexes or virtual columns. With both approaches, you are shortening the length of column to create indexes using either SUBSTR or new STANDARD_HASH functions or something creative that you can come up with.

Let us revert  the column length to  time trusted 4000 bytes, add index and then try to increase the column length. You are going to get a different error but the underlying cause us same.

SQL>  ALTER TABLE EXT_DATATYPE MODIFY  (EMPLOYEE_NO VARCHAR2(4000));

Table altered.

SQL>  ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_NO);

Table altered.

SQL> ALTER TABLE EXT_DATATYPE MODIFY  (EMPLOYEE_NO VARCHAR2(6390));

ALTER TABLE EXT_DATATYPE MODIFY  (EMPLOYEE_NO VARCHAR2(6390))

*

ERROR at line 1:

ORA-01404: ALTER COLUMN will make an index too large

Now let us drop our primary key constraint and  try adding lob data type as primary key; It will fail as LOB columns cannot be primary key or unique key.

SQL> alter table EXT_DATATYPE drop primary key;

Table altered.

SQL> ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_COMMENTS));

ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_COMMENTS))

ERROR at line 1:

ORA-01735: invalid ALTER TABLE option

SQL> ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_COMMENTS);

ALTER TABLE EXT_DATATYPE  ADD PRIMARY KEY (EMPLOYEE_COMMENTS)

ERROR at line 1:

ORA-02329: column of datatype LOB cannot be unique or a primary key

Summary: In essence, It if would have been better if Oracle increased the datatypes sizes to 6000 bytes instead of 32K so that indexing , primary key constraints are all supported.  There is always a learning curve with new features;  it gets little more complicated with enhancements.  With 32K sizes ,Initially it is going to introduce new risks and create confusion if implemented without understanding the actual consequences even though there are some workarounds for creating indexes using  virtual columns or function based indexes.  I just picked one restriction of LOBs ; More testing to be done to verify the behavior of AFTER UPDATE DML trigger , INSERT AS SELECT and many more restrictions.

Tagged: @?/rdbms/admin/utl32k.sql, extended datatypes, extended datatypes oracle 12c, MAX_STRING_SIZE=STANDARD, standard_hash, standrd_hash