oracle_fdwを用いてPostgreSQLからOracle Databaseにアクセスする

oracle_fdwは、PostgreSQLから外部Oracleサーバへのアクセスを可能にする外部データラッパです。
oracle_fdwを使用することによって、PostgreSQL側の操作でOracle上のテーブル・ビュー・マテリアライズドビューの読み書きができるようになります。
このページでは、oracle_fdwの使い方や注意点について紹介します。
※oracle_fdwの機能概要についてはこちらで解説していますので合わせてご覧ください。

oracle_fdwのメリット
oracle_fdwの使い方
注意事項
制限事項

oracle_fdwのメリット

PostgreSQL本体のみの場合、Oracleに接続する機能はデフォルトでは用意されていません。
一方、oracle_fdwを導入した場合、クライアント端末からPostgreSQLサーバ内の外部テーブルをSQL文で操作することにより、Oracleサーバ内のデータにアクセスできるようになります。

oracle_fdwの使い方

ここからは実際の使い方について説明していきます。

事前準備

外部テーブル使用のために必要な事前準備を行います。

1. oracle_fdwエクステンションの登録
PostgreSQLにoracle_fdwエクステンションを登録します。
postgresユーザーで以下を実行します。

$ psql
postgres=# CREATE EXTENSION oracle_fdw;

2. 外部サーバ作成
Oracleデータベースへの接続情報を定義します。

#外部サーバ作成
postgres=# CREATE SERVER ora_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'host1:1521/ORCLPDB1');

3. ユーザーマップ作成
PostgreSQLユーザー名とそれに対応するOracle上のユーザー情報を定義します。

postgres=# CREATE USER MAPPING FOR postgres SERVER ora_server OPTIONS (user 'ORA_USER', password '********');

postgres=# \deu+
                        ユーザーマッピング一覧
サーバー | ユーザー名 |               FDW オプション
------------+------------+--------------------------------------------
ora_server | postgres   | ("user" 'ORA_USER', password '********')

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の実行
postgres=# SELECT * FROM f_ora_tbl;
id | name |        update_time
----+--------+----------------------------
1 | user_1 | 2023-08-04 13:31:28.230864
2 | user_2 | 2023-08-04 13:31:38.111304

#SELECTの実行計画の確認 (SQLを実際に実行して確認する)
postgres=# EXPLAIN (analyze, verbose) SELECT * FROM f_ora_tbl;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.f_ora_tbl (cost=10000.00..20000.00 rows=1000 width=286) (actual time=1.093..1.128 rows=2 loops=1)
   Output: id, name, update_time
   Oracle query: SELECT /*34b484226103ddf0*/ r1."ID", r1."NAME", r1."UPDATE_TIME" FROM "ORA_USER"."ORA_TBL" r1
   Oracle plan: SELECT STATEMENT
   Oracle plan:   TABLE ACCESS FULL ORA_TBL
Planning Time: 2.943 ms
Execution Time: 1.194 ms
[補足]
・VERBOSEオプションなし: Oracle側でのクエリのみを表示
・VERBOSEオプションあり: Oracle側でのクエリと実行計画を表示

#INSERTの実行
postgres=# INSERT INTO f_ora_tbl VALUES (3,'user_3',current_timestamp);
INSERT 0 1
postgres=# SELECT * FROM f_ora_tbl;
id | name |        update_time
----+--------+----------------------------
1 | user_1 | 2023-08-04 13:31:28.230864
2 | user_2 | 2023-08-04 13:31:38.111304
3 | user_3 | 2023-08-22 15:25:21.934101

#UPDATEの実行
postgres=# UPDATE f_ora_tbl SET name = 'user_4' WHERE id = 3;
UPDATE 1
postgres=# SELECT * FROM f_ora_tbl;
id | name |        update_time
----+--------+----------------------------
1 | user_1 | 2023-08-04 13:31:28.230864
2 | user_2 | 2023-08-04 13:31:38.111304
3 | user_4 | 2023-08-22 15:25:21.934101

#DELETEの実行
postgres=# DELETE FROM f_ora_tbl WHERE id = 3;
DELETE 1
postgres=# SELECT * FROM f_ora_tbl;
id | name |        update_time
----+--------+----------------------------
1 | user_1 | 2023-08-04 13:31:28.230864
2 | user_2 | 2023-08-04 13:31:38.111304

プッシュダウン機能

プッシュダウン機能とは
プッシュダウンとはFDW (Foreign Data Wrapper, 外部データラッパ)の持つ機能の一つで、対象データソースサーバ側で各種データ処理を行わせる機能です。
以下に例を示します。

(1)WHERE句のプッシュダウン

postgres=# EXPLAIN (analyze, verbose) SELECT * FROM f_ora_tbl WHERE id = 1000000;
                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.f_ora_tbl (cost=10000.00..20000.00 rows=1000 width=286) (actual time=3.308..3.309 rows=0 loops=1)
   Output: id, name, update_time
   Oracle query: SELECT /*2aabc712f8ec0672*/ r1."ID", r1."NAME", r1."UPDATE_TIME" FROM "ORA_USER"."ORA_TBL" r1 WHERE (r1."ID" = 1000000)
   Oracle plan: SELECT STATEMENT
   Oracle plan:   TABLE ACCESS BY INDEX ROWID ORA_TBL
   Oracle plan:     INDEX UNIQUE SCAN SYS_C007849 (condition "R1"."ID"=1000000)
Planning Time: 3.229 ms
Execution Time: 3.396 ms

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;
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.f_ora_tbl (cost=10000.00..20000.00 rows=1000 width=286) (actual time=214.508..6635.159 rows=1000000 loops=1)
   Output: id, name, update_time
   Oracle query: SELECT /*67f4535eac8694d9*/ r1."ID", r1."NAME", r1."UPDATE_TIME" FROM "ORA_USER"."ORA_TBL" r1 ORDER BY r1."UPDATE_TIME" DESC NULLS FIRST
   Oracle plan: SELECT STATEMENT
   Oracle plan:   SORT ORDER BY
   Oracle plan:     TABLE ACCESS FULL ORA_TBL
Planning Time: 3.002 ms
Execution Time: 6711.482 ms

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;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=10038360.00..20075550.00 rows=1000000 width=72) (actual time=7250.052..18492.717 rows=1000000 loops=1)
   Output: t1.id, t1.name, t1.update_time, t2.id, t2.name, t2.update_time, t3.id, t3.name, t3.update_time
   Hash Cond: (t1.id = t3.id)
   -> Foreign Scan (cost=10000.00..10010000.00 rows=1000000 width=48) (actual time=258.279..10139.510 rows=1000000 loops=1)
         Output: t1.id, t1.name, t1.update_time, t2.id, t2.name, t2.update_time
         Oracle query: SELECT /*2535e9fc65ff84ae*/ r1."ID", r1."NAME", r1."UPDATE_TIME", r2."ID", r2."NAME", r2."UPDATE_TIME"
FROM ("ORA_USER"."ORA_TBL_1" r1 INNER JOIN "ORA_USER"."ORA_TBL_2" r2 ON (r1."ID" = r2."ID"))
         Oracle plan: SELECT STATEMENT
         Oracle plan:   HASH JOIN   (condition "R1"."ID"="R2"."ID")
         Oracle plan:     NESTED LOOPS
         Oracle plan:       NESTED LOOPS
         Oracle plan:         STATISTICS COLLECTOR
         Oracle plan:           TABLE ACCESS FULL ORA_TBL_1
         Oracle plan:         INDEX UNIQUE SCAN SYS_C008023 (condition "R1"."ID"="R2"."ID")
         Oracle plan:       TABLE ACCESS BY INDEX ROWID ORA_TBL_2
         Oracle plan:     TABLE ACCESS FULL ORA_TBL_2
   -> Hash (cost=10010000.00..10010000.00 rows=1000000 width=24) (actual time=6989.542..6989.546 rows=1000000 loops=1)
Output: t3.id, t3.name, t3.update_time
         Buckets: 65536 Batches: 16 Memory Usage: 3932kB
         -> Foreign Scan on public.f_ora_tbl_3 t3 (cost=10000.00..10010000.00 rows=1000000 width=24) (actual time=2.621..6550.015 rows=1000000 loops=1)
               Output: t3.id, t3.name, t3.update_time
               Oracle query: SELECT /*94a0058ec2ccbf6c*/ r4."ID", r4."NAME", r4."UPDATE_TIME" FROM "ORA_USER"."ORA_TBL_3" r4
               Oracle plan: SELECT STATEMENT
               Oracle plan:   TABLE ACCESS FULL ORA_TBL_3
Planning Time: 149.952 ms
Execution Time: 18541.471 ms

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);
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10028.89..10028.90 rows=1 width=8) (actual time=3.084..3.086 rows=1 loops=1)
   -> Nested Loop (cost=10000.42..10028.89 rows=2 width=0) (actual time=3.030..3.074 rows=2 loops=1)
         -> Foreign Scan on ora (cost=10000.00..10020.00 rows=2 width=4) (actual time=2.957..2.995 rows=2 loops=1)
               Oracle query: SELECT /*d73f63e37f8e763*/ r1."ID" FROM "ORA_USER"."ORA" r1 WHERE ((r1."ID" = 1) OR (r1."ID" = 1000000))
         -> Index Only Scan using pg_pkey on pg (cost=0.42..4.44 rows=1 width=4) (actual time=0.034..0.034 rows=1 loops=2)
               Index Cond: (id = ora.id)
               Heap Fetches: 0
Planning Time: 4.545 ms
Execution Time: 3.227 ms

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);
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10012641.83..10012641.84 rows=1 width=8) (actual time=3236.613..3236.668 rows=1 loops=1)
   -> Hash Join (cost=10016.81..10012641.83 rows=2 width=0) (actual time=6.666..3236.627 rows=2 loops=1)
         Hash Cond: (ora.id = pg.id)
         -> Foreign Scan on ora (cost=10000.00..10010000.00 rows=1000000 width=4) (actual time=2.869..3125.314 rows=1000000 loops=1)
               Oracle query: SELECT /*f139e4d3d79f78c4*/ r1."ID" FROM "ORA_USER"."ORA" r1
         -> Hash (cost=16.79..16.79 rows=2 width=4) (actual time=0.125..0.164rows=2 loops=1)
               Buckets: 1024 Batches: 1 Memory Usage: 9kB
               -> Bitmap Heap Scan on pg (cost=8.87..16.79 rows=2 width=4) (actual time=0.104..0.136 rows=2 loops=1)
                     Recheck Cond: ((id = 1) OR (id = 1000000))
                     Heap Blocks: exact=2
                     -> BitmapOr (cost=8.87..8.87 rows=2 width=0) (actual time=0.058..0.072 rows=0 loops=1)
                           -> Bitmap Index Scan on pg_pkey (cost=0.00..4.43 rows=1 width=0) (actual time=0.042..0.050 rows=1 loops=1)
                                 Index Cond: (id = 1)
                           -> Bitmap Index Scan on pg_pkey (cost=0.00..4.43 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=1)
                                 Index Cond: (id = 1000000)
Planning Time: 3.278 ms
Execution Time: 3237.099 ms

一方、良くない例の書き方をすると、外部テーブル側でのデータ絞り込みを先に行うことができないため、外部テーブルのデータを全件転送してしまいます。
実際に実行クエリを確認すると、Oracle側(oraテーブル)での件数絞り込みは行われていないことがわかります。
また、良い記述例の場合と比較すると処理時間もはるかに多くかかっていることがわかります。
外部テーブル結合を含む各SQL文については、実行計画を予め確認してから実行することをおすすめします。

注意事項

識別子の大文字/小文字
PostgreSQLとOracleとでは識別子の大文字・小文字の扱いに違いがあります。そのため、Oracle側のスキーマ名・テーブル名などはOracleのシステムカタログ通りに記述してください。

外部テーブル作成時における制約とデフォルト値
外部テーブルを作成する際の各列の制約およびデフォルト値はOracle側のテーブル定義に合わせてください。合わせなかった場合、下記のような問題が発生する場合があります。
・制約の定義を合わせない場合、外部テーブルでのUPDATEやDELETEができなくなる
・デフォルト値の定義を合わせない場合、外部テーブルでのINSERT時にOracle側テーブルに意図しないデータが入る

シリアライズエラーの発生と対処方法
(１) 新規レコードINSERT時におけるエラー発生
新規作成した(0レコードの)Oracleテーブルに対してPostgreSQL(oracle_fdw)から最初の行をINSERTするとシリアライズエラーが発生します。対処方法は以下のいずれかとなります。
・エラーを無視してINSERTを再実行する
・Oracle側のテーブル作成時にSEGMENT CREATION IMMEDIATE句を付けておく
(２) 複数トランザクションからの同時更新時におけるエラー発生
PostgreSQL(oracle_fdw)を使用して複数トランザクションから同じ外部テーブルを同時更新すると、シリアライズエラーが発生することがあります。対処方法は以下のいずれかとなります。
・複数トランザクションから同じ外部テーブルを同時更新しない
・エラーが発生したらトランザクションのROLLBACKを行い、再度トランザクションを実行する

制限事項

統計情報の自動更新
autovacuum実行に伴う自動ANALYZE処理は外部テーブルに対しては行われません。外部テーブルに対しては必要に応じて手動でANALYZEを行い、統計情報を更新して下さい。

ストアドプロシージャ・ユーザー定義関数
Oracle側にあるストアドプロシージャ・ユーザー定義関数をPostgreSQL側からoracle_fdw経由で実行することはできません。PostgreSQL側で上記を含むSQLを実行しても、プロシージャ・関数の未定義エラーとなります。

文字セット
PostgreSQL側の文字セットがOracle側で認識できないものである場合 (例. SQL_ASCII)、非ASCII文字は正しく変換できません。この場合、警告が表示されます。

サイト内の現在位置

PostgreSQL