OracleからSQLServerおよびSQLServerからOracleへの移行ガイド-Pt。 2

公開: 2022-03-11

このシリーズの最初のパートでは、トランザクションの実装におけるOracleDatabaseとMicrosoftSQL Serverの違いについて説明し、OracleからSQLServerへの移行中およびその逆の場合に発生する可能性のある落とし穴に焦点を当てました。この次回の記事では、OracleとSQL Serverの分割全体で一致しない、またはまったく異なる意味や使用法を持つ、一般的に使用されるSQL構文要素の数について説明します。

OracleのシーケンスとSQLServerのID列

データベースコミュニティには、自然キーの愛国者と人工（または「代理」）キーの支持者という2つの陣営の間で長年の隔たりがあります。

私自身は自然キーを守りますが、何らかの理由でサロゲートを作成することがよくあります。しかし、この議論の内容はさておき、人工キーを生成するための標準的なメカニズムであるOracleシーケンスとSQLServerID列を見てみましょう。

Oracleシーケンスは、ファーストクラスのデータベースレベルのオブジェクトです。対照的に、SQL Server ID列は列タイプであり、オブジェクトではありません。

Oracleシーケンスを使用してテーブルキー（通常は主キー）を生成すると、増分が保証されるため、一意になります。ただし、連続しているとは限りません。実際、適切に設計された実装であっても、いくつかのギャップがある可能性があります。したがって、Oracleの実装では、シーケンスによって生成された値が連続していることに依存してはなりません。

また、シーケンスはOracleデータベースのデータディクショナリを介して管理されるため、すべての代理キーをサポートする専用のシーケンスを作成するには、リソースを消費しすぎ（面倒）になります。単一のシーケンスオブジェクトは、複数またはすべての代理キーをサポートできます。

一方、複数のプロセスがシーケンスからNEXTVAL （次の増分値）にアクセスする必要がある場合、シーケンスは重要なシングルアクセスリソースになります。これにより、アクセスするすべてのプロセスが厳密にシーケンシャルになり、マルチスレッド（シングルまたはマルチサーバー）実装がシングルスレッドプロセスに変わり、待機時間が長くなり、メモリ使用量が多くなり、CPU使用率が低くなります。

そのような実装は実際に起こります。この問題の解決策は、問題のシーケンスオブジェクトを適切なキャッシュ値で定義することです。つまり、定義された範囲の値（10万または10万）が呼び出しプロセスのキャッシュに選択され、使用時にデータディクショナリに記録されます。、およびNEXTVALが呼び出されるたびにデータディクショナリにアクセスする必要なしに、この特定のプロセスで使用できるようになります。

ただし、キャッシュされたすべての値が使用される可能性が低いため、ギャップが作成されるのはまさにこのためです。また、並列セッションの複数のプロセス間で、記録されたシーケンス値の一部を時系列で反転できることも意味します。この反転は、シーケンス値がリセットまたはバックシフトされない限り、単一のプロセス内で発生することはありません。しかし、この最後のシナリオは問題を探すことになります。それは不要であるはずであり、正しく実装されていない場合、重複した値が生成される可能性があります。

したがって、Oracleシーケンスを使用する唯一の正しい方法は、代理キーを生成することです。つまり、一意であるが、他の信頼できる使用可能な情報を保持しているとは想定されていないキーです。

SQLServerのID列

SQL Serverはどうですか？オラクルの対応するものと非常に類似した機能と実装を備えたシーケンスがSQLServer2012に導入されましたが、それらは一流の頼りになる手法ではありません。他の機能追加と同様に、Oracleからの変換には意味がありますが、SQL Serverにサロゲートキーを最初から実装する場合は、 IDENTITYの方がはるかに優れたオプションです。

IDENTITYは、テーブルの「子」オブジェクトです。テーブル外のリソースにはアクセスせず、意図的に操作されない限り、シーケンシャルであることが保証されます。また、Oracleとのセマンティック互換性ではなく、まさにこのタスクのために特別に設計されています。

Oracleがバージョン12.1でIDENTITY機能を実装しているので、以前はIDENTITY機能がなかったのか、なぜ今実装したのか、SQL Serverが最初から（Sybase SQL Serverの起源から）IDENTITY機能を必要としていたのか疑問に思うのは当然です。

その理由は、Oracleには常にIDキー機能があったためですROWIDまたはUROWIDのデータ型を持つROWID疑似列です。この値は数値ではなく（ ROWIDとUROWIDは独自のOracleデータ型です）、データレコードを一意に識別します。

SQL ServerのIDENTITYとは異なり、OracleのROWIDは簡単に操作できず（クエリはできますが、挿入または変更はできません）、すべてのOracleテーブルのすべての行のバックグラウンドで作成されます。また、Oracleデータベースのデータ行にアクセスする最も効率的な方法はROWIDを使用することであるため、パフォーマンス最適化手法として使用されます。最後に、行データの低レベルのストレージに効果的にインデックスを付けるため、デフォルトのクエリ出力の並べ替え順序を定義します。

OracleのROWIDが非常に重要である場合、SQL Serverはそれらの年月をどのように生き残り、それなしでリリースされたのでしょうか。 IDENTITY列を主（代理）キーとして使用する。

OracleとSQLServerのインデックス構造の実装の違いに注意することが重要です。

SQL Serverでは、最初のインデックス（多くの場合、主キー）がクラスター化されます。これは、最も一般的には、プライマリデータファイルのデータがこのキーで並べ替えられることを意味します。 Oracle側では、クラスター化インデックスに相当するものは、インデックスで編成されたテーブルです。これはOracleのオプションの構成であり、必要な場合にのみ散発的に使用されます。たとえば、読み取り専用のルックアップテーブルなどです。

ROWIDの使用に基づくOracleのすべてのデザインパターン（データ重複排除など）は、SQLServerに移行するときにIDENTITY列に基づいて実装する必要があります。

SQL ServerでのIDENTITYの使用からOracleでのIDENTITYの使用に移行すると、機能的に正しいコードが生成される可能性がありますが、Oracle側では、 ROWIDのパフォーマンスがはるかに効率的であるため、最適ではありません。

単純なSQL構文変換を実行してOracleシーケンスをSQLServerに移動する場合も同じです。コードは実行されますが、コードの単純さとパフォーマンスの両方の観点から、 IDENTITYを使用することをお勧めします。

MicrosoftSQLServerのフィルター処理されたインデックス

数年前、Microsoft SQL Server 2008は、真に一流のエンタープライズデータベースに変える多くの重要な機能を導入しました。私の一日を何度も救ったものは、フィルタリングされたインデックスです。

フィルター処理されたインデックスは、 WHERE句を持つ非クラスター化インデックス（つまり、独自のデータファイルとして存在するインデックス）です。これは、インデックスファイルに句に関連するデータレコードのみが含まれていることを意味します。フィルタされたインデックスを最大限に活用するには、データセットを返すときに必要なすべての列を一覧表示するINCLUDE句も必要です。必要なすべてのデータポイントを含む特定のフィルター処理されたインデックスを使用するようにクエリが最適化されている場合、データベースエンジンは、プライマリテーブルのデータファイルを見なくても、（小さな）インデックスファイルにアクセスするだけで済みます。

これは、数年前にテラバイトサイズのテーブルで作業していたときに特に価値がありました。問題のクライアントは、常にアクティブなレコードのほんの一部にしかアクセスする必要がないことがよくありました。このアクセスの最初の実装（エンドユーザーのUIアクションによってトリガーされる）は、単に痛々しいほど遅いだけでなく、まったく使用できませんでした。必要なINCLUDEを使用してフィルター処理されたインデックスを追加すると、ミリ秒未満の検索になりました。この最適化タスクに費やした時間はわずか1時間でした。

確かに、フィルタリングされたインデックスにはいくつかの制限があります。 LOB列を含めることはできず、インデックス自体に含めることができるWHERE句の条件に制限があり、データベースのストレージフットプリントに追加されます。ただし、ユースケースがこれらのパラメーターに適合する場合、ストレージのトレードオフは、フィルター処理されたインデックスが提供できる大幅なパフォーマンスの向上と比較して、通常はごくわずかです。

Oracle Databaseのフィルター処理された索引についてはどうですか？

その後、私はFortune 500企業の大規模なチームに所属し、SQLServerからOracleへの移行プロジェクトの開発者/DBAとして働いていました。ソースデータベース（SQL Server 2008）を取り巻くコードは実装が不十分であり、パフォーマンスが低いために変換が不可欠でした。毎日のバックエンド同期ジョブが23時間以上実行されていました。フィルター処理されたインデックスはありませんでしたが、新しいOracle 11gシステムでは、フィルター処理されたインデックスが非常に有益であるケースが複数見られました。ただし、Oracle11gにはフィルタリングされたインデックスがありません。

また、最新のOracle18cにはフィルタリングされたインデックスが実装されていません。

しかし、技術専門家としての私たちの仕事は、私たちが持っているものを最大限に活用することです。そこで、Oracle 11gシステムにフィルター処理されたインデックスと同等のものを実装しました（そして、後で12cで使用したのと同じ手法）。この考え方は、OracleがNULLを処理する方法に基づいており、どのバージョンのOracleでも使用できます。

Oracleは、 NULL値を通常のデータと同じように扱いません。 OracleのNULLは何もありません—存在しません。その結果、インデックス付きの列をNULLABLE可能として定義し、 NULLの値で検索している場合、インデックスデータファイルには対象のレコードのみが含まれます。 Oracleインデックス定義にはINCLUDE句がないため、結果セットに含める必要のあるすべての列を含む複合インデックスを作成する必要があります。（この手法には、SQL ServerのINCLUDE句と比較してオーバーヘッドがありますが、かなり重要ではありません。）

このような回避策の実装には制限があります。先頭のインデックス列はNULLを許可する必要があるため、テーブルの主キーにすることはできません。ただし、このパフォーマンス最適化方法をサポートするために特別に作成された派生列または計算列にすることができます。ある意味で、インデックスの先頭の列は論理的にバイナリです。検索に含まれるデータの場合はNULLの値であり、「非表示」にする必要のあるデータの場合はNULLです。

SQL Serverのフィルター処理されたインデックスロジックをOracleに移行する際に考えられる他のオプションは、インデックス（またはテーブル全体）をパーティションとして実装することです。この場合、データベースエンジンは、関連するインデックスパーティションにのみアクセスします。ただし、 WHERE句で正確なパーティション化条件を使用することにより、クエリが正しく実装されます。

これは、大規模であっても、比較的静的なデータではうまく機能しますが、頻繁に変更されるデータに適用すると、DBAチームに高いメンテナンス負荷をかける可能性があります。例としては、時間中心のアプリケーションで今日のデータへのアクセスを最適化する場合があります。DBAチームは、パーティションを毎日再定義する必要があります。この再定義は夜間のメンテナンスジョブでスクリプト化できますが、システムがより複雑になり、新しい潜在的なシステム障害ポイントが発生します。

したがって、SQL ServerでフィルタリングされたインデックスロジックをOracleに移行する必要がある場合は、常に具体的かつ注意深く行う必要があります。

変換の処理方法

OracleからSQLServerへの移行では、フィルター処理されたインデックスを使用して最適化の機会を探します。 Oracleではフィルタリングされたインデックスは表示されませんが、 NULL値を含むインデックスは表示される場合があります。それらをそのままコピーしないでください。変換でパフォーマンスを向上させ、デザインを改善できる最高の場所である可能性があります。

SQL ServerからOracleへの移行で、フィルター処理されたインデックスが表示される場合は、対応するOracleコードのパフォーマンスのボトルネックを回避する方法を探してください。データフローを再設計して、フィルター処理されたインデックスがソース実装で提供したパフォーマンスの向上を補う方法をご覧ください。

SQLServerからOracle/OracleからSQLServerへの移行の課題の謎を解き明かす

OracleとSQLServerの間のどちらの方向への移行プロジェクトでも、関係するメカニズムをより深く理解することが重要です。それぞれのデータベースの現在のリリース（Oracle18cおよびMicrosoftSQL Server 2017 *）に、シーケンスやIDなど、互いの機能と同等の字句が含まれている場合、それは簡単な勝利のように思えるかもしれません。ただし、一方のRDBMSの優れたデザインをもう一方のRDBMSに直接コピーすると、コードが不必要に複雑になり、パフォーマンスが低下する可能性があります。

このシリーズの次の最後のパートでは、読み取りの一貫性と移行ツールの使用について説明します。乞うご期待！

* SQL Server 2019（または「15.x」）は、企業で広く採用されるほど長くはありませんでした。