脱oracle
ここ数年、既存のシステムでOracleを使っていたのを、システム更改と共に他のDBに乗り換える開発の仕事が何件かありましたので、それに関するネタを自分の防備録も兼ねて書きます。
何故、脱Oracle？
たぶん、それは
- Oracle税と呼ばれる、年々上昇するライセンス料
- 客の顔色を見て決められるライセンス料
- 強制的にバージョンアップさせられる、そのスピードに追いつかない
- 囲い込み戦略
等など、Oracleの傲慢とも言える顧客に対する態度によるものでしょう
羊は囲い込もうとすればするほど、外へ逃げようとするのです。
移行先は何処へ
他の商用（有料）DBへの移行の例もありますが、今まで自分が経験した、脱Oracleの脱獄先はPostgreSQLが殆です。この記事は脱Oracle→PostgreSQL化について書きます。
オープンソースのDBとしては以下がツートップでシェアを二分していますが
- PostgreSQL
- MySQL（MariaDB）（MySQLは今はOracleだが・・・）
一般的には既存がOracleならPostgreSQLの方が相性がいいと言われています。
脱oracle、PostgreSQL化で考慮しなければいけないこと
SQLの互換性
複数DBシステムを扱ったことのある人なら言うまでもないですが、基本SQLはそのDBベンダに依存した方言が多く、DBを乗り換えるイコール、そのSQLを移行先の仕様に合わせて書き換える必要があります。
全般的に、Oracleはさすが有償で高価なライセンス料をとるだけあって懐が広い面があります
多少の型の違い、いい加減なおかしなSQLでも、それなりに動いてしまいます。
しかし、PostgreSQL、MySQL（MariaDB）を始めとするオープンソース系のDBはそのような懐の広さはなく厳密にチェックされSQLのシンタックスエラーになります。ツンデレ系です。
その懐の広さもOracleの囲い込み戦略のひとつなのかもしれませんが。(^_^;)
型が合わなくてもそれなりに動く
よくあるケースがWHERE句の = の右辺と左辺の型が文字列型を数値型で違う場合です。
・・・
WHERE VARCHAR_COL = 1
-- 本当は WHERE VARCHAR_COL = '1'
Oracleは適当に型を合わせてくれて動きますが、PostgreSQLでは厳格にシンタックスエラーになります。
CASE WHEN文も同じでRETURNする方が違ってもエラーにならず適当によしなに型を合わせてくれます。（最終的にどうやってRETURNする型を決めているのか？謎）
こういった曖昧、いい加減なSQLがPostgreSQL、他のDBへ移行する際に発覚することが多々、あります。(^_^;
VARCHAR2の文字数（桁数）の仕様の違い
VARCHAR2の長さはセマンティクスをByte数にするか、文字数にするか選べますが、デフォルトはByte数で何故か未だにByte数を使っているシステムが多いです。
対して、PostgreSQL、他オープンソース系DBは文字数です。半角1文字も、全角1文字も1です。
Oracleの場合カラムによって、エンコードによって全角文字が入るカラムはByteセマンティクスの場合、SJISは2倍、UTF-8は3倍しないといけません。
ま、長さをそのままPostgreSQLに移行しても余分に長いだけで特にエラーになることはありません。
nullと空文字('')の扱いが違う
これは有名。Oracleはnullと空文字('')を区別しませんが、PostgreSQL、他オープンソース系DBは区別します。空文字('')はIS NULLでヒットしません。
Hint句が使えない（使えないことはないが）
OracleはHint句でクエリの最適化を制御できますが、PostgreSQLは標準ではHint句は使えません。
拡張機能「pg_hint_plan」を入れることによってHint句は使えるようになりますが、Oracleに比べるとかなり貧弱です。（指定できるものが少ない）
悪魔の+結合
OracleのSQLをPostgreSQLに書き換えようとした時、この+結合が出てくると軽い目眩がします。この+結合が使えるのは商用、オープン系合わせてもOracleだけで、この書き方は何の移植性もないので使わないほうが無難です。
テーブルロックの仕様が違う
Oracleは複数テーブルをJOINしていてUPDATE FOR ・・・でロックする場合、基本はメインのテーブルだけロックされます。（状況、JOINの仕方によりほかのテーブルもロックされる）
PostgreSQLはJOINしている場合、全てのテーブルがロックされます。メインのテーブルだけロックしたい場合は明示的にテーブルを指定しないといけません。
FOR UPDATE OF table_name [, ...]
OracleはロックのモードにはWAIT（秒数）、NOWAITが指定できますが、PostgreSQLはデフォルトがWAITでSQLではWAITは指定できません。SQLではNOWITしか指定できません。WAITの秒数もPostgreSQLのシステムパラメータで指定するのでSQL個別に指定はできません。
TemporaryTableの仕様が違う
両者は生存期間と削除の対象が違います
- PostgreSQL
- 一時テーブルは、作成したセッションまたはトランザクションの終了時に削除されます。このため、別のセッションからは参照できません。
- Oracle
- 一時テーブルは、作成したセッションまたはトランザクションの終了時にデータのみが削除され、テーブル自体は残ります。そのため、他のセッションから参照できます。
OracleのTemporaryTableは事前にDDLで作成しておくことができますが、PostgreSQLの場合は各セッションで必要なときに都度CREATEすることになります。
to be continue （あとは、思いついたら書く）
ストアドプロシージャ（PL/SQL）の違い
なんと言ってもこれが最も影響が大きいでしょう。
一昔前、何故かOracleのストアドプロシージャであるPL/SQLが多流行りしていた時期がありました。
PL/SQL専門の案件もありました。
しかし、いまとなってはストアドプロシージャは負の遺産以外の何者でもありません。事実、過去に作った大量のPL/SQLのせいで脱Oracleができないシステムも世の中にはたくさんあります。
私も多方面で主張していますが、ストアドプロシージャは以下の理由で今の時代は推奨しません。
- DBベンダ固有の言語、なんの互換性、移植性もない
- 今時の関数型言語でも、オブジェクト指向でもないレガシーな言語仕様
- 今どきの統合開発環境の支援を受けられない。変数、クラス、関数の参照の検索とか、コード補完、コードチェック、CTRL-右クリックで実装に飛ぶ、型チェック等の恩恵を受けられない
- デバックが困難（できないことはない）
- テストフレームワークがない、カバレッジがわからない
- 各、言語、フレームワーク、ORマッパ等からのサポートが弱い（明らかに力入ってない）
- 特に汎用的に複数のDBをサポートしているORマッパーの場合、ストアドの呼び出し部分も汎用的であり、そのDBベンダのストアド固有の機能、引数型を使っていると呼び出せない場合がある
- 各言語からList型、Array型の引数で呼び出せない。そこで相互変換が発生してしまう
- 各言語から分断されてしまうので、各言語側のビジネスロジックと、ストアド側のビジネスロジックで分断されてしまう。流れが繋がらない。流れを読みづらい
- DB上のオブジェクトとして隠蔽されてしまうので、その存在に気が付かない（あることさえ、忘れてしまう）
- 結局、作りぱなしで誰もテストしない
- 等など・・・
品質、生産性に関して言えばなにひとついいことはありません。
一方、ストアドプロシージャはDB上のオブジェクトなので全てDBサーバ上で完結し、実行速度が早いというメリットもあります。言語を使って書くと言語とDBサーバの間をSQLで何回も行き来しますが、そのオーバーヘッドがありません。
なので私としてもストアドプロシージャを完全否定はしませんが、そこにビジネスロジックを持ち込むのはアンチパターンだと思っています。
上記のように性能面では優位性があるので、ビジネスロジックではない、大量データ移行、日々の大量データメンテナンスでは有効であると思います。（ホント、お願いだからそこだけにして）
過去に作ってしまったものはしょうがない（涙で枕を濡らすしかない）ですが、今後は極力負の遺産を作らないようにしないといけません。
OracleのPL/SQLとPostgreSQLのPL/PgSQL
PostgreSQLにも一応、ストアドプロシージャPL/PgSQLはありますが、名前は「似て非なるもの」、全く違うものです。基本、脱Oracle、PostgreSQL化においてストアドプロシージャーは「イチから書き直してください」が定説です。
- PostgreSQLはfunctionとprocedureの区別がない。全てfunctionの扱い
- PostgreSQLはパッケージの概念がない
- PostgreSQLはprocedureの仕様部（procedureの名前と引数の定義）とBodyの区別がない
- PostgreSQLのストアドは戻り値を返すOUT引数が使えない
- 当然ながらSQL、テーブル、カラムの型の仕様が違う
世の中には、OracleのPL/SQLからPostgreSQLのPL/PgSQLへのコンバータなるものがいくつか存在しますが、その信頼性は以下の理由でイマイチです。
- コンバータでコンバートできても動作までは保証されない
- Oracleの場合はストアドのDB登録時にある程度チェックされるが、PostgreSQLはそれが緩い。DBオブジェクトとして正常に登録できも実行したらエラーになるケースが多々ある
- 結局、全部イチから試験しなおさないといけない ＿|￣|○ （結局、イチから作ったほうが早いんじゃね？説）
過去に、OracleのPL/SQLでビジネスロジックをゴリゴリに書いてあって、その本数、数百本というシステムがいくつかありましたが、こういったシステムで脱Oracleは地獄を見ます。