行を項目ごとにCASE WHENで指定して値をSELECTする際に、帰ってくる値は、(222,null,null)のようにスカラではないため、MAX関数でスカラにしないと、エラーとなる。 なぜOracle 9iは空の文字列をNULLとして扱いますか? 少し待ってください。オプティマイザーは依然として Index Scan とは対照的な Seq Scan を選択していますね? sql - 高速化 - データベース 設計 null . oracleデータベースで表・テーブルの断片化を解消する方法をお探しではありませんか? 本記事では、表・テーブルの断片化を解消する方法を紹介しています。sqlサンプルもあるのでぜひ参考にしてくださ … みなさんこんにちは!フリーランスプログラマーのsatoです。 「is null」と聞いて、プログラムをかじったことがある人ならnullの判定に使うんだろうなと考える人が多いと思います。 しかしsqlには使用環境によって「isnull関数」や「is null演算子」が存在します。 ここでは、SQL文をプリコンパイルするために使用するプリペアド・ステートメントについて見ていきましょう。 サンプル・コードは、where句の中のパラメータだけが異なるselect文を、forループで1万回実行し、その実行時間を出力するものです。mainメソッドの引数がsの場合は、通常のステートメント(java.sql.Statement インターフェイス)を使用し、引数がpsの場合は、プリペアド・ステートメント(java.sql.PreparedStatement インターフェイス)を使用します。 Copyright © 2020-2020 いちれべ.com All Rights Reserved. 問題の説明 – Oracleとは異なり、空文字は PostgreSQL では null とはされません。Oracle は自動的に空文字を null 値に変換しますが、PostgreSQL では null 値に変換しません。 Amazon RDS for PostgreSQL と Amazon Aurora with PostgreSQL により、コスト効率の高い方法で PostgreSQL クラウドのデプロイを簡単にセットアップ、運用、拡張することができます。昨年、私たちは (数百 GB から数 TB に及ぶ) 100 を超える Oracle データベースを Amazon Aurora PostgreSQL と Amazon RDS for PostgreSQL に移行しました。, この記事では、移行中に持ち上がった最も一般的な問題のいくつかについて説明します。皆さんは AWS Database Migration Service (AWS DMS) を使用して、あるデータベースから別のデータベースにデータを移動させた経験があることでしょう。私も AWS Schema Conversion Tool (AWS SCT) をかなり使い込みました。手始めに、データ抽出プロセスで直面する可能性のある問題を取り上げます。次に、データの移行中に起こる問題について取り上げます。最後に、移行後に PostgreSQL で観察するパフォーマンスの問題について説明します。, このフェーズで一般的に直面する問題は、大きなテーブルのデータ抽出が遅くなり、ソース DB で ORA-01555 エラー (スナップショットが古すぎます) が発生することです。経験上、500 GB を超えるテーブルの場合にその問題が頻繁に発生するようです。相対的にはそうですが、環境に依存するところもあります。次のセクションでは、ORA-01555 のエラーと、抽出プロセスを高速化するためにエラーを回避する方法について説明します。, 問題の説明 – Oracle から大きなテーブルまたはテーブルパーティションを移行する場合、抽出クエリの実行時間が長くなるため、クエリが ORA-01555 エラーで失敗することがあります。Oracle をよく使われている方は、過去にこのエラーが発生したのを見たことがあるだろうと思われます。クエリは、作成できないブロックの古いイメージを要求しています。そのイメージの作成に必要なアンドゥ情報はすでに上書きされています。ターゲットデータベースのスループットが制限されていると、ORA-01555 エラーが発生して抽出による全体的な移行時間が増加する可能性があります。, ソリューション – この問題を解決するには他にもアプローチがあります。考えられるアプローチは次のいずれかです。, 要するに、ご自身のユースケースに最適なソリューションを見つけることです。たとえば、テーブルを移行していて、90〜95% のデータをコピーした後に全ロードが失敗した場合、この問題を修正するには、アンドゥ保持サイズを増やすことが適切であるかもしれません。ただし、アンドゥ保持サイズがある程度あるときに、20% のデータをコピーした後に全ロードが失敗した場合、データパージ、ソースフィルタ、またはバイスタンダーなどの他の選択肢を検討してみてください。, これは、スキーマ変換とデータ移行が組み合わされた最も難しい段階です。データ型変換の問題、文字セットの不一致問題、ラージオブジェクトの移行などの問題に対処する必要があります。以下、最も一般的な問題のいくつかを取り上げます。, 問題の説明 – スキーマ変換時に、SCT は number データ型 (精度なし) を PostgreSQL の double precision データ型に変換し、number データ型 (精度あり) は numeric (p) として残します。これらの列に対して結合を実行すると、PostgreSQL オプティマイザーはこれらの列のインデックスを活用できないため、パフォーマンスが低下します。, ソリューション – この問題を説明するために、PostgreSQL に 2 つのテーブルを作成しました。1 つは numeric 列、もう 1 つは double precision です。これらの 2 つの列にもインデックスを追加しました。下記の実行計画からわかるように、オプティマイザーは、テーブルが numeric 列と double precision 列で結合されている場合に、Seq Scan を選択します。dept テーブルで列のデータ型を double precision から numeric に変更すると、オプティマイザーは適切な実行計画、つまり Index Scan を選択します。移行中に列の精度に注意を払い、number 列 (精度なし) を numeric に上書きするようにしてください。, これに加えて、PostgreSQL で正しい数値データ型を選択することが非常に重要です。本書の後半の point#4 でそれについて省察します。, 問題の説明 – Oracleとは異なり、空文字は PostgreSQL では null とはされません。Oracle は自動的に空文字を null 値に変換しますが、PostgreSQL では null 値に変換しません。アプリケーションで空文字を挿入し、Oracle で null 値として扱う場合は、PostgreSQL に移行する前に、アプリケーションがデータを挿入または抽出する方法を変更します。ユースケースでこれをよりよく理解できます。, 作業 – Oracle と PostgreSQL が null 値と空文字をどのように扱うかを並べて比較しました。下の例では、Oracle、PostgreSQL で全く同じテーブルを作成しています。両方のデータベースに、名前列が null の行と名前列が空文字を持つ行を挿入しています。, 見てわかるとおり、null は両方のデータベースで同じものとして扱われます。ただし、空文字を挿入すると、Oracle ではこれを null として格納します。そして述語「where is null 」を使用してクエリを実行すると、両方の値が表示されます。一方、同じ述語「where name is null」を使用してクエリを実行しても、PostgreSQL では空文字を含む行が表示されません。PostgreSQL で行を見つけるには、「=」演算子を使ってクエリを実行し、述語で空文字を探します。, 結論 – 空文字を挿入し、空文字を null 値として扱う場合は、PostgreSQL でアプリケーションの結果が異なります。PostgreSQL に移行する前に、すべての null 値 (空文字ではなく) に null を追加するか、すべての選択クエリを変更して null とともに空文字に「like」演算子を含めるかを確認して、アプリケーションが PostgreSQL にデータを挿入する方法を変更する必要があります。したがって、Oracle の null 動作をレプリケートするために、クエリは [select * from emp where name is null or name like ”] のような感じになります。, PostgreSQL が Oracle と異なり null をどのように処理するかに基づいてクエリやアプリケーション設計を変更する必要がある点は、強調しきれないぐらい重要です。Oracle から移行する場合は、null の問題が発生する可能性があります。AWS のアプリケーションの多くは、Oracle が null を処理する方法に従って null を処理するように設計されています。PostgreSQL に移行する他の DB プラットフォームについても同じことがいえるでしょう。, 複合一意インデックスがあるときに、それらの列の 1 つに null を挿入する場合に何が起こるかを調べるために、さらに詳しく見ていきます。ここでも、Oracle と PostgreSQL の動作を並べて比較しています。, 上のユースケースからわかるように、Empname、empid 列に複合一意制約を追加しました。ここで、最初の列 Empname の重複値を empid が null の「AA」として挿入しようとすると、Oracle は例外 (一意制約違反) をスローします。しかし、PostgreSQL では 2 つの null が等しくないので、2 番目の列 (empid) が異なると考え、重複した「AA」値を許容します。PostgreSQL では、null 値は未知の値を表し、2 つの未知の値が等しいかどうかを判断することはできません。, Oracle の動作を複製する方法の 1 つは、PostgreSQL で部分インデックスを使用することです。部分インデックスの使用法を示すスニペットを次に示します。, また、null 問題を扱い、アプリケーションコードが null 比較で「=」演算子を用いている場合は、PostgreSQL のマニュアルでパラメータ Transform_ null _equals を読むことをお勧めします。それは永続的な修正ではなく、一時的な回避策としてのみ使用してください。それをそのままにしておくのは好ましくありません。, これは、Oracleから PostgreSQL への移行中に私が見たよくある間違いです。デフォルトでは、多くの DBA は、Oracle のすべての number 列を変換するために、smallint、integer、bigint より PostgreSQL の numeric データ型を選択します。これには多くの理由がありますが、移行中にエラーが発生するものにのみ注意を払います。PostgreSQL のドキュメントには、必要な範囲でのみ触れます。たぶん私は特定の RDBMS で作業することに慣れているため、その RDBMS で実行できるオプションについて考える傾向があるかもしれません。, 私の疑問は、Oracle の number と PostgreSQL の numeric は同じか、という点にあります。 両者とも数値を保持しているため同じようなものかもしれませんが、大きな違いが 1 点あります。Oracle では、最大の数値データ型は 38 桁保持できますが、PostgreSQL では 131,072 桁保持できるという点です。PostgreSQL には、smallint、integer、bigint など、いくつかの数値データ型が用意されています。範囲、パフォーマンス、およびストレージの最適なバランスを見つけて、最適な数値データ型を選択します。, bigint や interger の代わりに numeric を選択すると、パフォーマンスが犠牲になってしまいます。それは並べて比較することで見つけることができます。, 次のスニペットでは、2 つの異なる PostgreSQL データ型を並べて比較しています。実際には、bigint、integer、numeric という 3 つのデータ型を比較しました。integer は bigint と同様に動作していたので、この図では削除しました。, テストを見てわかるように、numeric の代わりに bigint を使用すると、小さなデータセットでも 30〜35% の大幅なパフォーマンスの向上を実現できます。, 問題の説明 – PostgreSQL のシーケンス値はエンジンプロセスによってキャッシュされ、シーケンスのキャッシュ値が 1 より大きく、複数のセッションで同時に使用すると予期しない結果になることがあります。, 作業 – 問題を説明するために、Oracle と PostgreSQL でシーケンス値がどのように生成されるかを比較します。次のスニペットでは、シーケンスが 100 から始まり、キャッシュサイズは 20 です。セッション A とセッション B は、同じシーケンスを使用して Oracle と PostgreSQL で次の値を生成しています。, Oracle (スニペットの左半分) では、セッション A とセッション B がデータベースオブジェクトにアクセスしてシーケンスの次の値を生成するため、異なるセッションによって生成されたシーケンス番号が順番になります。, PostgreSQL では、セッション A は 100〜120 の値をキャッシュし、セッション B は 121〜140 の値をキャッシュします。したがって、セッション B は 120 以上の値を生成し、セッション A は 100〜119 の値を生成し続けます。, PostgreSQL のセッションベースのキャッシュを見てわかるように、シーケンス値は順不同であり、両方のセッションが同時に同じテーブルに挿入されている場合、セッション B の値 121 をセッション A の値 102 の前に挿入できます。これは、アプリケーションはシーケンスから生成される値の順序に依存している場合に問題を引き起こします。, ソリューション – アプリケーションにこのような依存性がある場合は、アプリケーション設計を変更するか、すべてのシーケンスのキャッシュサイズを 1 にします。, PostgreSQL への最終切り替え前の問題の 1 つは、読み込みを PostgreSQL に切り替えた後にテキスト検索中に発生する可能性があるパフォーマンスへの影響をどう管理するか、という点です。このセクションでは、この問題について詳述します。, 問題の説明 – PostgreSQL データベースではフルテキスト検索が他のいくつかの RDBMS と比較して全体的に遅く、通常の Btree インデックスを使ってもテキスト検索に役立たない可能性があります。たとえば、「like」演算子を使用して実行している SQL は、Oracle でのインデックススキャンと違い、エンジンクエリオプティマイザーがフルテーブルスキャン (seq スキャン) を実行するため、速度が大幅に低下する可能性があります。, ソリューション – PostgreSQL でテキスト検索を高速化するために、汎用転置 (GIN) インデックスと汎用検索ツリー (GiST) インデックスを使用できます。GIN インデックスは、フルテキスト検索を行う必要がある場合に役立ちます。GIN インデックスまたは GiST インデックスを使用するには、pg_trgm 拡張機能をインストールします。拡張機能を追加すると、GIN インデックスをすばやく作成できます。この記事では GIN インデックスのみを扱っていますが、GIN インデックスと GiST インデックスの長所と短所を踏まえ、環境内で実装することをお勧めします。要約すると、GIN は静的データに適していて、検索は高速ですが更新は遅く、GiST は動的データに適していて、更新は高速ですが検索は遅くなります。, 作業 – 問題を説明するために、従業員テーブル (emp) を作成します。ここではランダムな md5 を使用してテーブルにデータを入力し、フルテキスト検索に like 演算子を使用しています。以下に示すように、オプティマイザーはすべての行をフィルタリングし、Seq Scan を使用します。自然の流れとして、インデックスはありません。. . インポート実行時は必ずbufferを指定します。デフォルトでは、4キロバイトずつしか処理できません。 B, ƒƒfƒBƒAˆê—— | ŒöŽ®SNS | LˆÄ“à | ‚¨–â‚¢‡‚킹 | ƒvƒ‰ƒCƒoƒV[ƒ|ƒŠƒV[ | RSS | ‰^‰c‰ïŽÐ | Ì—pî•ñ, java.sql.PreparedStatement ƒCƒ“ƒ^[ƒtƒFƒCƒX, ƒNƒ‰ƒEƒh‚ÅKubernetes‚ðŠw‚ԁ\\ƒ}ƒl[ƒWƒhƒT[ƒrƒX‚ÅŽn‚ß‚éKubernetes“ü–å, uƒeƒŒƒ[ƒN‚ªƒRƒƒiŒã‚̃jƒ…[ƒm[ƒ}ƒ‹‚É‚È‚év‚Í–{“–‚©\\uƒŠƒ‚[ƒgƒ[ƒNvuÝ‘î‹Î–±vŠÖ˜Aƒjƒ…[ƒX‚Ü‚Æ‚ß, uƒRƒƒi‰Ðv‚ÅŒƒ•Ï‚µ‚½Šé‹Æ‚ÆITƒGƒ“ƒWƒjƒA‚́u¶‚«Žc‚èí—ªv\\“ÇŽÒ’²¸‚ƃjƒ…[ƒX‹LŽ–‚©‚çl‚¦‚é. Python join:なぜlist.join(string)の代わりにstring.join(list)ですか? なぜこのコードはランダムな文字列を使って "hello world"を印刷しますか. Abhinav Sarin は Amazon の ビッグデータテクノロジーチームのデータベースマネージャーです。彼は、Amazon の内部顧客と協力して、いくつかものサービスをオンプレミスの Oracle から Aurora、RDS PostgreSQL、RDS MySQL データベースへ移行しています。, 原文は How to solve some common challenges faced while migrating from Oracle to PostgreSQL。, Migrate-your-oracle-database-to-PostgreSQL/, How to solve some common challenges faced while migrating from Oracle to PostgreSQL, ソースが、抽出クエリランタイムよりも長いデータの古いイメージを保持していることを確認する。, 抽出クエリのチューニング: パフォーマンスの向上により、ORA-01555 エラーが発生する可能性が減ります。したがって、抽出クエリがより速く実行されるように、ソースに適切なインデックスがあることを確認してください。場合によっては、移行のために固有の新しいインデックスを作成できます。移動させるデータ量を考慮してください。すべてのデータ (テーブルのすべての行) を転送する必要がありますか? 下記のコード(パターン4以外)は、col_name の値が null であれば 0 、null 以外であれば 1 を返却している。 Oracle 11gにて検証. 本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。 本書の後半の point#4 でそれについて省察します。 PostgreSQL の空白文字と null. AWS DMS のベストプラクティスと移行の最新バージョンのレプリケーションインスタンスに従ってください。けれども問題に直面した場合は、問題を緩和し、正しい方向性を示すのにこの記事がお役に立てれば幸いです。ご不明な点がございましたら、コメントをお寄せください。幸運を祈ります! 【ゼビオ公式】ニューバランス | WL574 - 特価7,832円 | さらに71ポイント還元, ViViにも特集された、話題沸騰の美肌成分も配合されたバストクリーム【マニグランド】.