Oracle Databaseの文字列長最大値の拡張

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を設定しします。このパラメーターには以下の特徴があります。

1. デフォルト値はSTANDARD
2. 動的に変更できない（＝インスタンスの再起動が必要）
3. 一度EXTENDEDに設定すると元に戻せない。
4. 事前に${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")と出力されており、内部的にはファンクション・インデックスが使用されているようです。