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

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

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

問合せの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行になります。