oracle_fdw を使ってみる(後編)

oracle_fdw 紹介の後編です。
前編では oracle_fdw を PostgreSQL に導入・外部サーバ定義・ユーザマッピング・外部テーブル定義を順に行い Oracleデータベース上のテーブルに PostgreSQL からアクセスする流れを紹介しました。
今回は、機能をもう少し深堀りをしていきます。

本記事で使用するデータは以下の通りです。これらテーブルが Oracle側に在って、対応する同名のoracle_fdw外部テーブルがPostgreSQL側に作成されています。

  • ● EMP表
      EMP_NO EMP_NAME                       DEPT_CD         POST_CD       SALARY LAST_MODIFIED
  ---------- ------------------------------ --------------- --------- ---------- -------------------
           1 Lion                                           001            10000 2018/08/15 11:00:00
           2 Tiger                                          002             7000 2018/08/15 11:00:00
           3 Fox                            00005           003             5000 2018/08/15 11:10:00
           4 Pig                            00005           005             2000 2018/08/15 11:15:30
           5 Dog                            00002           004             3500 2018/08/15 11:25:00
           6 Cat                            00001           004             3500 2018/08/15 11:25:30
           7 Cow                            00006           003             5200 2018/08/15 11:30:15
           8 Rat                            00004           005             2000 2018/08/15 11:45:00
  • ● DEPT表
  DEPT_CD         DEPT_NAME
  --------------- ------------------------------
  00001           総務部
  00002           経理部
  00003           営業部
  00004           広報部
  00005           人事部
  00006           開発部
  • ● POST表
  POST_CD   POST_NAME
  --------- ------------------------------
  001       社長
  002       専務
  003       部長
  004       課長
  005       係長
  006       一般社員

実行プランを調べてみる

PostgreSQLでは EXPLAIN文を使って SQLの実行計画を確認することができます。oracle_fdw は EXPLAIN にも対応していて、PostgreSQL側の EXPLAIN で Oracle側で実行されるSQLや実行計画などの情報を参照できます。

oracle_fdw の外部テーブルを使った SQL の実行計画がどのように見えるかを調べて見ます。

  db1=> explain SELECT * FROM emp;
                                                                                   QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Foreign Scan on emp  (cost=10000.00..20000.00 rows=1000 width=178)
     Oracle query: SELECT /*582407e117cf2d80398b826784bcb7fc*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY", r1."LAST_MODIFIED" FROM "ORAUSER"."EMP" r1
  (2 行)

empテーブルをスキャンするために Oracle側で実行する SQL が「Oracle query:」の行に報告されます。

  db1=> explain (analyze) SELECT * FROM emp;
                                                                                   QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Foreign Scan on emp  (cost=10000.00..20000.00 rows=1000 width=178) (actual time=3.096..3.189 rows=8 loops=1)
     Oracle query: SELECT /*582407e117cf2d80398b826784bcb7fc*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY", r1."LAST_MODIFIED" FROM "ORAUSER"."EMP" r1
   Planning time: 1.687 ms
   Execution time: 3.223 ms
  (4 行)

SQLを実際に実行してみた結果を報告する analyzeオプションを付与した場合は、上記のように PostgreSQL側から見た所要時間が報告されいます。

  db1=> explain (analyze, verbose) select * from emp;
                                                                                   QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Foreign Scan on pguser.emp  (cost=10000.00..20000.00 rows=1000 width=178) (actual time=0.603..0.650 rows=8 loops=1)
     Output: emp_no, emp_name, dept_cd, post_cd, salary, last_modified
     Oracle query: SELECT /*582407e117cf2d80398b826784bcb7fc*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY", r1."LAST_MODIFIED" FROM "ORAUSER"."EMP" r1
     Oracle plan: SELECT STATEMENT
     Oracle plan:   TABLE ACCESS FULL EMP
   Planning time: 1.085 ms
   Execution time: 0.673 ms
  (7 行)

verboseオプションをつけると、「Oracle plan:」という行が出力されます。
これがOracle側の実行計画です。
本当にそうであるか、Oracle側で確認してみます。
「Oracle query:」の行に出力されているSQL文にコメント「/*582407e117cf2d80398b826784bcb7fc*/」が挿入されているので、これをOracleで検索します。

 SQL> SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%582407e117cf2d80398b826784bcb7fc%';

  SQL_ID          SQL_TEXT
  --------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------
  2zk7rdr3gtgvd   SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%582407e117cf2d80398b826784bcb7fc%'
  fd3s5h31jvhy1   SELECT /*582407e117cf2d80398b826784bcb7fc*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY", r1."LAST_MODIFIED" FROM "ORAUSER"."EMP" r1

上記の SQL_ID が「fd3s5h31jvhy1」の行が、PostgreSQLで実行したSQLです。このSQLの実行計画を確認します。

  SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('fd3s5h31jvhy1', 0));

  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  SQL_ID  fd3s5h31jvhy1, child number 0
  -------------------------------------
  SELECT /*582407e117cf2d80398b826784bcb7fc*/ r1."EMP_NO", r1."EMP_NAME",
  r1."DEPT_CD", r1."POST_CD", r1."SALARY", r1."LAST_MODIFIED" FROM
  "ORAUSER"."EMP" r1

  Plan hash value: 3956160932

  --------------------------------------------------------------------------
  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
  |   1 |  TABLE ACCESS FULL| EMP  |     8 |   552 |     3   (0)| 00:00:01 |
  --------------------------------------------------------------------------

  Note
  -----
     - dynamic sampling used for this statement (level=2)


  19行が選択されました。

「verbose」オプション付きで EXPLAINを実行したときに出力された「Oracle plan:」は、確かにOracle側の実行計画であることがわかりました。
この例のように、実行したSQLがどのようにOracleで実行されているのかを確認したいときにはSQL文に付与されたコメントの文字列を使って調べることができます。

プランナ統計情報

PostgreSQLではバージョン9.2以降から外部テーブルについてもプランナ統計情報の取得が可能となりました。oracle_fdwではプランナ統計情報にも対応しています。通常のテーブルと同様に ANALYZEコマンドを使用します。ただし、autovacuumプロセスによる自動ANALYZEには対応していない点に注意が必要です。定期的な ANALYZEコマンド実行を運用に組み入れるようにしましょう。

それでは oracle_fdw にANALYZEをしたときの挙動を見ていきましょう。

  db1=> explain (analyze) SELECT * FROM emp;
                                                                                   QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Foreign Scan on emp  (cost=10000.00..20000.00 rows=1000 width=178) (actual time=1.450..1.499 rows=8 loops=1)
     Oracle query: SELECT /*582407e117cf2d80398b826784bcb7fc*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY", r1."LAST_MODIFIED" FROM "ORAUSER"."EMP" r1
   Planning time: 190.054 ms
   Execution time: 1.524 ms
  (4 行)

これは外部テーブルを作った直後、未だプランナ統計情報が無い状態で実行しています。
EXPLAIN (analyze) の出力では、「(actual time= … )」の部分に実際の所要時間と行数、ループ数が報告されます。
応答先頭行の「cost=10000.00..20000.00 rows=1000 width=178」部分の rowsを見ると、実際の行数が 8件であるにもかかわらず 1000件存在している想定で実行計画を立てています。1000件というのは不明な場合のデフォルトの件数です。

次に ANALYZEを実行してから、再度 EXPLAIN を実行してみます。

  db1=> ANALYZE emp;
  ANALYZE

  db1=> explain (analyze) SELECT * FROM emp;
                                                                                   QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Foreign Scan on emp  (cost=10000.00..10080.00 rows=8 width=34) (actual time=0.900..0.991 rows=8 loops=1)
     Oracle query: SELECT /*582407e117cf2d80398b826784bcb7fc*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY", r1."LAST_MODIFIED" FROM "ORAUSER"."EMP" r1
   Planning time: 1.592 ms
   Execution time: 1.023 ms
  (4 行)

統計情報のと実際の件数が一致しました。oracle_fdw外部テーブルでもプランナ統計情報が機能しているといえます。

Push-down機能

oracle_fdwにより Oracleデータベース上のテーブルにアクセスする SQLが実行できました。
このとき、SQL処理過程でどの部分が Oracleで実行されて、どの部分が PostgreSQLで実行されるのかが気になるところです。
100万件のデータから20件が絞り込まれるようなSQLを実行したとき、以下のパターンが考えられますができることなら Oracle側で絞り込みを行った結果の20件のみをPostgreSQLが取得できる方が良いでしょう。

  • (A) Oracleで絞り込みを実行した結果の20件をPostgreSQLが取得
  • (B) Oracleから100万件のデータを取得し、PostgerSQLで20件に絞り込みを実行

検索条件による絞込みをより末端の方で行わせることを push-down(プッシュダウン)と呼びます。
上記例でいえば (B) であったものを (A) の処理にするのが push-down です。

oracle_fdwでは、すべてのケースではないですが WHERE句の push-downに対応しています。
ここから、実際にpush-down が行われていることを EXPLAIN文を使って確認していきます。

WHERE句のpush-down

WHERE句の条件を指定した場合について、確認してみます。
emp表から 所属(dept_cd列)が 00005(=人事部)であるような社員を抽出する SQL を実行します。

    db1=> select * from emp where dept_cd = '00005';
                                                                                                     QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Foreign Scan on pguser.emp  (cost=10000.00..20000.00 rows=1000 width=178) (actual time=1.636..1.656 rows=2 loops=1)
       Output: emp_no, emp_name, dept_cd, post_cd, salary, last_modified
       Oracle query: SELECT /*4b7ea9f8203e1ae2db752c3c52185bd8*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY", r1."LAST_MODIFIED" FROM "ORAUSER"."EMP" r1 WHERE (r1."DEPT_CD" = '00005')
       Oracle plan: SELECT STATEMENT
       Oracle plan:   TABLE ACCESS FULL EMP  (filter "R1"."DEPT_CD"='00005')
     Planning time: 194.733 ms
     Execution time: 1.685 ms
    (7 行)

「Oracle plan:」に「(filter “R1”.”DEPT_CD”=’00005′)」と出力されていることから、絞り込みがpush-downされていることがわかります。

続いては問題が起きそうなケースを試してみます。PostgreSQLとOracleで結果が異なる関数を実行するとどうなるでしょうか。
substr関数は PostgreSQL と Oracle で仕様が違います。そのため、以下のように結果が変わってきます。

  • PostgreSQLのsubstr関数実行結果
    db1=> SELECT emp_no, emp_name, substr(emp_name, -3, 2) FROM emp WHERE substr(emp_name, -3, 2) = 'io';
     emp_no | emp_name | substr
    --------+----------+--------
    (0 行)
  • Oracleのsubstr関数実行結果
    SQL> SELECT EMP_NO, EMP_NAME, SUBSTR(EMP_NAME, -3, 2) FROM EMP WHERE SUBSTR(EMP_NAME, -3, 2) = 'io';

        EMP_NO EMP_NAME                       SUBSTR(EMP_NAME,-3,2)
    ---------- ------------------------------ ------------------------
             1 Lion                           io

この SUBSTR関数を WHERE句の条件に指定したとき、PostgreSQLとOracleのどちらで実行されるでしょうか。

    db1=> SELECT emp_no, emp_name, substr(emp_name, -3, 2) FROM emp WHERE substr(emp_name, -3, 2) = 'io';
     emp_no | emp_name | substr
    --------+----------+--------
          1 | Lion     |
    (1 行)

    db1=> explain (verbose) SELECT emp_no, emp_name, substr(emp_name, -3, 2) FROM emp WHERE substr(emp_name, -3, 2) = 'io';
                                                                             QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
     Foreign Scan on pguser.emp  (cost=10000.00..20002.50 rows=1000 width=154)
       Output: emp_no, emp_name, substr((emp_name)::text, '-3'::integer, 2)
       Oracle query: SELECT /*aadff49a5fa2767492d5844f517ca51c*/ r1."EMP_NO", r1."EMP_NAME" FROM "ORAUSER"."EMP" r1 WHERE (substr(r1."EMP_NAME", -3, 2) = 'io')
       Oracle plan: SELECT STATEMENT
       Oracle plan:   TABLE ACCESS FULL EMP  (filter SUBSTR("R1"."EMP_NAME",-3,2)='io')
    (5 行)

本例では WHERE句の条件が push-downされたことにより、SUBSTR関数は Oracleで実行されました。
その結果該当データが1件となっています。
この振る舞いは push-downの有無により応答が変わる可能性があるということであり、アプリケーションの不具合の原因となります。
対策としては関数がどちらで実行される可能性もありうるということを考慮にいれて、関数の仕様差異があらわれる使い方を避けることです。

ORDER BY句のpush-down

PostgreSQL9.2以降から ORDER BY句の push-down が可能になり、oracle_fdwでも対応しています。
ただし、文字列で並び替えを行う場合には push-down されません。

以下の定義となっている外部テーブルの数値型列(emp_no)と文字型列(emp_name)に対して ORDER BY の push-down を試してみます。

     db1=> \d+ emp
                                                             外部テーブル "pguser.emp"
           列       |               型               | 照合順序 | Null 値を許容 | デフォルト | FDW オプション | ストレージ | 統計の対象 | 説明
     ---------------+--------------------------------+----------+---------------+------------+----------------+------------+------------+------
      emp_no        | integer                        |          | not null      |            | (key 'true')   | plain      |            |
      emp_name      | character varying(50)          |          |               |            |                | extended   |            |
      dept_cd       | character varying(5)           |          |               |            |                | extended   |            |
      post_cd       | character varying(3)           |          |               |            |                | extended   |            |
      salary        | bigint                         |          |               |            |                | plain      |            |
      last_modified | timestamp(0) without time zone |          |               |            |                | plain      |            |
     サーバー: oradb112
     FDW オプション: (schema 'ORAUSER', "table" 'EMP')
  • ORDER BY句に数値型カラム(emp_no)を指定
  db1=> explain (analyze, verbose) SELECT * FROM emp ORDER BY emp_no;
                                                                                               QUERY PLAN
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       Foreign Scan on pguser.emp  (cost=10000.00..20000.00 rows=1000 width=170) (actual time=3.169..3.225 rows=7 loops=1)
         Output: emp_no, emp_name, dept_cd, post_cd, salary
         Oracle query: SELECT /*e721c330da0d10a646db6bccb997a7e3*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY" FROM "ORAUSER"."EMP" r1 ORDER BY r1."EMP_NO" ASC NULLS LAST
         Oracle plan: SELECT STATEMENT
         Oracle plan:   SORT ORDER BY
         Oracle plan:     TABLE ACCESS FULL EMP
       Planning time: 3.001 ms
       Execution time: 3.267 ms
      (8 行)
  • ORDER BY句に文字列型カラム(emp_name)を指定
db1=> explain (analyze, verbose) SELECT * FROM emp ORDER BY emp_name;
                                                                                QUERY PLAN
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
       Sort  (cost=20049.83..20052.33 rows=1000 width=170) (actual time=2.003..2.005 rows=7 loops=1)
         Output: emp_no, emp_name, dept_cd, post_cd, salary
         Sort Key: emp.emp_name
         Sort Method: quicksort  Memory: 25kB
         ->  Foreign Scan on pguser.emp  (cost=10000.00..20000.00 rows=1000 width=170) (actual time=1.876..1.928 rows=7 loops=1)
               Output: emp_no, emp_name, dept_cd, post_cd, salary
               Oracle query: SELECT /*f26579891b741d370f31c72dea761fe0*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD", r1."POST_CD", r1."SALARY" FROM "ORAUSER"."EMP" r1
               Oracle plan: SELECT STATEMENT
               Oracle plan:   TABLE ACCESS FULL EMP
       Planning time: 1.684 ms
       Execution time: 2.055 ms
      (11 行)

数値型カラムの場合だけ「Oracle plan:」に「SORT ORDER BY」が出力されていることが確認できました。

結合のpush-down

PostgreSQL9.6以降では、以下の条件を満たすとき、結合においてもpush-downが動作します。

  • SELECT文であること
  • 結合対象のテーブルは、同一の外部サーバに定義されていること。
  • 結合は内部結合(inner join)であること。
  • 結合は2つまでであること
  • 結合がpush-downされるとき、ORDER BYはpush-downされない

条件を満たすSQLを実行してみます。

    db1=> explain verbose
    db1-> select e.emp_no, e.emp_name, d.dept_name
    db1->   from emp e
    db1->  inner join dept d on e.dept_cd = d.dept_cd;
                                                                                                QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Foreign Scan  (cost=10000.00..20000.00 rows=1000 width=240)
       Output: e.emp_no, e.emp_name, d.dept_name
       Oracle query: SELECT /*1f2567bfb61910168f19b658c678aa90*/ r1."EMP_NO", r1."EMP_NAME", r2."DEPT_NAME" FROM ("ORAUSER"."EMP" r1 INNER JOIN "ORAUSER"."DEPT" r2 ON (r1."DEPT_CD"= r2."DEPT_CD"))
       Oracle plan: SELECT STATEMENT
       Oracle plan:   HASH JOIN   (condition "R1"."DEPT_CD"="R2"."DEPT_CD")
       Oracle plan:     TABLE ACCESS FULL DEPT
       Oracle plan:     TABLE ACCESS FULL EMP
    (7 行)

問題なくpush-downされていることが確認できます。

条件違反になるように、結合を3つにして実行するとpush-downは行われないのでしょうか。試してみます。

    db1=> explain verbose
    db1-> select e.emp_no, e.emp_name, d.dept_name
    db1->   from emp e
    db1->  inner join dept d on e.dept_cd = d.dept_cd
    db1->  inner join post p on e.post_cd = p.post_cd;
                                                                                                  QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Hash Join  (cost=30012.50..40177.50 rows=5000 width=126)
       Output: e.emp_no, e.emp_name, d.dept_name
       Hash Cond: ((e.dept_cd)::text = (d.dept_cd)::text)
       ->  Foreign Scan  (cost=10000.00..20000.00 rows=1000 width=14)
             Output: e.emp_no, e.emp_name, e.dept_cd
             Oracle query: SELECT /*6c04b217ae3b4d3ffd35b76e854e0966*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD" FROM ("ORAUSER"."EMP" r1 INNER JOIN "ORAUSER"."POST" r4 ON (r1."POST_CD" = r4."POST_CD"))
             Oracle plan: SELECT STATEMENT
             Oracle plan:   NESTED LOOPS
             Oracle plan:     TABLE ACCESS FULL EMP
             Oracle plan:     INDEX UNIQUE SCAN PK_POST (condition "R1"."POST_CD"="R4"."POST_CD")
       ->  Hash  (cost=20000.00..20000.00 rows=1000 width=142)
             Output: d.dept_name, d.dept_cd
             ->  Foreign Scan on pguser.dept d  (cost=10000.00..20000.00 rows=1000 width=142)
                   Output: d.dept_name, d.dept_cd
                   Oracle query: SELECT /*8e88abaedea07ba4637cb0f885201300*/ r2."DEPT_CD", r2."DEPT_NAME" FROM "ORAUSER"."DEPT" r2
                   Oracle plan: SELECT STATEMENT
                   Oracle plan:   TABLE ACCESS FULL DEPT
    (17 行)

emp表とpost表の結合、depto表の参照に分かれてpush-dowされ、それぞれの結果をPostgreSQL側で結合しているようです。

今度は、外部結合(left join)を実行を試してみます。

    db1=> explain verbose
    db1-> select e.emp_no, e.emp_name, d.dept_name
    db1->   from emp e
    db1->  left outer join dept d on e.dept_cd = d.dept_cd;
                                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Hash Right Join  (cost=20080.10..30086.65 rows=30 width=126)
       Output: e.emp_no, e.emp_name, d.dept_name
       Hash Cond: ((d.dept_cd)::text = (e.dept_cd)::text)
       ->  Foreign Scan on pguser.dept d  (cost=10000.00..20000.00 rows=1000 width=142)
             Output: d.dept_cd, d.dept_name
             Oracle query: SELECT /*8e88abaedea07ba4637cb0f885201300*/ r2."DEPT_CD", r2."DEPT_NAME" FROM "ORAUSER"."DEPT" r2
             Oracle plan: SELECT STATEMENT
             Oracle plan:   TABLE ACCESS FULL DEPT
       ->  Hash  (cost=10080.00..10080.00 rows=8 width=14)
             Output: e.emp_no, e.emp_name, e.dept_cd
             ->  Foreign Scan on pguser.emp e  (cost=10000.00..10080.00 rows=8 width=14)
                   Output: e.emp_no, e.emp_name, e.dept_cd
                   Oracle query: SELECT /*f8e48586f7f43a704253aec0be7a2a79*/ r1."EMP_NO", r1."EMP_NAME", r1."DEPT_CD" FROM "ORAUSER"."EMP" r1
                   Oracle plan: SELECT STATEMENT
                   Oracle plan:   TABLE ACCESS FULL EMP
    (15 行)

emp表、dept表への参照がそれぞれpush-downされ、PostgreSQLで外部結合が行われました。

最後に、結合と”order by”を同時に行います。

    db1=> explain verbose
    db1-> select e.emp_no, e.emp_name, d.dept_name
    db1->   from emp e
    db1->  inner join dept d on e.dept_cd = d.dept_cd
    db1->  order by e.emp_no;
                                                                                                   QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=20049.83..20052.33 rows=1000 width=126)
       Output: e.emp_no, e.emp_name, d.dept_name
       Sort Key: e.emp_no
       ->  Foreign Scan  (cost=10000.00..20000.00 rows=1000 width=126)
             Output: e.emp_no, e.emp_name, d.dept_name
             Oracle query: SELECT /*1f2567bfb61910168f19b658c678aa90*/ r1."EMP_NO", r1."EMP_NAME", r2."DEPT_NAME" FROM ("ORAUSER"."EMP" r1 INNER JOIN "ORAUSER"."DEPT" r2 ON (r1."DEPT_CD" = r2."DEPT_CD"))
             Oracle plan: SELECT STATEMENT
             Oracle plan:   HASH JOIN   (condition "R1"."DEPT_CD"="R2"."DEPT_CD")
             Oracle plan:     TABLE ACCESS FULL DEPT
             Oracle plan:     TABLE ACCESS FULL EMP
    (10 行)

結合はpush-downされますが、「order by」はPostgreSQLで実行されることがわかりました。

このように、条件を満たさない SQL の場合には、push-down は部分的にだけ行われます。
少しでも条件に反したら全く push-downしないわけではありません。

更新とトランザクション

oracle_fdwは更新SQLにも対応しています。
以下のように PostgreSQL から Oracle側のデータを変更できます。

db1=> INSERT INTO post VALUES ('007', 'アルバイト');
INSERT 0 1
db1=> SELECT * FROM post ;
post_code | post_name
-----------+------------
001 | 社長
002 | 専務
003 | 部長
004 | 課長
005 | 係長
006 | 一般社員
007 | アルバイト
(7 rows)

トランザクションにも対応していて、PostgreSQLで更新内容をロールバックすると、Oracle側でもロールバックされます。以下に例を示します。

db1=> BEGIN;
BEGIN
db1=> DELETE FROM post WHERE post_code = '007';
DELETE 1
db1=> SELECT * FROM post ;
 post_code | post_name
-----------+-----------
 001       | 社長
 002       | 専務
 003       | 部長
 004       | 課長
 005       | 係長
 006       | 一般社員
(6 rows)

db1=> ROLLBACK;
ROLLBACK


SQL> SELECT * FROM post WHERE post_id = '007';

POST_ID
---------------------------------------------
POST_NAME
--------------------------------------------------------------------------------
007
アルバイト

更新における注意点

更新SQLを実行する場合には特に、PostgreSQLと Oracle のデータ型の振る舞いの違いについて注意が必要です。

POSTテーブルの POST_NAME列は VARCHAR(30) 型、PostgreSQL内の外部テーブルとしても varchar(30) 型です。そこに以下のように マルチバイト文字で20文字のデータ投入しますと、Oracle側でエラーが発生します。これは Oracleでは VARCHARの長さは文字数ではなくバイト数を基準としているからです。

db1=> INSERT INTO post VALUES ('999', '例外ケース/特別ケース/上記非該当ケース'
);
ERROR:  error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-12899: value too large for column "ORAUSER"."POST"."POST_NAME" (actual: 40, maximum: 30)

そのほか、浮動小数点データ型や日付時刻データ型における端数の丸め方など、データ型の振る舞いの違いにより、値が両データベース間を行き来することで期待と違う値が見えたり、格納されてしまったりすることがあります。

PostgreSQLとOracleで対応させることができるデータ型であっても、必ずしも同一の振る舞いをするデータ型ではないことを認識するようにしてください。

まとめ

後編では oracle_fdw の外部テーブルが実際にどのように動作するかを見てきました。

検索処理のpush-down、更新SQL、トランザクション処理などに対応していることが確認できました。
EXPLAIN を使って SQL処理のどの部分が Oracle側で実行されるのかを調べることで、応答データが期待に反していたり、性能が出ないといった問題に対処できました。
また、両データベース製品の関数やデータ型における仕様の違いに注意が必要です。