こんにちは！ さて、今回はDB間の非互換の対処法に関して学んだことを共有していきたいと思います！ なかなかまとまった資料がないので、私たちの記事が皆さんの参考になればうれしいです！
現在携わっている案件で、OracleからPostgreSQLにDDLを移行するお仕事がありました。 OracleやMySQLデータベースを PostgreSQLに移行するための無料ツールです。 しかし、Ora2pgでは埋めることができなかった非互換や、Ora2pgによる誤変換がいくつかあったので、それらの対処方法を以下にまとめました。
メモリー上で順序番号を事前に割り当てないように設定したいとき、OracleではNO CACHE、PostgreSQLではCACHE句を削除する、もしくはCASHE 1と設定します。 Oracleで使用しているORDER句は、PostgreSQLには存在しません。 Oracleで利用しているPCTFREE句はPostgreSQLには存在しません。Ora2pgの自動変換でも変換時に削除されます。
ただし、PCTFREE句はFILLFACTORで代用可能です。FILLFACTORは空き領域割合ではなく、挿入で使用可能な領域割合を指定するため、下記の式のように設定します。 Oracleで使用できるNUMBER型はPostgreSQLには存在しないため、Ora2pgを使用すると、自動的に以下のように変換されます。 Ora2pgによる整数の変換に関しては問題ありませんが、小数の変換には注意が必要です。double precision及びrealは内部的には二進数で扱われるため、
四則演算等を行った際に計算結果が異なる場合があります。 OracleのDATE型（7バイト、固定長）は日付と時刻が格納されます。 OracleのDATE型は、PostgreSQLのTIMESTAMP型と同じく、システム的には時間(hh:mm:ss)まで保持しています。 OracleのON COMMIT句はPostgreSQLにおいても存在します。ON COMMIT句はOracleとPostgreSQLで互換性がありますが、Ora2pgの自動変換により削除されてしまうため、手動で追記する必要があります。
表記の仕方はOracleと同じです。 OracleのCYCLE句はPostgreSQLにおいても存在します。CYCLE句はOracleとPostgreSQLで互換性がありますが、Ora2pgの自動変換により削除されてしまうため、手動で追記する必要があります。
表記の仕方はOracleと同じです。 REDOログへの記録を許可/禁止するOracleのLOGGING/NO LOGGING句はPostgreSQLでは存在しないため削除します（Ora2pgの自動変換においても削除されます）。
PostgreSQLではREDOログではなく、WALというトランザクション・ログ（データベースに対する変更履歴）を記録しておく機能があります。 OracleのUSING INDEX内でENABLEを指定している場合、制約が有効化されており、これはOracleではデフォルトとなります。
PostgreSQLではALTER TABLE～で制約・インデックスが作成されることで、Oracleと同じ動作になります。
Oracleは、表にUNIQUEキー整合性制約またはPRIMARY KEY整合性制約を規定するために、一意キーまたは主キーの一意索引を作成します。
この索引は、制約を使用可能にしたときに、データベースによって自動的に作成され、必要に応じて、USING INDEX句を指定して索引作成を制御できます。 DISABLE句はNOVALIDATEがデフォルトであり、OracleでDISABLE句だけを指定してしている場合、インデックスが削除され制約も無効になります。 今回はDDL側から非互換を発見し対処をしましたが、DDL以外の観点で考えるともっと非互換対処が必要になってきます。
OracleからオープンソースのPostgreSQLに移行するのはなかなか大変ですね。。リリースの際に何も起こらないことを願うのみです。
はじめに
株式会社BFT名古屋支店・新卒の「なる」と「もな」です！
ついに2023年になってしまいましたね。もうそろそろ新卒と言えなくなってしまします、、泣
社会人、時間の流れが早すぎるを実感している今日この頃です。。背景
Oracleと全く同じ構文のDDLをPostgreSQLに移行できればよいのですが、残念ながらそうではありません。。
まず、OracleとPostgreSQLのDDLには互換性があるものとないものがあります。
今回はOra2pgを使用して、このようなDDLの非互換を埋めようとしました。
OracleやMySQLからDDL等のデータをエクスポートし、PostgreSQLにデータ等をインポートすることでデータベースを移行することができます。
詳しくはこちらを参照してください。
https://ora2pg.darold.net/（公式サイト）
使用した環境は以下の通りです。
使用環境
Oracleサーバ
PostgreSQLサーバ
非互換一覧（DDLの観点から）
NO CACHE(シーケンス)
Ora2pgで自動変換を行った場合はNO CACHE句を削除することで対応しますが、シーケンスの歯抜けを防ぐためにCASHE 1に手動修正することを推奨します。
（以下参考）
https://www.postgresql.org/docs/14/sql-createsequence.htmlORDER
OracleではNO CASHに設定する、もしくは（cacheの値が2以上であっても）ORDERを指定することで要求順通りに採番可能ですが、PostgreSQLでは上記の通りORDER句にあたる句や機能が存在しないため、OracleのNO CACHEと同義であるCACHE 1に設定することで順序通りの採番を可能にします。PCTFREE
FILLFACTOR = 100 - PCTFREE
(例)PCTFREE=10の場合、FILLFACTOR=100-10=90となるので、WITH(FILLFACTOR=90)と追記します。NUMBER
より正確な値を必要とする小数を扱う際は、double precision及びrealではなくnumericを使用することを推奨します。
※numericを使用する場合は精度とスケールを指定してください。指定しない場合、計算速度が劣化する可能性があります。DATE
PostgreSQLにもDATE型は存在しますが、PostgreSQLのDATE型は日付のみ（時刻なし）格納されるため、
日付と時刻の両方が格納されるTIMESTAMP型（8バイト）を用いて修正を行います。CURRENT_DATE
OracleのCURRENT_DATEでは時間まで格納しますが、PostgreSQLのCURRENT_DATEでは日付までしか格納しません。
そのため、CURRENT_DATEをCURRENT_TIMESTAMPに手動修正します。ただしCURRENT_TIMESTAMP関数は小数点以下6桁まで扱うため、Oracleと表記を合わせるためにはCURRENT_TIMESTAMPの末尾に精度を記載する必要があります。
（例）CURRENT_TIMESTAMP(0)
→2023/2/9 12:34:56
CURRENT_TIMESTAMP(4)
→2023/2/9 12:34:56.7890
ON COMMIT
CYCLE
LOGGING/NO LOGGING
更新ログをとらないという点に関しては、PostgreSQLではUNLOGGEDテーブルを作成するという手もあります。
しかし、OracleのNO LOGGING句は最小限の更新ログ（UPDATE・DELETE・通常のINSERT）がREDOに記録されるのに対し、UNLOGGEDテーブルは一切の更新ログをWALに書き出さないため、完全な代替手段とは言えません。USING INDEX
PostgreSQLでは、テーブルに一意性制約またはPRIMARY KEYが定義されると、自動的に一意インデックスを作成します。このインデックスが、PRIMARY KEYや一意性制約（適切ならば複数列のインデックスで）となる列に対して作成され、この制約を強制する機構となります。
※手作業で一意列に対しインデックスを作成する必要はありません。 これは、単に自動作成されるインデックスを二重にするだけです。
（参考資料）
www.postgresql.jpDISABLE
DISABLE句とVALIDATE句を両方指定することで「インデックスが削除」and「制約が有効」となります。
OracleでDISABLE句だけを指定してしている場合にOra2pgでDDLを変換すると、「ALTER TABLE xxxx ADD PRIMARY KEY」等の制約の文が別で作成されたままなので、変更後のDDLから制約を作成する文を削除します。
※VALIDATE、NOVALIDATEの詳細については以下を参照してください
データ整合性おわりに
ちなみに、PostgreSQL上にOracleと互換性のある関数やデータ型を提供するorafceという拡張モジュールもありますので、そちらもぜひ利用してみください。
また、次回もOracleからPostgreSQLの移行の際に学んだことを伝えていけたらなと思います！
引き続き株式会社BFT名古屋支店・新卒の「なる」と「もな」をよろしくお願いいたします～！
【Oracle,PostgreSQL,Ora2pg】Oracle-PostgreSQL非互換対処方法一覧
＜互換性のないものの例＞
etc...
Ora2pgとは
Ora2pgサーバ
PostgreSQLではCACHE 1と設定する
PostgreSQLではCACHE 1と設定する
PostgreSQLではWITH(FILLFACTOR= )と設定する
小数を扱う場合、PostgreSQLではnumericを使用する
PostgreSQLではTAIMESTAMP型に修正
PostgreSQLではCURRENT_TIMESTAMPに修正
PostgreSQLにおいてもON COMMIT句は存在するため、追記する
PostgreSQLにおいてもCYCLE句は存在するため、追記する
PostgreSQLにはLOGGING/NO LOGGING句に当たる句は存在しないため削除する
PostgreSQLでは自動的に一意インデックスを作成するので、USING INDEX句は削除する
PostgreSQLでは制約を無効にする
