[Database] Dealing with very very long string lists using Database 12.2

原文はこちら。
https://blogs.oracle.com/datawarehousing/entry/dealing_with_very_very_long

---

Oracle Database 11gR2で、文字列値を取り扱うLISTAGG関数が導入されました。これは、行のグループから値を集計し、値が通常コンマまたはセミコロンで区切られた連結文字列を戻すために利用できます。また、独自の区切り記号を指定すれば、コード内でこれを判別できます。

さまざまなフォーラムやブログの投稿から、開発者に広く使用されているようです。しかし、多くの人が強調してきた重要な問題が1つあります。非常に大きな文字列を含むデータセットに対してLISTAGGを使用すると、長すぎるリストを作成する可能性があります。これにより、次のようなオーバーフローエラーが発生します。

ORA-01489: result of string concatenation is too long
ORA-01489: 文字列を連結した結果、長さが最大長を超えました

開発者やDBAにとってさらに面倒なことに、指定されたLISTAGG measure_expr内の値を連結するとORA-01489エラーが発生するかどうかを事前に判断することは非常に難しいため、（筆者自身を含め）多くの人がこの問題を解決するための回避策を提示しています。おそらく最も洗練されたシンプルなソリューションは、12cのMATCH_RECOGNIZE機能を使用することですが、これは12c Release 1を必要とするため、すべてのDBAや開発者が常に利用できるわけではありません。
問題を再現したい場合、そしてサンプルのSHスキーマにアクセスできる場合は、次のクエリを実行してみてください。

SELECT 
g.country_region, 
LISTAGG(c.cust_first_name||' '||c.cust_last_name, ',') WITHIN GROUP (ORDER BY c.country_id) AS Customer
FROM customers c, countries g
WHERE g.country_id = c.country_id
GROUP BY country_region
ORDER BY country_region;

この記事のすべてのサンプルは、サンプルのSHスキーマを使用しています。オンプレミス版のOracle Database 12c Release 2がリリースされると、OTNのデータベース・ホーム・ページから、ご使用のプラットフォーム用のサンプル・ファイルをダウンロードできるようになるはずです。筆者はLiveSQL上ですぐに使えるチュートリアルがあるのですが、現在非常に長い文字列を使用する上で技術的な問題が発生しています（LISTAGGワークショップを実行すると 'Data out of range' エラーが発生する）。問題を解決次第、このブログの記事を更新し、このチュートリアルへのリンクを追加します。

Oracle Live SQL
https://livesql.oracle.com/apex/livesql/file/index.html

What have we changed in 12.2?

ORA-01489エラーを解決する方法の一つは、単純にVARCHAR2オブジェクトのサイズを増やすことです。

Larger object sizes

VARCHAR2 オブジェクトの最大サイズはDatabaseのパラメータMAX_STRING_SIZEで決まります。以下のコマンドを使って環境の設定を確認できます。

show parameter MAX_STRING_SIZE

筆者のデモ環境では、以下のような結果が返ってきます。

NAME            TYPE   VALUE 
--------------- ------ -------- 
max_string_size string STANDARD

Oracle RDBMS 12.1.0.2以前では、 VARCHAR2 のサイズの上限は4Kでしたが、Oracle RDBMS 12.1.0.2でこの制限が拡大され、32Kになりました。この拡大によって、多くの問題が解決する可能性がありますが、DatabaseのパラメータMAX_STRING_SIZEを変更する必要があります。MAX_STRING_SIZE = EXTENDEDを指定すると、新たに32767 バイトを上限に設定変更できます。

ALTER SYSTEM SET max_string_size=extended SCOPE= SPFILE;

しかし、ビッグデータ・ソースへの関心が高まるにつれ、大規模なデータ・セットに対する問合せでLISTAGG機能を使用した場合に、エラーORA-01489の可能性が依然高いことは明らかです。必要なのは、LISTAGG関数内の構文がもっと豊富に提供されることですが、これは、Database 12c Release 2の一部として実装されています。

Better list management

12cR2では、文字列サイズの大きさゆえにエラーが発生しそうなリストの管理がより簡単になりました。利用可能な新しいキーワードをご紹介しましょう。

• ON OVERFLOW ERROR
• ON OVERFLOW TRUNCATE
• WITH COUNT vs. WITHOUT COUNT

これらの機能のそれぞれをもうちょっと深掘りしてみましょう。

1. Keeping Pre-12.2 functionality
文字列長が長すぎた場合に既存コードで引き続きエラーを返したい場合、すばらしいことに、これがデフォルトの挙動です。 LISTAGG 文字列の長さが VARCHAR2 の制限を超えた場合、標準的なエラーが返ります。

ERROR at line xxx:
ORA-01489: result of string concatenation is too long
ORA-01489: 文字列を連結した結果、長さが最大長を超えました

しかしながら、可能であれば、オーバーフロー発生時にエラーが発生することを完全に明確にするため、LISTAGGコードに "ON OVERFLOW ERROR"を追加することをお勧めします。

SELECT 
g.country_region,
LISTAGG(c.cust_first_name||’ ‘||c.cust_last_name, ‘,' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY c.country_id) AS Customer
FROM customers c, countries g
WHERE g.country_id = c.country_id
GROUP BY country_region
ORDER BY country_region;

デフォルトでは、truncation（切り詰め）機能は無効化されており、エラーの発生を抑止したい場合には既存コードを変更する必要があることに注意することが重要です。

2. New ON OVERFLOW TRUNCATE… keywords
4Kもしくは32Kの境界で値のリストを切り詰めたい場合には、新規追加されたキーワードON OVERFLOW TRUNCATEを使う必要があります。以下はその例です。

SELECT 
g.country_region,
LISTAGG(c.cust_first_name||’ ‘||c.cust_last_name, ‘,' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY c.country_id) AS Customer
FROM customers c, countries g
WHERE g.country_id = c.country_id
GROUP BY country_region
ORDER BY country_region;

切り詰めが発生すると、完全な値が切り詰められ、その時点でリストが切り捨てられたことをユーザーに示す方法を制御できます。 デフォルトでは、切り詰めが発生したことを示すインジケータとして3つのドット '...'を文字列に追加しますが、これを次のようにオーバーライドすることができます。

SELECT 
g.country_region,
LISTAGG(c.cust_first_name||' '||c.cust_last_name, ',' ON OVERFLOW TRUNCATE ‘***') WITHIN GROUP (ORDER BY c.country_id) AS Customer
FROM customers c, countries g
WHERE g.country_id = c.country_id
GROUP BY country_region
ORDER BY country_region;

文字列長が超過した際、12.2以前の既存の挙動のままエラーを返したい場合は、デフォルトの挙動に従うか、もしくはキーワードを使って明示的にエラーを返すことを宣言することができます（個人的には、デフォルトの挙動に依存しないほうがよいと考えています）。

SELECT 
g.country_region,
LISTAGG(c.cust_first_name||' '||c.cust_last_name, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY c.country_id) AS Customer
FROM customers c, countries g
WHERE g.country_id = c.country_id
GROUP BY country_region
ORDER BY country_region;

この場合、通常のエラーメッセージを生成します。つまり、12.2以前の挙動のままです。

ORA-01489: result of string concatenation is too long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.

もちろん、新しいキーワードを省略しても同じ挙動になります。

SELECT 
g.country_region,
LISTAGG(c.cust_first_name||’ ‘||c.cust_last_name, ‘,') WITHIN GROUP (ORDER BY c.country_id) AS Customer
FROM customers c, countries g
WHERE g.country_id = c.country_id
GROUP BY country_region
ORDER BY country_region;

以前と同様、この場合も通常のエラーメッセージを生成するので、12.2以前の挙動と同じです。

ORA-01489: result of string concatenation is too long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.

3. How many values are missing?
利用可能な領域に収まるようにリストから削除された値の個数を知る必要がある場合は、キーワード 'WITH COUNT' を使用できます。切り捨てられた文字列の最後に削除された値の個数が不要の場合、キーワード 'WITHOUT COUNT'を使用することができます。デフォルトの挙動は'WITHOUT COUNT'です。

SELECT 
g.country_region,
LISTAGG(c.cust_first_name||' '||c.cust_last_name, ',' ON OVERFLOW TRUNCATE'***' WITH COUNT) WITHIN GROUP (ORDER BY c.country_id) AS Customer
FROM customers c, countries g
WHERE g.country_id = c.country_id
GROUP BY country_region
ORDER BY country_region;

4. Do we split values when truncation occurs? （切り詰めが発生した場合に値は分割されるのか？）
いいえ、切り捨てを強制する場所を決定する際には、各値の全長を考慮に入れます。したがって、各国内の顧客名リスト作成時にLISTAGGを使用する場合、顧客のフルネームであるKeith Laker（ファーストネーム + ラストネーム）が常に含まれます。 完全な文字列（ファーストネーム + ラストネーム）をリストに追加するのに十分なスペースがなければ、文字列全体、 "Keith Laker"が削除され、切り捨てインジケータが挿入されます。 ラストネームが切り捨てもしくは削除された場合、ファーストネームだけが文字列の最後の値として存在することはありません。

5. How do we calculate the overall length of the string values?（文字列の全長の計算方法は？）
オーバーフローが発生したことを示す文字は値リストの後ろに追加されます。デフォルトの場合、その文字は3個のドット'...'です。オーバーフロー機能は、最大長からLISTAGG句の最後の完全な値の最後まで逆方向に移動した後、ユーザ定義の区切り文字（最終デリミタ）、ユーザ定義のオーバーフローインジケータ（切り捨てインジケータ）を追加した上で、 'WITH COUNT'による、リストから削除または切り捨てられた値の個数を示す出力を追加します。

Summary

Oracle Database 12c Release 2では、ORA-01489エラーに対し次の2つの方法で対処しました。

1. VARCHAR2オブジェクトのサイズを32Kに増やした
2. LISTAGGの拡張機能を使用して非常に長いリストの管理をより詳細に制御できるようにした

そして、新しいキーワードが追加されています。

• ON OVERFLOW TRUNCATE
• ON OVERFLOW ERROR （デフォルトの挙動）
• WITH COUNT（デフォルトの挙動）
• WITHOUT COUNT

うまくいけば、この新しい機能によって、長年にわたって作成された

ORA-01489: result of string concatenation is too long
ORA-01489: 文字列を連結した結果、長さが最大長を超えました

というエラーを解決するためのすばらしい回避策は、標準のSQL機能で置き換えられることでしょう。