Japan
サイト内の現在位置
PostgreSQL
oracle_fdwを用いてPostgreSQLからOracle Databaseにアクセスする
oracle_fdwは、PostgreSQLから外部Oracleサーバへのアクセスを可能にする外部データラッパです。
oracle_fdwを使用することによって、PostgreSQL側の操作でOracle上のテーブル・ビュー・マテリアライズドビューの読み書きができるようになります。
このページでは、oracle_fdwの使い方や注意点について紹介します。
※oracle_fdwの機能概要についてはこちらで解説していますので合わせてご覧ください。
oracle_fdwのメリット
PostgreSQL本体のみの場合、Oracleに接続する機能はデフォルトでは用意されていません。
一方、oracle_fdwを導入した場合、クライアント端末からPostgreSQLサーバ内の外部テーブルをSQL文で操作することにより、Oracleサーバ内のデータにアクセスできるようになります。

oracle_fdwの使い方
ここからは実際の使い方について説明していきます。
事前準備
外部テーブル使用のために必要な事前準備を行います。
1. oracle_fdwエクステンションの登録
PostgreSQLにoracle_fdwエクステンションを登録します。
postgresユーザーで以下を実行します。
$ psql postgres=# \dx |
2. 外部サーバ作成
Oracleデータベースへの接続情報を定義します。
#外部サーバ作成 #外部サーバの一覧を表示 |
3. ユーザーマップ作成
PostgreSQLユーザー名とそれに対応するOracle上のユーザー情報を定義します。
postgres=# CREATE USER MAPPING FOR postgres SERVER ora_server OPTIONS (user 'ORA_USER', password '********'); postgres=# \deu+ |
4. 外部テーブル作成
PostgreSQL上の外部テーブル名(仮想的なテーブル)とそれに対応するOracle上の接続先テーブル(テーブル実体)を定義します。
postgres=# CREATE FOREIGN TABLE f_ora_tbl( id integer OPTIONS (key 'true'), name varchar(128), update_time timestamp) SERVER ora_server OPTIONS (schema 'ORA_USER',table 'ORA_TBL'); postgres=# \d リレーション一覧 スキーマ | 名前 | タイプ | 所有者 ----------+---------------------------+--------------+---------- public | f_ora_tbl | 外部テーブル | postgres |
5. 基本的なSQLの動作確認
#SELECTの実行 |
プッシュダウン機能
プッシュダウン機能とは
プッシュダウンとはFDW (Foreign Data Wrapper, 外部データラッパ)の持つ機能の一つで、対象データソースサーバ側で各種データ処理を行わせる機能です。
以下に例を示します。
(1)WHERE句のプッシュダウン
postgres=# EXPLAIN (analyze, verbose) SELECT * FROM f_ora_tbl WHERE id = 1000000; |
Oracle queryはOracle側で実行されるクエリ、Oracle planはOracle側での実行計画を表しています。
Oracle queryにWHERE句があることとOracle planに検索条件があることから、WHERE句の処理がOracle側で行われている(=プッシュダウンされている)ことが確認できます。
[注意点]
プッシュダウン機能により、WHERE句内の処理がPostgreSQL内ではなくOracle内で実行される場合があります。
そのため、WHERE句内においてPostgreSQLとOracleで挙動が異なる関数を使用した場合、意図しない結果が発生する場合があります。
(2)ORDER BY句のプッシュダウン
postgres=# EXPLAIN (analyze, verbose) SELECT * FROM f_ora_tbl ORDER BY update_time DESC; |
Oracle queryにORDER BY句があることとOracle planにSORT ORDER BYがあることから、ORDER BY句の処理がOracle側で行われている(=プッシュダウンされている)ことが確認できます。
[注意点]
ソート対象列が数値(数値型, 日付型など)の場合はプッシュダウンされますが、文字列の場合はプッシュダウンされません (PostgreSQL側でソートされます)。
(3)JOIN句のプッシュダウン
postgres=# EXPLAIN (analyze, verbose) SELECT * FROM f_ora_tbl_1 t1 JOIN f_ora_tbl_2 t2 ON t1.id = t2.id JOIN f_ora_tbl_3 t3 ON t1.id = t3.id; |
JOIN句のプッシュダウンでは、テーブル3つ以上の結合の場合、テーブル2つの結合はプッシュダウンされ、それ以降の結合はPostgreSQL側で行われます。
実際に実行結果を見てみると、3行目のHash Cond: (t1.id = t3.id)より、3つめのテーブル (t3.id)の結合はPostgreSQL側で処理されていることがわかります。
また、1つ目のForeign ScanにおいてOracle queryにINNER JOIN、Oracle planにHASH JOINがあることから、最初の2つのテーブル結合(t1.id = t2.id)はOracle側で行われている(=プッシュダウンされている)ことが確認できます。
[注意点]
①テーブル結合時にプッシュダウン機能の対象とならないテーブル(例. 3つめ以降の結合対象テーブル)は、Oracle側で処理されずテーブル全件がPostgreSQL側に転送された後にPostgreSQL側で結合されます。そのため、oracle_fdw使用時には、PostgreSQL単体での使用時と比較して同じSQL文であっても処理速度が大幅に低下する場合があります。
②最適な実行計画策定のために、PostgreSQL側の外部テーブルの統計情報の更新を随時行ってください 。
データ転送量を減らすクエリの書き方
データ転送量はSQLの実行パフォーマンスに大きく影響します。クエリ記述の少しの違いによってデータ転送量に大きな違いが出ることもあるため注意が必要です。
具体例を外部テーブル(実体はOracleサーバ内)とPostgreSQL内部にあるテーブルを結合するクエリで説明します。

良い記述例
postgres=# EXPLAIN ANALYZE SELECT COUNT(*) FROM ora JOIN pg ON ora.id = pg.id WHERE (ora.id = 1 OR ora.id = 1000000); |
Oracle側の実行クエリを確認すると、プッシュダウン機能によりOracle側(oraテーブル)で件数絞り込みが行われていることがわかります。これにより、データ転送量が抑えられています(転送件数2件)。
良くない記述例
postgres=# EXPLAIN ANALYZE SELECT COUNT(*) FROM ora JOIN pg ON ora.id = pg.id WHERE (pg.id = 1 OR pg.id = 1000000); |
一方、良くない例の書き方をすると、外部テーブル側でのデータ絞り込みを先に行うことができないため、外部テーブルのデータを全件転送してしまいます。
実際に実行クエリを確認すると、Oracle側(oraテーブル)での件数絞り込みは行われていないことがわかります。
また、良い記述例の場合と比較すると処理時間もはるかに多くかかっていることがわかります。
外部テーブル結合を含む各SQL文については、実行計画を予め確認してから実行することをおすすめします。
注意事項
識別子の大文字/小文字
PostgreSQLとOracleとでは識別子の大文字・小文字の扱いに違いがあります。そのため、Oracle側のスキーマ名・テーブル名などはOracleのシステムカタログ通りに記述してください。
外部テーブル作成時における制約とデフォルト値
外部テーブルを作成する際の各列の制約およびデフォルト値はOracle側のテーブル定義に合わせてください。合わせなかった場合、下記のような問題が発生する場合があります。
・制約の定義を合わせない場合、外部テーブルでのUPDATEやDELETEができなくなる
・デフォルト値の定義を合わせない場合、外部テーブルでのINSERT時にOracle側テーブルに意図しないデータが入る
シリアライズエラーの発生と対処方法
(1) 新規レコードINSERT時におけるエラー発生
新規作成した(0レコードの)Oracleテーブルに対してPostgreSQL(oracle_fdw)から最初の行をINSERTするとシリアライズエラーが発生します。対処方法は以下のいずれかとなります。
・エラーを無視してINSERTを再実行する
・Oracle側のテーブル作成時にSEGMENT CREATION IMMEDIATE句を付けておく
(2) 複数トランザクションからの同時更新時におけるエラー発生
PostgreSQL(oracle_fdw)を使用して複数トランザクションから同じ外部テーブルを同時更新すると、シリアライズエラーが発生することがあります。対処方法は以下のいずれかとなります。
・複数トランザクションから同じ外部テーブルを同時更新しない
・エラーが発生したらトランザクションのROLLBACKを行い、再度トランザクションを実行する
制限事項
統計情報の自動更新
autovacuum実行に伴う自動ANALYZE処理は外部テーブルに対しては行われません。外部テーブルに対しては必要に応じて手動でANALYZEを行い、統計情報を更新して下さい。
ストアドプロシージャ・ユーザー定義関数
Oracle側にあるストアドプロシージャ・ユーザー定義関数をPostgreSQL側からoracle_fdw経由で実行することはできません。PostgreSQL側で上記を含むSQLを実行しても、プロシージャ・関数の未定義エラーとなります。
文字セット
PostgreSQL側の文字セットがOracle側で認識できないものである場合 (例. SQL_ASCII)、非ASCII文字は正しく変換できません。この場合、警告が表示されます。
関連ページ
Advancedサポートサービスのご紹介
PostgreSQL保守Advancedサポートサービスでは、PostgreSQL技術者による24時間365日体制のサポートのもと、PostgreSQLの技術的な問い合わせや、障害調査、pg_repackを含む指定のOSSツールのサポートに対応します。詳細はこちらをご覧ください。
その他周辺ツールの活用例
お問い合わせ