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.
TwelveC 
Recent Comments