Oracleデータベースにアクセスする～oracle_fdwを使いこなすために～

PostgreSQLには、PostgreSQLの外部にある様々なデータに対してアクセスするための仕組みとして、外部データラッパー（FDW: Foreign Data Wrapper）が用意されています。Oracleデータベースにアクセスするための外部データラッパーであるoracle_fdwの使い方については、「Oracleデータベースにアクセスする～oracle_fdwの基本的な使い方～」で紹介しています。

Oracleデータベースにアクセスする～oracle_fdwの基本的な使い方～

今回は、oracle_fdwをより効果的に利用するためのポイントについて解説します。
なお、確認に使用した環境は、PostgreSQL 11.1、oracle_fdw 2.1.0、Oracle Instant Client 18.5（OCIライブラリー）、Oracle Database 18c XEです。

1. プッシュダウン（push down）の仕組み

外部データラッパーには、プッシュダウンという機構があり、oracle_fdwにも備わっています。この機構は、クライアントから問合せのあったSQL文に含まれるWHERE句（検索条件）、ORDER BY句（ソート条件）、および、JOIN句（結合条件）の処理を、リモート側で実行させます。なお、WHERE句とJOIN句については、ローカルとリモートとの間のデータ転送量を抑え、通信におけるボトルネックを減少させる効果があります。例えば、外部テーブルの1000件のデータに対してWHERE句で10件に絞り込むようなSELECT文を実行する場合、Oracle側で絞り込みを行い、その結果をPostgreSQL側に送信することで、通信量を100分の1に抑えることができます。
以下に、WHERE句、ORDER BY句、および、JOIN句のプッシュダウンについて、利用上のポイントを解説します。

1.1 WHERE句のプッシュダウン

問合せのSQL文にWHERE句があると、WHERE句に記述されたステートメントがOracleデータベースに渡され、実行されます。その際に注意が必要な点は、WHERE句のステートメントに関数が使われていると、その関数もOracle側で実行されることです。PostgreSQLとOracleデータベースでは、同じ関数名であっても仕様に違いがある場合があります。そのため、WHERE句のステートメントに関数がある場合は、Oracle側の関数仕様を確認しておく必要があります。

ここで、EXPLAINコマンドで実行計画を出力することで、WHERE句がプッシュダウンされることを確認してみます。なお、ANALYZEおよびVERBOSEオプションを指定することにより、Oracle側で実行されるSQL文（Oracle query:）と実行計画（Oracle plan:）も出力します。「Foreign Scan」ブロックがOracle側のテーブルに対する実行計画であり、「Oracle query:」にWHERE句があり、「Oracle plan:」のところに検索条件があることから、WHERE句に記述されたステートメントがOracle側で実行されていることを確認できます。

次に、WHERE句のステートメントにRPAD関数を使用したときの実行結果を見てみます。この関数は、OracleデータベースとPostgreSQLにおいて、マルチバイト文字を使用した場合の実行結果が異なります。以下のSQL文のように、WHERE句ステートメントに、PostgreSQLの仕様を意識して「RPAD(name, 8, '*') = 'あいう*****'」と指定しても、このSQL文が実行されるときにWHERE句ステートメントがプッシュダウンされるため、RPAD関数はOracle側で実行されます。nameカラムに「あいう」というデータが存在しているとき、RPAD(name, 8, '*')の実行結果は「あいう**」になり、WHERE句の条件に合いません。そのため、このSQL文の実行結果は0行になります。

1.2 ORDER BY句のプッシュダウン

問合せのSQL文にORDER BY句があると、プッシュダウンされてOracle側でソートされます。ただし、ソート対象になるカラムのデータ型が「文字型」のときには、OracleデータベースとPostgreSQLのソート順が同じであることをoracle_fdwは保証できないため、プッシュダウンされません。ソート対象カラムのデータ型が、「数値型」やcurrent_timestampなどの定型の「日付/時刻型」のように、ソート順が同じであることを保証できる場合にはプッシュダウンされます。なお、その他のデータ型でソートする場合は、別途、動作確認が必要です。
また、SELECT文内においてORDER BY句とJOIN句を併用した場合は、ソート対象カラムのデータ型に関わらずORDER BY句がプッシュダウンされないため、注意が必要です。

ここで、ORDER BY句がプッシュダウンされることを確認してみます。まずは、ソート対象カラムが「数値型」の場合についての実行計画を見てみます。「Foreign Scan」ブロックの「Oracle query:」にORDER BY句があり、「Oracle plan:」のところに「SORT ORDER BY」があることから、ソートがOracle側で行われていることが確認できます。

次に、ソート対象カラムが「文字型」の場合についての実行計画を見てみます。こちらは、PostgreSQL側の実行計画に「Sort Method: quicksort」があり、Oracle側の実行計画にORDER BYに関する情報が無いことから、PostgreSQL側でソートされていることが確認できます。

1.3 JOIN句のプッシュダウン

問合せのSQL文にJOIN句があると、JOIN句に記述されたステートメントがOracleデータベースに渡され、Oracle側で結合が実行される場合があります。JOIN句のプッシュダウンには、以下の制約などがあるため、確認しておく必要があります。

JOIN句がSELECT文内にあること
結合対象のテーブルが同じ外部サーバー上で定義されていること
2つのテーブルの結合であること（3つ目からのテーブル結合はPostgreSQL側で行われる）
同一のSELECT文内にJOIN句とWHERE句がある場合は、JOIN句と共にWHERE句もプッシュダウンされる
同一のSELECT文内にJOIN句とORDER BY句がある場合は、JOIN句はプッシュダウンされるが、ORDER BY句はプッシュダウンされない
結合条件が無いクロス結合のときはプッシュダウンされない

ここで、同一の外部サーバー上で定義されている3つの外部テーブルを結合する例について、実行計画を確認してみます。まず、1つ目の「Foreign Scan」ブロック内の「Oracle query:」にJOIN句があり、「Oracle plan:」のところに「MERGE JOIN」および「SORT JOIN」があることで、2つまでの外部テーブル（t1, t2）がOracle側で結合されていることが確認できます。また、もう１つの外部テーブル（t3）については、2つ目の「Foreign Scan」ブロック内にJOIN句の処理が存在せず、かつ、PostgreSQLの実行計画に「Hash Join」および「Hash Cond」があることから、PostgreSQL側で結合されていることが分かります。

また、同一のSELECT文にJOIN句とORDER BY句がある場合に、JOIN句はプッシュダウンされ、ORDER BY句はプッシュダウンされない例について、実行計画を確認してみます。JOIN句については、「Foreign Scan」ブロック内に該当の実行計画があることから、プッシュダウンされていることが確認できます。しかし、ORDER BY句については、PostgreSQL側の実行計画に「Sort Method: quicksort」があることから、プッシュダウンされていないことが確認できます。

2. 更新トランザクションの利用

oracle_fdwは更新トランザクションにも対応しています。ただし、PostgreSQLへの単一のSQL文の問合せであっても、oracle_fdwからOracleデータベースへのSQL文の問合せは複数になる場合があることから、実行結果の一貫性保証の上で、Oracle側へのトランザクション分離レベルはSERIALIZABLEで実行されます。そのため、1つの外部テーブルに対して複数のトランザクションから同時に更新すると、以下の図のようにシリアライズ失敗によるエラーが発生することがあります。
このような問題を回避するためには、アプリケーション側にて、以下のどちらかの対処が必要です。

外部テーブルを同時更新しないようにする
例外「シリアライズの失敗 SQLSTATE(40001)」が発生したら、ROLLBACKして、再度トランザクションを実行する（Oracle側のエラー：「ORA-08177: can't serialize access for this transaction」）

図1 シリアライズ失敗によるエラーの例

またoracle_fdwは、プリペアドステートメント（PREPARE）、および、2相コミット（PREPARE TRANSACTIONなど）については、Oracleデータベースを含む制御が必要になるため、サポートしていません。

3. データ型の違い

外部テーブルを作成する際、Oracleデータベースのテーブルカラムのデータ型に応じて、適切なデータ型で定義する必要があります。以下の表に、oracle_fdwが変換可能なデータ型の組み合わせを示します。なお、外部テーブルと、Oracle上の実テーブルとの間のデータ型の変換は、実際に外部テーブルにアクセスする際に自動で行われます。ただし、データの長さが実際に格納できるカラム長を超えた場合にはランタイムエラーが発生するため、注意が必要です。また、浮動小数点データ型や日付時刻データ型における端数の丸め方など、データ型の振る舞いが違う点にも注意してください。

表1 Oracleデータベースに対するPostgreSQLの変換可能なデータ型
Oracleデータベースのデータ型	PostgreSQLの変換可能なデータ型
CHAR	char, varchar, text
NCHAR	char, varchar, text
VARCHAR	char, varchar, text
VARCHAR2	char, varchar, text, json
NVARCHAR2	char, varchar, text
CLOB	char, varchar, text, json
LONG	char, varchar, text
RAW	uuid, bytea
BLOB	bytea
BFILE	bytea (read-only)
LONG RAW	bytea
NUMBER	numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<＝0	numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text
FLOAT	numeric, float4, float8, char, varchar, text
BINARY_FLOAT	numeric, float4, float8, char, varchar, text
BINARY_DOUBLE	numeric, float4, float8, char, varchar, text
DATE	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH LOCAL TIME ZONE	date, timestamp, timestamptz, char, varchar, text
INTERVAL YEAR TO MONTH	interval, char, varchar, text
INTERVAL DAY TO SECOND	interval, char, varchar, text
MDSYS.SDO_GEOMETRY	geometry（サポート範囲に制約があるため、詳細についてはoracle_fdwのドキュメントの"PostGIS support"を確認してください）

参考

Oracleデータベースの文字型CHARやVARCHAR2の長さはデフォルトではバイト単位で指定します。それに対して、PostgreSQLの文字型CHAR、VARCHAR、TEXTの長さは文字単位で指定します。例えば、Oracle上の実テーブルのカラムがVARCHAR2(64)型、外部テーブルのカラムがVARCHAR(64)型として対応している場合に、INSERT文で、外部テーブル上のそのカラムに51文字のひらがなを指定したとします。すると、oracle_fdwがデータ変換を行う際に、Oracle側で64バイトしかないカラムに153バイトのデータ（UTF-8文字コードの場合）を格納することになり、以下のエラーが発生します。そのため、外部テーブル定義を行う際には、扱うデータの大きさを考慮する必要があります。

4. 外部テーブル定義の制約とデフォルト値

外部テーブルを作成する際の制約（CHECK句、NOT NULL句など）とデフォルト値（DEFAULT句）については、連携先のOracle側のテーブルで定義されている制約とデフォルト値に合わせることが推奨されています。以下に、制約チェックとデフォルト値適用についてのタイミングを示し、次に、この推奨に従わない場合の問題点について述べます。

4.1 制約チェックとデフォルト値適用のタイミング

制約がチェックされるタイミング、および、デフォルト値の適用のタイミングについて示します。図2の（1）のSQL文のように、INSERT文で、NOT NULL制約のあるemp_idカラムにNULL値を指定し、デフォルト値10の定義されたtagカラムにdefault値を指定した例について説明します。

図2 外部テーブル定義の制約チェックとデフォルト値適用のタイミング

制約（主キー、NOT NULLなど）については、oracle_fdwではチェックされず、Oracleデータベースにてチェックされます。
図2の例では、Oracleデータベース側にて、EMP_IDカラムのNOT NULL制約がチェックされて制約違反になります。
デフォルト値については、oracle_fdwで適用され、そのデータがoracleデータベースに渡されます。
図2の例では、oracle_fdwにて、tagカラムのデフォルト値10が適用され、Oracleデータベースには10という値が渡されます。

参考

図2のINSERT文の制約違反エラーのメッセージを示します。DETAILに「ORA-01400:」があることから、Oracleデータベース側でエラーが発生していることが分かります。

4.2 推奨に従わない場合の問題点

外部テーブルを定義する際に、制約やデフォルト値を、連携先のOracle側のテーブル定義の設定に合わせて指定しなかった場合、以下のような問題が発生する場合があるため、注意が必要です。

主キー制約を正しく指定しない場合

実際に、外部テーブル上の制約定義を連携先に合わせて定義しない場合でも、Oracle側で制約違反が起きない限りSQL文の問合せは正しく動作します。以下の例のように、外部テーブル上で、Oracle側の実テーブルとは異なるカラムに主キー制約を定義しても、INSERTは実行可能です。しかし、INSERTによって、外部テーブル上の主キー制約とデータの状態に矛盾が発生した場合、UPDATEやDELETEはエラーになります。

デフォルト値を正しく指定しない場合

図3のように、外部テーブルを定義する際にtagカラムのDEFAULT句を省略した場合、INSERT時に入力値を省略（あるいは、defaultキーワードを指定）すると、oracle_fdwはOracle側にNULL値を渡します。すると、Oracle側のDEFAULT句が適用されないため、結果的に、意図しないデータとしてNULL値が格納されてしまいます。

図3 外部テーブル定義でDEFAULT句を指定しなかった場合の問題点

5. その他の利用時のポイント

oracle_fdwを利用する上での、その他のポイントを以下に示します。

統計情報の更新は自動で行われません
通常のテーブルは、autovacuumプロセスが実行されるときに、合わせてANALYZEも実行され、統計情報の更新が行われますが、外部テーブルは対応していません。そのため、定期的にANALYZEコマンドを実行するよう、運用に組み込む必要があります。
連携先にしかないストアドプロシージャやユーザーファンクションはoracle_fdw経由では実行できません
Oracle側にあるストアドプロシージャやユーザーファンクション（既存のファンクションも含む）を記述したSQL文を実行する場合、PostgreSQLのパーサはOracle側の定義を参照しないため、それらの定義が存在しない旨のエラーになります。
外部テーブルスキャンにて大量にデータを取得すると性能に影響を与えます
PostgreSQLとOracleデータベースとの間の1回の通信で取得できる行数はデフォルトで200行になっているため、大量のデータ取得は性能への影響があります。oracle_fdwは、Oracleデータベースの行プリフェッチを使用して実装しており、外部テーブルのOPTIONSでprefetchを指定することで、0から10240行の間でフェッチ行数が指定できます。0を指定するとプリフェッチが無効になります。値を大きくすることでパフォーマンスが向上しますが、PostgreSQLサーバーでより多くのメモリを使用します。

oracle_fdwを実際に利用することを想定し、考慮すべきいくつかの重要な点について解説しました。詳細な点については、oracle_fdwのドキュメントを参照の上、ご利用ください。

2020年3月27日公開