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側で実行されるのかを調べることで、応答データが期待に反していたり、性能が出ないといった問題に対処できました。
また、両データベース製品の関数やデータ型における仕様の違いに注意が必要です。