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

PostgreSQLには、PostgreSQLの外部にある様々なデータに対してアクセスするための仕組みとして、外部データラッパー（FDW: Foreign Data Wrapper）が用意されています。その概要については、「外部データとの連携～FDWで様々なデータソースとつなぐ～」で紹介しています。今回は、Oracleデータベース用の外部データラッパーであるoracle_fdwを利用して、Oracleデータベースにアクセスする方法について解説します。

外部データとの連携～FDWで様々なデータソースとつなぐ～

1. oracle_fdwとは

oracle_fdwとは、Oracleデータベースに対応した外部データラッパーです。Oracleデータベース上のテーブルやビュー（マテリアライズド・ビューを含む）に対応する外部テーブルを作成し、SELECT文やINSERT文などのSQL文でアクセスすることで、Oracleデータベース上のデータを扱えるようになります。利用イメージを図1に示します。
oracle_fdwは、PostgreSQLクライアントから外部テーブルにアクセスがあると、「Oracle Call Interface（以降、OCIと略します）ライブラリー」を経由して、外部にあるOracleデータベースのテーブルやビューのデータを参照／更新します。なお、OCIライブラリーは、PostgreSQLサーバー上にインストールしておく必要があります。

図1 oracle_fdwの利用イメージ

oracle_fdwは、LinuxとWindowsに対応していますが、この記事ではLinux上での動作を前提に説明します。また、確認に使用した環境は、PostgreSQL 11.1、oracle_fdw 2.1.0、Oracle Instant Client 18.5（OCIライブラリー）、Oracle Database 18c XEです。

2. oracle_fdwの使い方

oracle_fdwを利用するための具体的な準備と利用手順について説明します。なお、PostgreSQLサーバーには、PostgreSQL、oracle_fdw、およびOCIライブラリーがインストールされているものとします。Oracleデータベースには、連携先のテーブルが存在し、リモート接続用のリスナー設定が完了しているものとします。

2.1 oracle_fdwを使うための準備

環境変数の設定

oracle_fdwからOCIライブラリーを利用するために、以下の環境変数を設定します。

環境変数：LD_LIBRARY_PATH
説明：OCIライブラリーのインストール先のディレクトリーを設定します。
設定例：/usr/lib/oracle/18.5/client64/lib
環境変数：NLS_LANG
説明：連携先Oracleデータベースの環境変数NLS_LANGと同じ値を設定します。
設定例：JAPANESE_JAPAN.AL32UTF8

Oracleデータベースにアクセスするための準備

oracle_fdwがOracleデータベースにアクセスするためには、図2のように、連携先の各種情報を4つの手順で設定します。なお、図中の括弧付き数字は、準備の手順を示しています。

図2 Oracleデータベースにアクセスするための準備

（1）oracle_fdwのエクステンション（EXTENTION）の作成: PostgreSQLのエクステンションとして、oracle_fdwを作成します。エクステンションの作成後には、外部データラッパーのリストを表示して、正しく作成されていることを確認します。

（2）外部サーバーの作成: Oracleデータベースへの接続情報を外部サーバー（ora_sv）として定義します。オプションを使って外部サーバーのホスト名（host_ora）、接続ポート（デフォルト値の1521）、データベースサービス名（XEPDB1）を指定します。外部サーバーの作成後には、外部サーバーのリストを表示して、正しく設定されていることを確認します。なお、ホスト名の名前解決が正しく行われるよう、事前にOSの設定を行っておきます。

さらに、PostgreSQL側で使用するローカルユーザー（postgres）が、定義された外部サーバー（ora_sv）を使用できるように、権限を付与します。

（3）ユーザーマップの作成: ローカルユーザー（postgres）とOracle側のリモートユーザー（ora_user）を紐づけるため、外部サーバー（ora_sv）上にユーザーマップを作成します。オプションでリモートユーザーのユーザー名とパスワードを指定します。ユーザーマップの作成後には、ユーザーマップのリストを表示して、正しく設定されていることを確認します。

（4）外部テーブルの作成: Oracleデータベース上のテーブルやビューに対応する、外部テーブル（f_ora_tbl）を作成します。なお、外部テーブルは、外部サーバー（ora_sv）上に関連づけて作成されます。外部テーブルの作成後には、外部テーブルのリストとテーブル定義を表示して、正しく設定されていることを確認します。

外部テーブルを作成する上で、以下の注意点があります。

テーブルカラム定義は、Oracle側のテーブルカラムと同じ種類のデータ型を定義する必要があります。
双方のデータベースのデータ型の仕様には違いがあります。外部テーブルとOracle側のテーブルとのデータ変換は、oracle_fdwによって自動で行われますが、正しく変換されるかどうか確認しておくことをお勧めします。
Oracle側のテーブルデータを更新する場合は、主キーカラムすべてにkeyオプションの設定が必要です。
OPTIONS (key 'true')で設定します。
OPTIONSに指定するOracleデータベースのスキーマ名（ORA_USER）とテーブル名（ORA_TBL）は、大文字 / 小文字に注意して記述してください。
Oracleデータベースでは、スキーマ名やテーブル名などの識別子をダブルクォーテーション無しで定義すると大文字で格納されます。そのため、連携先のOracleデータベースのスキーマ名やテーブル名がダブルクォーテーション無しで定義されていた場合は、外部テーブル作成時に大文字で記述する必要があります。なお、連携先のOracleデータベースにおいて、ダブルクォーテーションを付けてスキーマ名やテーブル名が定義されていた場合は、その定義どおりに記述します。なお、外部テーブル作成時に指定するスキーマ名とテーブル名は、コマンド構文の規則上、シングルクォーテーションを付けます。

参考

外部テーブルを作成する際に、スキーマ名とテーブル名をOracleデータベース上の定義どおりに指定しなくても、その時点ではエラーになりません。しかし、その外部テーブルにアクセスしたタイミングでエラーになります。以下に、その実行例を示します。

2.2 oracle_fdwを利用したOracleデータベースへのアクセス

外部テーブルを使うための準備ができましたので、次に、外部テーブルに対してSQL文の問合せを発行し、Oracleデータベースのテーブルにアクセスできることを確認します。図3に、oracle_fdwを利用したOracleデータベースへのアクセスについて、その処理の流れを示します。

図3 oracle_fdwを利用したOracleデータベースへのアクセス

（1）クライアントは、PostgreSQLに、外部テーブルに対するSQL文の問合せを行う
（2）PostgreSQLは、oracle_fdwに、Oracleデータベースの実行計画やテーブルデータの取得を依頼する
（3）oracle_fdwは、Oracle側のテーブルにアクセスするために必要な情報を、「外部サーバー」、「ユーザーマップ」から取得する
（4）oracle_fdwは、OCIライブラリーを経由してOracleデータベースにSQL文で問合せを行い、実データにアクセスする
（5）oracle_fdwは、Oracleデータベースから実行結果を取得し、PostgreSQLに結果を返す
（6）クライアントは、SQL文の実行結果をPostgreSQLから受け取る

実際にSQL文の問合せを行い、作成した外部テーブルの参照（SELECT）と更新（UPDATE）について確認します。

SELECT文で外部テーブル（f_ora_tbl）を参照します。

ここで、EXPLAINコマンドを使ってSELECT文の実行計画を表示して、Oracle側のテーブルにアクセスしていることを確認します。スキャン方式が「Foreign Scan」になっているところが、実際にOracle側のテーブルにアクセスする箇所になります。また、EXPLAINコマンドにANALYZEオプションを指定することにより、Oracle側で実行されるSQL文が確認でき（Oracle query: の箇所）、VERBOSEオプションを指定することにより、Oracle側での実行計画が確認できます（Oracle plan: の箇所）。

次に、UPDATE文で外部テーブル（f_ora_tbl）を更新し、データが更新されていることを確認します。

3. oracle_fdwを利用する上でのポイント

oracle_fdwの仕組みを正しく理解しないで利用した場合、外部テーブルへのアクセスに非常に時間が掛かったり、想定どおりの結果が得られなかったりすることがあります。ここでは、利用上の重要なポイントについて概要を述べます。詳細については、「Oracleデータベースにアクセスする～oracle_fdwを使いこなすために～」で解説します。

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

プッシュダウンの仕組み

プッシュダウンとは、クライアントから問合せのあったSQL文に含まれる「WHERE句」などの部分的な処理を、リモート側で実行させる機構です。以下の句は、oracle_fdwのプッシュダウンの対象となり、条件に応じてOracle側で実行されます。
「WHERE句」は、Oracle側で処理されます。
「ORDER BY句」は、対象カラムのデータ型が数値型のようにPostgreSQLとOracleデータベース間でソート順が同じ場合、かつ、JOIN句が無い場合に、Oracle側で処理されます。
「JOIN句」は、SELECT文において、同一外部サーバー上の2つまでのテーブル結合であれば、Oracle側で処理されます。
「LIMIT句」は、Oracle側でFETCH FIRST n ROWS ONLYに変換して処理されます（PostgreSQL 14以降）。
なお、「WHERE句」「JOIN句」「LIMIT句」は、ローカルとリモートとの間のデータ転送量を抑え、通信におけるボトルネックを減少させる効果があります。

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

oracle_fdwからOracleデータベースに問合せる際には、トランザクション分離レベルSERIALIZABLEで実行されます。そのため、1つの外部テーブルに対して複数のトランザクションから同時に更新すると、シリアライズ失敗によるエラーが発生することがあるため、注意が必要です。
また、プリペアドステートメント（PREPARE）、2相コミット（PREPARE TRANSACTIONなど）はサポートされません。

データ型の違い

外部テーブルを作成する際のカラムのデータ型は、Oracle側のカラムと同じ種類のデータ型で定義しますが、データ長や、端数の丸め方などに違いがあるため、注意が必要です。もし、文字型カラムにおいて、データ長を超えるようなアクセスがあると、ランタイムエラーが発生します。

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

外部テーブルで定義する制約（CHECK句、NOT NULL句など）やデフォルト値（DEFAULT句）は、Oracle側のテーブル定義に合わせることを推奨します。定義を合わせない場合、外部テーブル上の制約と、テーブル内のデータの状態に矛盾が発生すると、外部テーブルのUPDATEやDELETEができなくなる可能性があります。

4. 外部テーブルの一括作成機能

PostgreSQLの外部データラッパーには、外部テーブルをまとめて作成してくれる便利な機能があります。通常、CREATE FOREIGN TABLEコマンドを使用して外部テーブルを作成する際には、連携先のOracleデータベースの個々のテーブルカラムについて、同様のデータ型を定義する必要があります。そのため、テーブル数が多くなると、非常に手間が掛かる作業になります。そこで、IMPORT FOREIGN SCHEMAコマンドを使用することで、指定されたスキーマに含まれるすべてのテーブルを対象に、外部テーブルを生成してくれます。また、このコマンドのオプションで、スキーマに属するテーブルを個別に指定したり、個別に除外したりすることも可能です。なお、DEFAULT句については適用されないため、別途、外部テーブル定義に追加する必要があります。

以下に、IMPORT FOREIGN SCHEMAコマンドを使用して、Oracleデータベース上のORA_USERスキーマにある4つのテーブルを、PostgreSQLのimp_schemaにインポートする例を示します。なお、Oracleデータベースのスキーマ（ORA_USER）を指定する際には、Oracleデータベースのシステムカタログに定義されている情報（基本的には大文字）と同様に指定する必要があるため、ダブルクォーテーションで囲みます。
コマンド実行後は、外部テーブルのリストを表示して、正しく作成されていることを確認します。

oracle_fdwを利用することにより、PostgreSQLからOracleデータベースのテーブルに直接アクセスできることが分かりました。さらに詳細を知りたい場合は、「Oracleデータベースにアクセスする～oracle_fdwを使いこなすために～」を参照してください。PostgreSQLは、Oracleデータベース以外のデータベースや、RDB以外のデータソースとの連携が可能ですので、PostgreSQLの適用範囲を大きく広げることができます。適用シーンに合わせた利用をご検討ください。

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

2022年3月25日更新