Oracle YesからPostgresへ

私たちの組織が長い間、緊密にOracleを使用しているのは偶然です。 私自身は、第6バージョンの時にOracleデータベースに出会いましたが、それ以来、不快感はありませんでした。 すべてが市場関係を台無しにしました。 最近、顧客が無料のDBMSを使用している場合、顧客が私たちのプロジェクトをより好意的に見ていることに気付き始めました。 これらのプロジェクトの1つを移植することは私の話になります...



無料のDBMSを選択することは、私にとって長く困難な問題でしたが、最終的には、よく知られた2つの選択肢になりました。 PostgreSQLはその豊富な（そしてまだ進化している）機能で魅力的でしたが、 MySQLはパフォーマンスと「ゼロ」管理に魅了されました。 Oracleが私たちを甘やかし、私たち全員がSQLを知っていて愛したので、多数の新しいNoSQLの亜種は準決勝でも落ちました。



PostgreSQLとMySQLの両方を急いで比較したり、完全に正当な判断を下したりせずに、PostgreSQLとMySQLの両方を生き生きと感じる機会を与えた1つのみすぼらしいゲームがなければ、どのDBMSに行き着くかを言うのは難しいです。 もちろん、機能に加えて、パフォーマンスも比較しました。 詳細は説明しませんが、「足場」の重要なポイントの1つは、データベースに大量のレコードを迅速かつ確実に挿入できること（ ACID 、はい）です。 この主題についてテストが実施されました。









縦軸は、データベースに毎秒保存される固定長のレコード数を示します。 「凡例」の数字は、トランザクションのサイズを意味します。 ここでは、MySQLが「現状のまま」測定され、PostgreSQLは通常のパーティションテーブルとマテリアライズドビューで動作する機能を提供する小さなストリップを使用していることに注意してください。 「信頼性の高い」ストレージに関するものであるため、グラフ上のMyISAMは、使用するハードウェアの望ましいパフォーマンスの「理論上の最大値」がどこにあるのかを完全に理解するためだけに表示されます。



テスト自体は長時間にわたって行われ、利用可能なハードウェア上のSSDの臭いさえしなかったため、グラフに示された絶対値は定説として扱われるべきではありません。 もちろん、データをさらに高速に保存できますが、（ほぼ）同じ条件で動作するさまざまなDBMSのパフォーマンス比に興味がありました。 PostgreSQLは、パーティショントリガーによって圧迫されていても、 InnoDBを使用してMySQLとほぼ同じ速度で動作し、大規模なトランザクション（1000レコード以上）でMyISAMに追いつき始めたことに驚きました。



簡単に推測できるように、上記のグラフはついにPostgreSQLに切り替えるべきだと確信しました。 オーバーライド列タイプ（数値と整数の数値、varcharとテキストのvarchar2など）を使用してテーブルを再作成するのは簡単でした。 XMLとXSLTはデータの移行を支援しました。





Oracle用に作成されたすべてのSQLコードの操作性を確保するために残っています。 リクエストのほとんどが機能し、一部はわずかな外観の変更後に機能しました。 まず、 デュアルテーブルを作成しました。

create table dual ( x varchar(1) not null ); insert into dual(x) values('x');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

それなしではできなかったわけではありませんが、クエリで頻繁に使用されていたため、書き換えるのは実用的ではありませんでした。 PostgreSQLを「満足」させるには、クエリにさらに厳密さを追加する必要がありました。







すべてのインラインビューには名前を付ける必要があり、列エイリアスの前に「 as 」キーワードを使用することを強くお勧めします。 ほとんどの列では省略できますが、「name」や「value」などの名前を使用すると、エラーが発生します。 次のステップは、プラットフォーム固有のコードを、OracleとPostgreSQLの両方でサポートされている適切な構造に置き換えることでした。 私たちはnvlとデコード 、および外部接続の時代遅れの構文について話しています。 最初の2つは、標準の（より柔軟な） 合体とcaseに簡単に置き換えられます。外部接続を使用する場合は、リクエストを書き換える必要があります。







ANSI Joinはバージョン9以降Oracle でサポートされており、私の意見では（+）を使用する従来のバージョンよりも便利です（簡潔ではありませんが） 。 1つのSQLクエリで異なる形式の結合を組み合わせようとしないでください。 外部結合を使用した場合、内部結合では、 from句にコンマを使用してテーブルをリストするのではなく、 内部結合を使用するのが非常に論理的です。



SQLコードの移行作業の大部分は、階層クエリの書き換えに関連していることが判明しました。 PostgreSQLのconnect byというフレーズは当然サポートされていません。 一方、次のタイプのリクエストが多数ありました。





CTEを使用してこのような要求を簡単に書き換えても、プラットフォームに依存することはできません。 Oracle（バージョン11.2以降） は再帰クエリをサポートしていますが、その構文はPostgreSQLで使用されているものとは異なります 。 特に、PostgreSQLでは、再帰キーワードの使用は必須ですが、Oracleは「理解しません」。 幸いなことに、ほとんどの場合、要求の階層部分はビューで「非表示」でした。







マージステートメントの書き換えは、より「不可解」であることが判明しました（残念ながら、階層クエリほど頻繁には使用されませんでした）。 PostgreSQLはこのステートメントをサポートしていませんが、 更新ステートメントでのfromおよびreturnフレーズの使用をサポートしています。後者は、 withフレーズで使用できるように、完全な結果セット（ selectステートメントと同様）を返します 。 ここに置いておきます。







この例では、PostgreSQLでのシーケンスの操作もOracleで採用されているものとは異なることに気付くでしょう。 もちろん、Oracleでは、PostgreSQLのシーケンスから値を受け取る関数と同様の関数を定義できましたが、Oracleコード（およびJavaコード）の書き換えを避けたいと思いました。 さらに、このアプローチは追加のオーバーヘッドに関連付けることができます。



多くの喜びが仕事を日付と時間とともにもたらしました 。 実際、Oracleで広く使用されている日付型は、その値を処理する際に多少のずるさを教えています。 このような値は数値であり、整数部は「魔法の」日付から経過した日数を決定し、小数部は最大1秒までの時間を決定すると想定できます。 （Oracleのほとんどの機能と同様に）ある程度慣れると、これは非常に便利ですが、PostgreSQLはデータ型に関してはるかに厳密です。

 date '2001-09-28' + interval '1 hour'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

したがって、一定の間隔を日付に追加できますが、変数値を追加する必要がある場合はどうでしょうか？ 求められている表現はまったく明らかではありません。

 date '2001-09-28' + (to_char(p_hours, '99') || ' hour')::interval
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

' hour 'の前の行にスペースが必要です！ また、PostgreSQLの厳密性は、数値から文字列への変換にも適用されることに注意してください（もちろんその逆も可能です）。 マスクは、9つだけで構成されている場合でも必要です。 Oracleでの作業に慣れている暗黙の変換は機能しません。



残りのリクエストはそれほど根本的な変更を受けていません。 OracleとPostgreSQLの対応する関数の外観が異なるため、文字列を操作するすべてのコードには改訂が必要です。 まだ残っていたrownum列は、ウィンドウrow_number（）に置き換える必要がありました。 表示される行数を制限するためにrownum条件が使用された場合、要求はフレーズlimitを使用して書き換えられました。



テーブル関数についても話すべきです。 OracleとPostgreSQLの両方にあります 。 もちろん実装は異なりますが、SQLクエリからアクセスする方法は似ています。 残念ながら、再帰CTEの場合のように、すべてが1つのキーワードの存在を台無しにします。







パッケージを扱うことは残っています 。 PostgreSQLにはそのような概念はありませんが、詳しく調べてみると、実際には必要ないことがわかります。 実際、Oracleのパッケージは何ですか？ グローバル変数と初期化コード（これは使用しません）を別にすれば、パッケージの主な利点は、依存関係のチェーンを壊すことです。 データベースオブジェクトを変更すると、依存パッケージの実装のみが無効になり、ヘッダーは無効になりません。 パッケージ内で再帰呼び出しを行う機能は、この事実の結果の1つです。



PostgreSQLでは、依存性メカニズムは実装されていません。 ストアド関数への再帰呼び出し（PostgreSQLにはプロシージャはありません）では、すべてが順序どおりです。 クライアントコードに最小限の変更を加える必要があるためには、パッケージの操作を継続する外観のみを提供するだけで十分です。 PostgreSQLスキーマはこれに最適です。 もちろん、このような「パッケージ」では、「プライベート」機能を実装することはできませんが、これはそれほど大きな問題ではありません。 コードは次のようになります。





スキームを「再作成」する前にすべてのオブジェクトを削除する必要は少し退屈ですが、生きることはできます。 テキストに「 strict 」という珍しい単語があることに気付くかもしれません。 ゼロまたは複数のレコードをフェッチしようとするときに、通常のOracleの動作を提供します。 他の記憶に残る瞬間から、最後のクエリによって変更された行の数を計算する奇妙な構造に言及できます。







もちろん、あまり多くはないため、すべてのパッケージの実装を書き換える必要がありました。 私の以前の物語から、書き直されたすべてのSQLコードが3つのカテゴリに分割されたことを理解できます。

1. 小規模な書き直しによって、プラットフォームに依存しないフォームにつながることを要求する
2. プラットフォーム固有のフラグメントがビューに隠されたクエリ
3. 確かにプラットフォーム固有のコード

最初の2つに問題はありません。 後者のカテゴリは、クライアントによって生成されたリクエストにプラットフォーム依存のコンストラクトが存在する場合、いくつかの問題を引き起こす可能性があります。 実際、Javaコードは書き直されたくないのです。 さまざまなDBMSで動作する2つのバージョンにソースを分割する必要性はさらに少なくなります。 残念ながら、プラットフォーム固有の設計をクライアントコードから完全に除外することはできませんでした。 ほとんどの場合、 tableキーワードはテーブル関数のクエリに干渉しました。 シーケンス呼び出しといくつかの階層クエリもありました。



プラットフォームに依存するすべてのクエリをデータベースに保存し、最初のアクセス時にそれらをソフトウェアキャッシュにロードすることにしました。 最初は、各データベースが独自のバージョンのクエリを保存すると想定されていましたが、使用されるすべてのDBMSにクエリを同時に保存する方が便利であることが判明しました。 Oracleでは、クエリテキストを保存するためにCLOBフィールドが使用され、PostgreSQLではtextが使用されていました。 均一性を確保するために、CLOBからvarchar2への変換が使用され、最大クエリサイズが4000文字に制限されました （ただし、1つのクエリはこのサイズを超えてクロールされますが、PostgreSQLバージョン向けであるため、「圧縮」する必要はありませんでした）。 to_char変換自体は、ビューを使用して非表示にする必要がありました。







要約すると、私はこの作品が最初に感じたほど怖くはなかったと言えます。 そのほとんどは階層クエリとOracleパッケージの書き換えに関連しており、ほとんどの問題はより厳密なSQL構文とPostgreSQLの通常の暗黙的な変換の欠如に関連していました。 Oracleでより厳密でプラットフォームに依存しないコードを最初に使用した場合、作業の範囲は狭くなる可能性があります。