OracleからSQLServerおよびSQLServerからOracleへの移行ガイド

公開: 2022-03-11

「ベンダー依存」は、多くの経営幹部にとって恐ろしい言葉です。 一方、完全な「ベンダーの独立性」を実現できないことは、業界ではすでに広く理解されています。 そして、それはデータベースの場合に特に当てはまります。

最も普及しているエンタープライズRDBMSプラットフォームの2つは、OracleDatabaseとMicrosoftSQL Serverです（簡潔にするために、この記事の残りの部分では、それぞれ「Oracle」と「SQLServer」と呼びます）。 確かに、IBM Db2は、縮小し続けるメインフレームプラットフォームでOracleと競合しますが、多くの分野で依然として重要です。 また、PostgreSQLなどの急速に進歩しているオープンソースの代替手段は、低から中レベルのコモディティハードウェアおよびWeb上の動的な環境で確固たる基盤を築いています。

しかし、 OracleとSQL Serverは、組織が新しいRDBMSを必要とするときに多くの経営幹部が直面する選択です。 最終的な選択は、ライセンスコスト、利用可能な社内の専門知識と過去の経験、既存の環境との互換性、パートナーとの関係、将来のビジネスプランなど、複数の要因に基づいています。変更すると、プラットフォームも変更する必要があります。 私はこれを知っています。なぜなら、私のキャリアの中で、このような移行を2回実装し、移行の実現可能性の評価を1回準備し、現在、クロスプラットフォーム機能の移行に取り組んでいるからです。

OracleとSQLServerはどちらも「旧式」であり、部分的にANSIに準拠したRDBMS実装です。 手続き型拡張（PL / SQLとTransact-SQLの構文は異なりますが、一般に変換が簡単です）と、新しいオブジェクト指向の未来を除いて、SQLコードは一見似ているように見えます。 そして、これは危険なハニートラップです。

OracleとSQLServerの間（どちらの方向でも）の移行プロジェクトで最も重要な2つのポイントは、トランザクションと、密接に関連する一時テーブルです。これらは、トランザクションスコープを解決するための重要なツールです。 また、ネストされたトランザクション（別のトランザクションのスコープ内に存在するトランザクション）についても説明します。これは、ネストされたトランザクションがOracleでのユーザーセキュリティ監査の実装の重要な部分であるためです。 ただし、SQL Serverでは、そのコンテキストでのCOMMITの動作のため、ユーザーセキュリティ監査には別のアプローチが必要です。

トランザクション構造の理解：1万フィートからのOracleとSQLServerの観察

Oracleトランザクションは暗黙的です。 つまり、トランザクションを開始する必要はありません。常にトランザクションを実行しているということです。 そして、このトランザクションは、コミットまたはロールバックステートメントを発行するまで開いています。 はい、トランザクションを明示的に開始し、ロールバックセーフポイントを定義し、内部/ネストされたトランザクションを設定できます。 ただし、重要なのは、「トランザクションに参加していない」ことはなく、常にコミットまたはロールバックを発行する必要があるということです。 また、データ定義言語（DDL）ステートメント（ CREATE 、 ALTERなど。トランザクションでは動的SQLを介して実行できます）を発行すると、発行されたトランザクションがコミットされることに注意してください。

Oracleとは異なり、SQLServerには明示的なトランザクションがあります。 つまり、明示的にトランザクションを開始しない限り、すべての変更は「自動的に」コミットされます。つまり、ステートメントが処理されるとすぐに、すべてのDMLステートメント（ INSERT 、 UPDATE 、 DELETE ）が独自にトランザクションを作成し、エラーが発生しない限りトランザクションをコミットします。アウト。

これは、データストレージの実装の違い、つまりデータがデータベースに書き込まれる方法とデータベースエンジンがデータを読み取る方法の違いの結果です。

Oracleでは、DMLステートメントはデータファイル内のレコードを直接変更します。 レコードの古いコピー（またはINSERTの場合は空のレコード置換）が現在のロールバックファイルに書き込まれ、変更の正確な時刻がレコードにマークされます。

SELECTステートメントが発行されると、発行前に変更されたデータに基づいて処理されます。 SELECTの発行後にレコードが変更された場合、Oracleはロールバックファイルの古いバージョンを使用します。

これは、Oracleが読み取り整合性と非ブロッキング読み取り/書き込みを実装した方法です。 また、非常にアクティブなトランザクションデータベースで長時間実行されるクエリで、悪名高いエラーORA-01555、 snapshot too old: rollback segment ... too small場合があるのもそのためです。 （これは、古いバージョンのレコードのクエリに必要なロールバックファイルがすでに再利用されていることを意味します。）これが、「Oracleトランザクションの長さ」という質問に対する正解の理由です。 「必要な限り、もはや」です。

SQL Serverの実装は異なります。データベースエンジンは、データファイルに対してのみ直接書き込みおよび読み取りを行います。 すべてのSQLステートメント（ SELECT / INSERT / UPDATE / DELETE ）は、複数のステートメントをグループ化して変更をロールバックできる明示的なトランザクションの一部でない限り、トランザクションです。

すべてのトランザクションは、必要なリソースをロックします。 Microsoft SQL Serverの現在のリリースは、必要なリソースのみをロックするように高度に最適化されていますが、必要なものはSQLコードによって定義されるため、クエリを最適化することが重要です）。 つまり、Oracleとは異なり、SQL Serverのトランザクションはできるだけ短くする必要があります。これが、自動コミットがデフォルトの動作である理由です。

また、OracleとSQL ServerのどのSQL構造が、トランザクション実装の違いの影響を受けますか？ 一時テーブル。

OracleおよびSQLServerの一時テーブル

ANSI SQL標準でローカルおよびグローバルの一時テーブルが定義されている場合、それらの実装方法は明示されていません。 OracleとSQLServerはどちらも、グローバル一時テーブルを実装しています。 SQL Serverは、ローカル一時テーブルも実装します。 Oracle 18cは、「真の」ローカル一時テーブル（「プライベート一時テーブル」と呼ばれます）も実装しました。これにより、SQLServerコードのOracle18cへの変換が、古いバージョンよりも明らかに簡単になります。 ID列の自動インクリメントなどの機能。

ただし、純粋な機能分析の観点からは、プライベート一時テーブルの導入は、SQL ServerからOracleへの移行の問題が実際よりも少ないように見えるため、さまざまなメリットがあります。 これは別のハニートラップです。それ自体がいくつかの新しい課題をもたらす可能性があるからです。 たとえば、設計時のコード検証はプライベート一時テーブルでは実行できないため、それらを使用するコードは常にエラーが発生しやすくなります。 動的SQLを使用したことがある場合は、次のように説明します。プライベート一時テーブルはデバッグも同様に複雑ですが、明確な独自のユースケースはありません。 したがって、Oracleがローカル（プライベート）一時テーブルを18cでのみ追加し、以前は追加しなかったのはなぜですか。

要するに、グローバルな一時テーブルを使用して同じかそれ以上を実装できないOracleのプライベート一時テーブルのユースケースは見当たりません。 したがって、深刻な変換を行う場合は、OracleとSQLServerのグローバル一時テーブルの違いを理解する必要があります。

OracleおよびSQLServerのグローバル一時テーブル

Oracleグローバル一時テーブルは、設計時にDDLステートメントによって明示的に作成された永続的なデータディクショナリオブジェクトです。 これはデータベースレベルのオブジェクトであり、必要な権限を持つ任意のデータベースセッションからアクセスできるため、「グローバル」です。 ただし、その構造がグローバルであるにもかかわらず、グローバル一時テーブル内のすべてのデータは、それが動作するセッションのみにスコープされ、どのような状況でも、このセッションの外部には表示されません。 つまり、他のセッションは、同じグローバル一時テーブルの独自のコピーに独自のデータを持つことができます。 そのため、Oracleでは、グローバル一時テーブルはセッションローカルデータを保持します。これは主に、コードの簡略化とパフォーマンスの最適化のためにPL/SQLで使用されます。

SQL Serverでは、グローバル一時テーブルは、Transact-SQLコードのブロックで作成された一時オブジェクトです。 作成セッションが開いている限り存在し、構造とデータの両方でデータベース内の他のセッションに表示されます。 つまり、これはセッション間でデータを共有するためのグローバルな一時オブジェクトです。

SQL Serverのローカル一時テーブルは、それを作成するセッションでのみアクセスできるという点でグローバルテーブルとは異なります。 また、SQL Serverでのローカル一時テーブルの使用は、グローバル一時テーブルの使用よりもはるかに広範囲に渡っています（そして、データベースのパフォーマンスにとってより重要です）。

では、ローカル一時テーブルはSQL Serverでどのように使用され、どのようにOracleに変換する必要がありますか？

SQL Serverでのローカル一時テーブルの重要な（そして正しい）使用法は、トランザクションリソースロックを短縮または削除することです。

  • レコードのセットを何らかの集計で処理する必要がある場合
  • データセットを分析および変更する必要がある場合
  • 同じスコープで同じデータセットを複数回使用する必要がある場合

このような場合、ソーステーブルからロックを削除するために、このレコードのセットをローカルの一時テーブルに選択する方がよい場合がよくあります。

SQL Serverの一般的なテーブル式（CTE、つまりWITH <alias> AS (SELECT...)ステートメント）は単なる「シンタックスシュガー」であることに注意してください。 これらは、SQLを実行する前にインラインサブクエリに変換されます。 Oracle CTE（ /*+ materialize */ヒント付き）はパフォーマンスが最適化されており、マテリアライズド・ビューの一時バージョンを作成します。 Oracleの実行パスでは、CTEはソースデータに1回だけアクセスします。 この違いに基づいて、SQL Serverは、Oracleクエリで実行できるように、同じCTEへの複数の参照ではなく、ローカル一時テーブルを使用した方がパフォーマンスが向上する可能性があります。

トランザクションの実装の違いにより、一時テーブルも異なる機能を果たします。 その結果、SQL Serverの一時テーブルを「現状のまま」Oracleに移動すると（Oracle 18cでプライベート一時テーブルを実装した場合でも）、パフォーマンスが低下するだけでなく、機能的にも問題が発生する可能性があります。

一方、OracleからSQL Serverに移行する場合は、トランザクションの長さ、グローバル一時テーブルの可視範囲、および「マテリアライズド」ヒントを使用したCTEブロックのパフォーマンスに注意を払う必要があります。

どちらの場合も、移行されたコードに一時テーブルが含まれるとすぐに、コードの変換ではなく、システムの再実装について話し合う必要があります。

テーブル変数を入力してください

開発者はおそらく疑問に思うでしょう：テーブル変数はどうですか？ OracleからSQLServerへの移行手順で、変更を加える必要がありますか、それともテーブル変数を「現状のまま」移動できますか？ まあ、これはコードでそれらが使用される理由と方法によって異なります。

一時テーブルとテーブル変数の両方をどのように使用できるかを見てみましょう。 まず、MicrosoftSQLServerから始めます。

Transact-SQLでのテーブル変数の実装は、一時テーブルとある程度一致しますが、独自の機能をいくつか追加します。 主な違いは、テーブル変数をパラメーターとして関数とストアドプロシージャに渡す機能です。

これは理論ですが、実際の使用上の考慮事項はもう少し複雑です。

深く根付いたOracleのバックグラウンドから来たときに、最初に本格的なTransact-SQL最適化を担当しました。これは、テーブル変数がメモリ内にあり、一時テーブルがディスク上にあるということです。 しかし、2014年までのMicrosoft SQL Serverのバージョンでは、テーブル変数がメモリに保存されていなかったことがわかりました。 したがって、一時変数の全表スキャンは、実際にはディスクの全表スキャンです。 ありがたいことに、SQL Server 2017以降のバージョンでは、一時テーブルとテーブル変数の両方に対して宣言型メモリの最適化がサポートされています。

では、一時テーブルを使用してすべてを同様に、またはより適切に実行できる場合、Transact-SQLのテーブル変数のユースケースは何でしょうか。 テーブル変数のキープロパティは、それが変数であるため、トランザクションロールバックの影響を受けず、パラメーターとして渡すことができます。

Transact-SQL関数には非常に制限があります。関数のタスクは単一の戻り値を返すことであるため、設計上、副作用を引き起こすことはありません。 Transact-SQLは、 SELECTでさえ副作用と見なします。これは、SQL Serverでは、テーブルにアクセスすると、暗黙的なトランザクションと関連するトランザクションロックが作成されるためです。 つまり、関数内では、既存の一時テーブルのデータにアクセスしたり、一時テーブルを作成したりすることはできません。 その結果、レコードのセットを関数に渡す必要がある場合は、テーブル変数を使用する必要があります。

（グローバル）一時テーブルとコレクション変数（Transact-SQLテーブル変数に相当するOracle PL / SQL）の使用に関するOracleの考慮事項は異なります。 Oracleコレクション変数はメモリ内にありますが、一時テーブルは一時テーブルスペースにあります。 Oracle関数は、永続的または一時的なテーブルへの読み取り専用アクセスを許可します。 Oracleの単純なSELECTは、リソースをロックすることはありません。

Oracleでは、コレクション変数と一時テーブルのどちらを使用するかの選択は、予想されるデータ量、このデータを保持する必要がある期間、およびメモリとディスクの割り当てと可用性に基づいています。 また、コレクション変数は、行セットを出力としてホストプログラムに戻すための標準的な方法です。

ほとんどのSQL構文要素はSQLServerとOracleの間で非常に似ているため、SQLServerTransact-SQLからOraclePL/ SQLへのテーブル変数を含むコードブロックの変換は、より単純で構文的に寛容なプロセスです。 基本的な検証テストに合格する可能性がありますが、上記のように一時的なテーブルの再実装手順を実行しない限り、機能的には正しくありません。 一方、OracleからSQL Serverに移動されたコードには、構文的に有効であるためだけに、より多くの変更手順が含まれます。 また、機能的に正しくするには、一時テーブルとCTEを使用する詳細なケースに対処する必要があります。

内部トランザクション（「ネストされたトランザクション」）

OracleからSQLServerへの移行の課題に関して、次に注目すべき主要な領域はネストされたトランザクションです。

一時テーブルの場合と同様に、Transact-SQLコードにネストされているかどうかに関係なくトランザクションが含まれている場合、またはOracleコードにネストされたトランザクションが含まれている場合は、単純なコードの移行だけでなく、機能の再実装についても説明します。

まず、Oracleのネストされたトランザクションがどのように動作し、それらをどのように使用する傾向があるかを見てみましょう。

Oracleのネストされたトランザクション

Oracleのネストされたトランザクションは完全にアトミックであり、外部スコープから独立しています。 プレーンな対話型OracleSQLクエリでは、ネストされたトランザクションを実際に使用することはできません。 対話モードでOracleを使用している場合、状態になったことを確認したら、手動で変更をコミットするだけです。 最後のステップ（たとえば、不確かなステップ）を実行するまでまだコミットできない変更を行った場合、ロールバックする必要があるかもしれませんが、すでに実行した作業を保持したい場合は、トランザクション全体をコミットまたはロールバックせずに、セーフポイントにロールバックするためのセーフポイントを作成します。

では、ネストされたトランザクションはどこで使用されますか？ PL/SQLコード。 より具体的には、自律プロシージャPRAGMA AUTONOMOUS_TRANSACTIONで宣言されたプロシージャ。 これは、このコードが（名前付きストアドプロシージャとして、または匿名で）呼び出されると、このコードを呼び出したトランザクションとは関係なく、トランザクションがコミットまたはロールバックされることを意味します。

ネストされたトランザクションを使用する目的は、呼び出し元のコードに何が起こるかに関係なく、自己完結型の作業単位をコミットまたはロールバックすることです。 内部トランザクションをコミットまたはロールバックできる場合、たとえば部屋予約システムの実装などで、共有リソースの可用性（または予約）を確認するために使用されます。 コミットのみの内部トランザクションの主な用途は、アクティビティの監視、コードトレース、および安全なアクセスの監査です（つまり、ユーザーは変更を行うことはできませんでしたが、変更を試みました）。

SQLServerTransact-SQLコードのネストされたトランザクションは完全に異なります。

SQLServerのネストされたトランザクション

Transact-SQLでは、内部トランザクションが完全にコミットされるかどうかは、最も外部のトランザクションに依存します。 内部トランザクションがロールバックされている場合は、ロールバックされているだけです。 ただし、内部トランザクションがコミットされている場合、外部スコープトランザクションのいずれかのレベルがロールバックされた場合にロールバックされる可能性があるため、まだ完全にはコミットされていません。

では、外部トランザクションをロールバックすることでコミットを元に戻すことができる場合、内部トランザクションの使用はどうなりますか？ 答えは、ローカル一時テーブルのユースケースと同じです。つまり、リソースのロックを解除します。 違いは、これがグローバルロックリリースではなく、直接の外部（直接の「親」）トランザクションのスコープ内のロックであるということです。 複雑なTransact-SQLコードで使用され、外部トランザクションの内部リソースを解放します。 これは、パフォーマンスの最適化およびリソース管理ツールです。

OracleとSQLServerの内部/ネストされたトランザクションは異なる（おそらく反対の）動作と完全に異なるユースケースを持っているため、あるプラットフォームから別のプラットフォームへの移行には、再書き込みだけでなく、ネストされたトランザクションブロックを含むスコープを完全に再構築する必要があります。

その他の要因

これらの一時テーブルおよびトランザクション中心の考慮事項は、OracleからSQL Serverへの移行で対処する必要がある唯一のことですか？ それらは最も重要かもしれませんが、確かに他にもあり、それぞれがカバーする価値のある独自の癖を持っています。 以下は、私が最も誤解されているトピックであるとわかった残りの部分です。

  1. SQLServerのID列
  2. Oracleのシーケンス
  3. Oracleの同義語
  4. フィルタリングされたインデックス
  5. 読み取りの一貫性（OracleからSQL Serverのみ）
  6. 移行ツールの使用

このシリーズの次のパートでは、これら、特に最初の3つについて説明します。

一時テーブル、テーブル/コレクション変数、およびネストされたトランザクション：移行の上位3つの問題点

一時テーブル、テーブル変数/コレクション、およびネストされたトランザクションから始めました。これらは、変換プロジェクトで最も一般的で明白な障害点であるためです。 OracleDatabaseまたはMicrosoftSQLServerの重要なシステムは、間違いなくそれらの一部を使用します。これらの要素の使用は、それぞれのRDBMS実装によるトランザクションサポートの特定の設計と非常に緊密に結合されています。

パート2を読んでください！

マイクロソフトゴールドパートナーバッジ。 （Toptalはマイクロソフトのゴールドパートナーです。）