e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第94回 「文字列連結の注意点(SQL文において バインド変数の使用)」

2014.04.21

こんにちは。インストラクターの蓑島です。

今日も前回に引き続き、文字列連結についてです。
題して、「文字列連結の注意点(SQL文において バインド変数の使用)」です。

前回は長い文字列を変数を連結して組み立てる際のコツについて解説しました。 この方法を使えば、長い文字列でも正確に変数を文字列連結できます。ただ、その場合、SQL文を文字列連結で組み立てる場合の考慮すべきことがあります。
今回はそのことをテーマに解説します。

それは、SQL文を文字列で組み立てる場合、表名とか列名ではなく「値」の部分については文字列の変数を連結する方法ではなく「バインド変数」で記述する方法が一般に推奨されるということです。
理由はパフォーマンスとセキュリティです。

実はバックナンバー第47回「動的SQLでのバインド変数の使用」でもこのようなことを説明していますが、今回は文字列連結がテーマなので、もう一度このことをテーマにしたいと思います。さらに今回はセキュリティについても、簡単に解説します。

それでは解説します。

たとえば前回の最後の説明に以下のような文字列連結がありました。

V_SQL := 'SELECT * FROM EMP WHERE ENAME = ''' || V_ENAME || '''';

V_ENMAE変数の値が仮に'SMITH'だとすると、「SELECT * FROM EMP WHERE ENAME = 'SMITH'」という文字列になります。

つまりこのSQL文の値の部分は'SMITH'であり、その値を文字列変数(V_EAME)を連結することで、SELECT文を組み立てたわけです。

しかし、このSQL文がV_ENAME変数の値を変えて何度も実行される場合はパフォーマンス上の問題点があります。
それはV_ENAME変数の値が異なると、違ったSQL文に組みあがるので、SQL文が共有できないという問題です。

たとえば、2回目の実行のときに、V_ENAME変数の値が'ALLEN'だとすると、「SELECT * FROM EMP WHERE ENAME = 'ALLEN'」という文字列になります。

そうすると、1回目、2回目あわせて以下の二つのSQL文が実行されることになります。

SELECT * FROM EMP WHERE ENAME = 'SMITH'
SELECT * FROM EMP WHERE ENAME = 'ALLEN'

上記の二つのSELECT文は厳密に比較される結果、値が違っているので、同一の文とはならず、SQLの解析結果が共有されません。
(その結果、ハード解析が行われます)

値が違うために共有されないSQL文が多ければ多いほど、ハード解析の負荷が増すのでパフォーマンスに対して悪い影響を与えます。

そこでこのような場合は、値の部分を「バインド変数」で記述します。すると変数の値は違っても、SQL文としては同一なので、ハード解析の負荷は増しません。
具体的には以下のような記述です。

V_SQL := 'SELECT * FROM EMP WHERE ENAME = :B1';

ここで「:B1」の部分がバインド変数です。
PL/SQLにおいてバインド変数はコロン(:)で始まればどのような名前でも結構です。

そして実行時にバインド変数の値を指定します。以下のようにです。

EXECUTE IMMEDIATE 'SELECT * FROM EMP WHERE ENAME = :B1' USING V_ENAME;

ここで、USINGで指定したV_ENAME変数の値をバインド変数:B1の値として実行しているわけです。
(上記の実行構文は過度に簡略化してます。構文の詳細は動的SQL関係のバックナンバー第43回~47回を参照してください)

このようにバインド変数を使うと、その値がなんであっても、SQL文としては一つの文なので解析結果を共有できます。
つまり値だけが違って何度も実行される文の場合、ハード解析の負荷を軽減し、一般にパフォーマンスを向上させることができます。

なお、主キー以外が問い合わせ条件となっているケースなどでは、解析結果が共有され実行計画が固定されることによりパフォーマンスがかえって悪化するケースも場合により発生します。
(データの分布に偏りが大きい場合) Oracle 11gからは「優れたカーソル共有」という機能でその問題に対処できますが、非常に細かい話なので今回は話題にしません。

またバインド変数はあくまでも、「値」の部分にしか使えないので、セキュリティに対する対策にもなります。
理由は「値」としてしか扱えないので、想定外の文字列を連結してSQL文の意味を変えてしまうような攻撃を防止する効果もあるからです。(SQLインジェクション対策)

SQL文に限らず、様々なケースで文字列連結は多用される技術ですが、特にSQL文を文字列連結で組み立てる場合は、「値」の部分は文字列連結ではなく、バインド変数を使うことを考慮してください。

また、すでに「値」の部分をバインド変数ではなく、文字列連結で対応しているシステムが運用されている場合で、ハード解析がパフォーマンス上の大きな課題となっている場合は、最善の解決策はアプリケーションを修正してバインド変数を使うようにすることです。
しかし、アプリケーションの修正には大きなコストがかかりますので、次善の策として以下のパラメータ設定でパフォーマンス改善を試みることが可能です。

ALTER SYSTEM SET CURSOR_SHARING = FORCE;

これにより、解析時に値の部分をバインド変数に置き換えますのでSQL文の共有が可能となり、パフォーマンスが向上する可能性があります。
このCURSOR_SHARINGパラメータ関連については、次回、簡単に解説したいと思います。

いかがですか?
SQL文を文字列で組み立てるとき、一般に「値」の部分はバインド変数が推奨であることがご理解いただけたと思います。
それでは今回はここまでにいたします。また次回、ご期待ください。

先頭へ戻る