Tim Hall perfectly describes in his excellent post how new extended datatypes are stored on Oracle 12c.
I just found interesting parameter “_scalar_type_lob_storage_threshold“ – “threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB” – This parameter is the max size in bytes, at which these data types will be stored “inline” as simple datatypes, without creation of the lob segments.
See little example:
[sourcecode language=”sql” highlight=”5,15,20,28,54″]
SQL> @param_ _scalar_type_lob_storage_threshold;
NAME VALUE DEFLT TYPE DESCRIPTION
—————————————- ———— ———— ———— ————————————————————
_scalar_type_lob_storage_threshold 4000 TRUE number threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB
SQL> select * from user_lobs;
no rows selected
SQL> create table T_4000(
2 i int generated always as identity
3 ,v1000 varchar2(1000)
4 ,v4000 varchar2(4000)
5 ,v4500 varchar2(4500)
6 );
Table created.
SQL> alter system set "_scalar_type_lob_storage_threshold"=5000;
System altered.
SQL> create table T_5000(
2 i int generated always as identity
3 ,v1000 varchar2(1000)
4 ,v4000 varchar2(4000)
5 ,v4500 varchar2(4500)
6 );
Table created.
SQL> select table_name,column_name ,data_type,data_type_mod,data_length,char_col_decl_length,char_length,char_used
2 from user_tab_columns;
TABLE_NAME COLUMN_NAM DATA_TYPE DAT DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
———- ———- ———- — ———– ——————– ———– –
T_4000 V4500 VARCHAR2 4500 4500 4500 B
T_4000 V4000 VARCHAR2 4000 4000 4000 B
T_4000 V1000 VARCHAR2 1000 1000 1000 B
T_4000 I NUMBER 22 0
T_5000 V4500 VARCHAR2 4500 4500 4500 B
T_5000 V4000 VARCHAR2 4000 4000 4000 B
T_5000 V1000 VARCHAR2 1000 1000 1000 B
T_5000 I NUMBER 22 0
8 rows selected.
SQL> select table_name,column_name,chunk,retention,cache,logging,encrypt,compression,deduplication,in_row,securefile
2 from user_lobs;
TABLE_NAME COLUMN_NAM CHUNK RETENTION CACHE LOGGING ENCR COMPRE DEDUPLICATION IN_ SEC
———- ———- ———- ———- ———- ——- —- —— ————— — —
T_4000 V4500 8192 YES YES NO NO NO YES YES
[/sourcecode]
Note that there are no lobs for table t_5000!