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")と出力されており、内部的にはファンクション・インデックスが使用されているようです。