Oracle Databaseの文字列を格納する型はいくつかありますが、格納できる最大バイト数が異なります。従来面倒だったのは、PL/SQLプログラムの変数の最大値と、テーブル列型の最大値が違っていることでした。このためPL/SQLのVARCHAR2型の変数に格納したデータがテーブルのVARCHAR2型列に格納できないといった事態が発生していました。 Oracle Database 12cからこの制限が一部緩和しました。

データ型	最大バイト数 (11.2まで)	最大バイト数 (12.1から)	PL/SQLの最大長
CHAR	2000	2000	32767
NCHAR	2000	2000	32767
VARCHAR2	4000	32767	32767
NVARCHAR2	4000	32767	32767
RAW	2000	32757	32767

最大長の設定変更

文字列型の最大長のデフォルト値はOracle Database 12.1以降でも従来のバージョンと同じになっています。最大長を変更するには、初期化パラメーターmax_string_sizeを設定しします。このパラメーターには以下の特徴があります。

デフォルト値はSTANDARD
動的に変更できない（＝インスタンスの再起動が必要）
一度EXTENDEDに設定すると元に戻せない。
事前に${ORACLE_HOME}/rdbms/admin/utl32k.sqlの実行が必要

格納フォーマット

拡張された文字列型データの格納方法には以下の2種類があります。

CREATE TABLE

最初から従来の制限を超える列を定義した場合、インラインBLOB型のセグメントに格納されます。LOBセグメントの物理フォーマットは格納される表領域のSECUREFILE設定に依存します。

SQL> CREATE TABLE extend1(c1 NUMBER, c2 VARCHAR(5000)) ;

表が作成されました。

SQL> SELECT object_name, object_type FROM USER_OBJECTS ;
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
SYS_IL0000073935C00002$$       INDEX
SYS_LOB0000073935C00002$$      LOB
EXTEND1                        TABLE

SQL> SELECT COLUMN_NAME, IN_ROW FROM USER_LOBS ;
COLUMN_NAME                    IN_ROW
------------------------------ ------
C2                             YES

ALTER TABLE

後から列を拡張した場合は、従来通りのフォーマットでテーブルに格納されます。文字列の拡張を行いたいが、LOBに格納したくない場合はこちらを利用してください。

SQL> CREATE TABLE extend1(c1 NUMBER, c2 VARCHAR(4000)) ;

表が作成されました。

SQL> ALTER TABLE extend1 MODIFY c2 VARCHAR2(5000) ;

表が変更されました。

SQL> SELECT object_name, object_type FROM USER_OBJECTS ;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
EXTEND1                        TABLE

実際にはCREATE TABLE文で隠しパラメーター_scalar_type_lob_storage_threshold（デフォルト4000）を超える場合にBLOBセグメントが作成されます。

インデックスの作成

　内部的にLOBが使われる列（拡張データ型）に対してインデックスを作成すると何の問題も無く作成されます。

SQL> CREATE INDEX idx1_extend1 ON extend1(c2) ;

索引が作成されました。

実行計画を確認します。

SQL> SET AUTOTRACE ON
SQL> SELECT * FROM extend1 WHERE c2='TEST' ;

レコードが選択されませんでした。

実行計画
----------------------------------------------------------
Plan hash value: 229613465

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)|Time    |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |  2515 | 1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EXTEND1      |     1 |  2515 | 1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX1_EXTEND1 |     1 |       | 1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"='TEST')
       filter(INTERNAL_FUNCTION("C2"))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

実行計画を見ると、INTERNAL_FUNCTION("C2")と出力されており、内部的にはファンクション・インデックスが使用されているようです。