PostgreSQL は、オープンソースのリレーショナルデータベースの中でも最も人気のあるシステムの 1 つです。Oracle や Microsoft SQL Server などの商用データベースから移行する場合、最高のデータベースの選択肢の 1 つと言ってもいいでしょう。AWS には 2 つのマネージド PostgreSQL オプションである Amazon RDS と Amazon Aurora があります。
マネージド PostgreSQL サービスに加えて、AWS は移行を支援するツールやリソースも提供しています。AWS Schema Conversion Tool (SCT) は既存のスキーマの変換や、複数のソースデータベースとターゲットデータベースをサポートする無料の AWS ツールです。AWS には AWS Database Migration Service (DMS) もあります。これは異種データベース間および同種データベース間でデータを転送し、継続的にレプリケートを行うのに役立ちます。同様に、商用データベースと PostgreSQL などのオープンソースデータベースとの間の多数の機能マッピングを文書化した移行プレイブックを提供しています。
この投稿では、コードを PL/SQL から PL/pgSQL に変換するためのヒントとベストプラクティスをご紹介します。これにより、パフォーマンスの向上や PostgreSQL へのコード変換を実行できます。この投稿は、データベースの移行に取り組む開発者を対象としており、読者はデータベースと PL/SQL の基本的な知識があることを前提としています。
パフォーマンスに関する考慮事項
このセクションでは、SQL Server や Oracle などの商用データベースまたはレガシーデータベースから移行する際に、PostgreSQL のパフォーマンスの向上に影響するいくつかの要因について説明しています。データベースにはたいてい類似したオブジェクトがありますが、正しいオブジェクトについて考慮すると、システムの動作が改善されます。このセクションでは、ストアドプロシージャ、関数、SQL ステートメントを使ってパフォーマンスを向上させる方法について説明します。
データ型
作業のやり直しを避けるには、プロジェクトを開始する前に、ターゲットデータベースのデータ型をソースシステムに正しくマッピングします。次のテーブルは、Oracle および SQL Server から PostgreSQL への一般的なデータ型マッピングをまとめたものです。
| Oracle |
PostgreSQL |
SQL Server |
注意 |
| 数値 |
小整数 |
Tinyint/Smallint (小さな数値) |
一般に、テーブルの値が制限されているルックアップテーブルの場合。 |
| 数値 |
整数/Bigint (大きな数値) |
整数/Bigint (大きな数値) |
|
| 数値 |
倍精度/ 浮動/数値 |
倍精度/ 浮動/数値 |
アプリケーションに高精度な値を保存するような金融ドメインの場合、数値/10 進数として設定するかもしれません。そうでなければ、倍精度または浮動小数点で十分でしょう。 |
| Varchar |
Char(n) Varchar(n) Varchar テキスト 可変長文字型 |
Nchar Nvarchar Ntext |
|
| Timestamp(6) |
タイムゾーンのないタイムスタンプ |
DateTime2(p) DateTime |
|
| Clob |
テキスト |
|
|
| Blob |
未加工 |
|
|
|
Bytea |
バイナリ、画像、VarBinary |
|
| ブール値 |
ブール値 |
ビット |
|
| XML |
XML |
XML |
|
smallint/整数/bigint に数値ではなく番号を付ける理由
データベースから最高のパフォーマンスを得るには、最適なデータ型を使用することが重要です。
テーブル列に最大 4 桁の数字を含める必要がある場合、4 (整数/実数)、8 (bigint/倍精度)、または変数 (数値) のバイトデータ型を定義するのではなく、2 (smallint) バイトの列データ型で十分です。
数値は 131,000 桁を保持できる複合型のため、正確さが必要な金額やその他の数量の保存に推奨されます。しかし演算子が遅いため、数値の計算は整数型または浮動小数点型に比べて極めて遅くなります。
次のテーブルは、インデックスを除く非精度列の数値サイズを smallint/int/bigint と比較したときに、単一列のテーブルサイズがどのように増加するかを示しています。
| TN |
サイズ |
外部サイズ |
挿入される値 |
| numericsize |
16 KB |
8192 バイト |
numericsize 値に挿入 (1234678) |
| smallintsize |
8192 バイト |
0 バイト |
numericsize 値に挿入 (1234) |
| intsize |
8192 バイト |
0 バイト |
numericsize 値に挿入 (123457) |
| bigintsize |
8192 バイト |
0 バイト |
numericsize 値に挿入 (123486) |
次のテーブルでは前のテーブルと同じ情報を使用していますが、インデックスが含まれています。このテーブルでは、サイズはテーブルの合計サイズを指し、外部サイズはインデックスなどの関連オブジェクトのサイズを指します。
| TN |
サイズ |
外部サイズ |
| numericsize |
32 KB |
24 KB |
| smallintsize |
24 KB |
16 KB |
| intsize |
24 KB |
16 KB |
| bigintsize |
24 KB |
16 KB |
AWS SCT は実際のデータサイズが分からなくても、テーブルの数値データ型に数値をマッピングします。このツールには、変換中に正しいデータ型を設定/マッピングするオプションがあります。
プロシージャと関数
PostgreSQL 10 以前のバージョンには、プロシージャのサポートがありません。Oracle および SQL Server のすべてのプロシージャと関数は、PostgreSQL の関数にマッピングされます。これらのプロシージャはバージョン 11 以降の PostgreSQL でサポートされており、Oracle に類似しています。
PostgreSQL は、Volatile、Stable、Immutable の 3 つの変動性カテゴリの関数をサポートしています。移行中は、機能に基づいて適切な型を使用する必要があります。パフォーマンスの調整には、関数型を適切にマークすることが重要です。
volatile 型
volatile 型は 1 回のテーブルスキャン内でも関数値が変更する可能性があります。そのため、最適化ができません。比較的少ないですが、データベース関数は発揮性です。Random()、currval()、timeofday() がそれらの例です。副作用のある関数は、結果が予測可能であっても、呼び出しが最適化されないように発揮性として分類する必要があります。その例の 1 つが setval() です。関数の作成中に volatility 型がない場合、すべての新しい関数はデフォルトで volatile 型としてマークされます。
以下は、Volatile 関数の実行にかかる時間を示すサンプル関数です。
Create Or Replace Function add_ten_v(num int) Returns integer AS $$
Begin
Perform pg_sleep(0.01);
Return num + 10;
End
$$ Language 'plpgsql' Volatile;
以下の関数を実行して、関数のコストを確認します。
lab=>Explain Analyze Select add_ten_v(10)FROM generate_series(1,100,1);
Query plan
-----------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..260.00 rows=1000 width=4) (actual time=10.200..1015.461 rows=100 loops=1)
Planning time: 0.030 ms
Execution time: 1015.501 ms
(3 rows)
Time: 1016.313 ms
Stable
Stable 型は、関数がデータベースを変更できないことを示します。単一のテーブルスキャン内で、同じ引数値に対して一貫して同じ結果を返しますが、その結果は SQL ステートメント間で変わる可能性があることも示しています。これは、結果が現在のタイムゾーンなどのデータベースルックアップまたはパラメーター変数に依存する関数に適しています。関数の current_timestamp ファミリーが値がトランザクション内で変更されないため、安定と見なされます。
以下は、Stable 関数の実行にかかる時間を示すサンプル関数です。
Create Or Replace Function add_ten_s(num int) Returns integer AS $$
Begin
Perform pg_sleep(0.01);
Return num + 10;
End
$$ Language 'plpgsql' Stable;
以下の関数を実行して、関数のコストを確認します。
lab=> Explain Analyze Select add_ten_s(10) From generate_series(1,100,1);
Query Plan
-------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..260.00 rows=1000 width=4) (actual time=10.153..1013.814 rows=100 loops=1)
Planning time: 0.031 ms
Execution time: 1013.846 ms
(3 rows)
Time: 1014.507 ms
Immutable
Immutable 型は関数がデータベースを変更できず、同じ引数値が指定されたときには常に同じ結果を返すことを示します。つまり、データベースのルックアップを行わない、または引数リストに直接存在しない情報を使用しないことを意味します。このオプションを指定すると、すべて定数の引数を使用した関数の呼び出しを関数の値に即座に置き換えることができます。
以下は、Immutable 関数の実行にかかる時間を示すサンプル関数です。
Create Or Replace Function add_ten_i(num int) Returns integer AS $$
Begin
Perform pg_sleep(0.01);
Return num + 10;
End
$$ Language 'plpgsql' Immutable;
以下の関数を実行して、関数のコストを確認します。
lab=> Explain Analyze Select Add_Ten_I(10) From Generate_Series(1,100,1);
Query Plan
--------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=4) (actual time=0.009..0.016 rows=100 loops=1)
Planning time: 10.185 ms
Execution time: 0.030 ms
(3 rows)
Time: 10.681 ms
これらの関数はすべて、次の値を返します。
lab=> Select Add_Ten_V(10), Add_Ten_S(10), Add_Ten_I(10);
add_ten_v | add_ten_s | add_ten_i
-----------+-----------+-----------
20 | 20 | 20
(1 row)
上記のすべての関数は同じ値を返しますが、パフォーマンスを向上させるには、機能に応じてこれら 3 つの関数タイプのいずれかを使用する必要があります。
これらの各関数のテストの実行は、関数に同じ機能が含まれていることを示していますが、Immutable バリアントでは最小限の時間しかかかりません。これは、このカテゴリによって、オプティマイザが定数引数を使用したクエリ呼び出し中に関数を事前評価できるためです。
ビューとクエリでの関数呼び出し
多くのアプリケーションでは、関数呼び出しを含むビューとクエリを使用しています。前のセクションで説明したように、PostgreSQL では特に関数の揮発性のカテゴリが正しく設定されていない場合、コストが高くつく操作となる可能性があります。これに加えて、関数呼び出し自体がクエリコストに追加されます。
関数の機能に基づいて、関数に適切な揮発性を選択します。関数が本当に Immutable または Stable である場合、デフォルトの Volatile を使用せずにこれらを設定すると、パフォーマンスが向上します。
次のコード例は、Volatile 関数呼び出しを使用したクエリです。
Explain Analyze
Select Empid,
Empname,
Getdeptname(Deptid),
Salary,
Doj,
Address
From Emp
Where Deptid=2
関数 getDeptname() は volatile としてマークされています。クエリの合計ランタイムは 2 秒と 886 ミリ秒です。
次のコード例は、Stable 関数呼び出しを使用したクエリです。
Explain Analyze
Select Empid,
Empname,
Getdeptnames(Deptid),
Salary,
Doj,
Address
From Emp
Where Deptid=2
getDeptname() 関数安定していると言えます。クエリの合計ランタイムは 2 秒と 644 ミリ秒です。
次のコード例では、関数呼び出しを機能に置き換えます。
Explain Analyze
Select Empid,
Empname,
Deptname,
Salary,
Doj,
Address
From Emp E
Join Dept D On D.Deptid = E.Deptid
Where E.Deptid=2
関数のロジックがクエリに正常に移動しました。クエリの合計ランタイムは 933 ミリ秒です。
例外の最適化
PostgreSQL には、Exception および Raise ステートメントを使用して、エラーをトラップおよび発生させる機能があります。これは便利な機能ですが、コストがかかります。
Raise ステートメントは、PL/pgSQL 関数の操作中にエラーと例外を発生させます。デフォルトでは、PL/pgSQL 関数内でエラーが発生すると、関数は実行を中止し、変更をロールバックします。エラーから回復するには、PL/pgSQL は Exception 句を使用してエラーをトラップします。この機能を使用するには、PostgreSQL は例外処理を伴うコードブロックに入る前にトランザクションの状態を保存する必要があります。これは高くつく操作のため、オーバーヘッドコストが追加されます。
このオーバーヘッドを回避するには、アプリケーション側で例外をキャッチするか、関数が例外を発生させないように必要な検証を確実に行うことを確認することをお勧めします。
次のコード例は、関数呼び出しに例外が発生するためにパフォーマンスに影響があることを示しています。
Create Or Replace Function empsal (eid int)
Returns Integer AS $total$
Declare
Total Integer;
Begin
Update Emp Set Salary = Salary * 0.20 Where Empid = Eid;
Return 1;
End;
$$ Total Language Plpgsql;
Create Or Replace Function Empsalexcep (Eid Int)
Returns Integer AS $Total$
Declare
Total Integer;
Begin
Update Emp Set Salary = Salary * 0.20 Where Empid = Eid;
RETURN 1;
Exception
When Others Then
Raise Notice 'Salary Update Failed ';
END;
$$ Total Language Plpgsql;
Select * From Empsal(3) – 78ms -- without exception handling
Select * From Empsalexcep(3) - 84ms -- with exception handling
例外が発生せず検証できないのであれば、明らかに例外が必要です。前の例では、診断をチェックして、処理された変更があるかどうかを確認できます。可能であれば、例外処理の使用を避けることをお勧めします。
フェッチ操作にカウンターは不要
たいていのアプリケーションは合計カウントを取得し、カーソルをループしてレコードを取リ込みます。レコードが存在しない場合、フェッチ操作は null を返すため、別の 2 つの変数を宣言してカウントをチェックすることでカウントをループするのではなく、フェッチステータスを使用することをお勧めします。実行するステートメントの数を減らしてパフォーマンスを向上させることにより、余分な変数の宣言や増加値のチェックを回避できます。例として次のコードをご参照ください。
Select Count(1) Into Count_Value
From Tab1
Where Tab1.A = Value
Counter = 0
Open Dvscriptcursor For Select Id From Tab1;
While (Counter < Count_Value)
Loop
Fetch Id Into Var_Id
……..
…….
Counter = Counter +1;
End Loop
以下のように、このコードを書き換えることもできます。これで、2 つの変数の宣言を回避でき、カーソル自体を使用して反復し、カーソルステータスを使用してループを中断/終了できます。
OPEN Dvscriptcursor For Select Id From Tab1;
Loop
Fetch Id Into Var_Id
Exit When Not Found
……..
…….
…….
End Loop
カウントではなく EXISTS で確認する
レガシーアプリケーションでは、SQL クエリを記述して一致するレコードの数を見つけて、必要なビジネスロジックを適用します。テーブルに数十億のレコードがある場合、レコードカウントの取得にコストがかかる可能性があります。
以下のコードサンプルは、行数を確認してからデータを更新する方法を示しています。
Create Or Replace Function Empsal (Eid Int)
Returns Integer As $Total$
Declare
Total Integer;
Begin
If (Select Count(*) From Emp Where Empid = Eid) > 0 Then -- Wrong Usage
Update Emp Set Salary = Salary * 0.20 Where Empid = Eid ;
End If;
Return 1;
End;
$Total$ Language plpgsql;
クエリの合計ランタイムは 163 ミリ秒です。
このコードは、行全体ではなく 1 つの列をチェックするように書き換えることもできます。こうすると、コストとパフォーマンスの効率を向上できます。以下のサンプルコードをご参照ください。
Create Or Replace Function Empsal (Eid Int)
Returns Integer AS $Total$
Declare
Total Integer;
Begin
If Exists (Select 1 From Emp Where Empid = Eid) Then. – Right Usage
Update Emp Set Salary = Salary * 0.20 Where Empid = Eid ;
End If;
RETURN 1;
END;
$$ Total Language plpgsql;
クエリの合計ランタイムは 104 ミリ秒です。
DML ステートメントの後のレコード数
ほとんどのレガシーアプリケーションでは、レコード数はデータ操作ステートメントに変更があるかどうかを示します。PostgreSQL では、この情報は統計情報として保持されます。これを取得して、操作後の値のカウントを回避することができます。以下のコードサンプルが示すように、診断を使って、影響を受ける行の数を取得します。
Create Or Replace Function Empsal (Eid Int)
Returns Integer AS $Total$
Declare
Total Integer;
Rows_Affected Int;
Begin
If Exists (Select 1 From Emp Where Empid = Eid) Then
Update Emp Set Salary = Salary * 0.20 Where Empid = Eid ;
Get Diagnostics Rows_Affected = ROW_COUNT;
End If;
RETURN 1;
END;
$$ Total Language plpgsql;
パターン一致と検索
ワイルドカード文字 ％ または _ を LIKE (あるいは区別しない検索の場合は ILIKE) 式とともに使用して、テーブルからデータを取得するのが一般的です。ワイルドカード文字が指定されたパターンの先頭にある場合、クエリプランナーはインデックスが存在してもインデックスを使用できません。このような場合、シーケンシャルスキャンを使用しますが、時間がかかります。クエリプランナーが利用可能なインデックスを使用できるようにして、数百万のレコードでパフォーマンスを向上するには、述語の先頭ではなく中央または末尾にワイルドカード文字を使用します。こうすると、プランナーがインデックスを使用するように強制します。
LIKEし 式に加えて、pg_trgm モジュール/拡張機能をパターンマッチングに使用することもできます。pg_trgm モジュールは、英数字テキストの類似性を判断する関数と演算子を提供します。同様の文字列の高速検索をサポートするインデックス演算子クラスも提供します。詳細については、PostgreSQL ウェブサイトの pg_trgm ドキュメントをご参照ください。
Oracle、SQL Server、PostgreSQL 間の変換マッピング
このセクションでは、Oracle、SQL Server、PostgreSQL データベース全体で SQL ステートメントを作成する際のデータベース固有の比較について説明します。
デフォルトの FROM 句
Oracle では、FROM 句は必須です。これは、コード Select 1 from Dual; を使用します。PostgreSQL および SQL の場合、コード Select 1; の使用はオプションです。
一連の値の生成
開始から終了までの一連の値を生成します。
Oracle の場合、開始番号は必要ありませんが、終了番号を指定できます。例として次のコードをご参照ください。
Select Rownum As Rownum_Value From Dual Connect By Level <= 64
開始番号と終了番号を使用する場合、次のコードを使用します。
With t(n) As (
Select 1 from dual
Union All
Select n+1 From t Where n < 64
)
Select * From t;
PostgreSQL では、次のコードを使用します。
Select Generate_Series(1,64) AS Rownum_Value
SQL Server では、次のコードを使用します。
;With n(n) As
(
Select 1
Union All
Select n+1 From n Where n < 64
)
Select n From n Order By n
(+) 演算子での結合
Oracle では、左結合の場合、次のコードを使用します。
Select b.id, b.title, b.author, b.year_published, l.name language
From books b, ibrary.languages l
Where l.id (+)= b.language_id
Order By b.id
右結合の場合、次のコードを使用します。
Select b.id, b.title, b.author, b.year_published, l.name language
From books b, ibrary.languages l
Where l.id = b.language_id (+)
Order BY b.id
詳細については、Oracle データベースのサイトにある「SQL for Beginners (Part 5): Joins」をご参照ください。
PostgreSQL または SQL Server には、テーブルに左結合または右結合を行う「+」の機能はありません。代わりに、次の 2 つのクエリを使用します。
Select b.id, b.title, b.author, b.year_published, l.name language
From books b,
Left join ibrary.languages l
On l.id = b.language_id
Order BY b.id
Select b.id, b.title, b.author, b.year_published, l.name language
From books b,
Right join ibrary.languages l
On l.id = b.language_id
Order By b.id
関数へのパラメーターとして入力
SQL Server では、Type データ型の複数のレコードを渡すことができます。PostgreSQL で同じものを実装するには、JSON 形式または配列の JSON データ型あるいはテキストデータ型としてこれを使用します次のコード例は、複数のレコードを含む JSON 形式のテキストデータ型を使用しています。これを一時テーブルに挿入し、次のコードを使ってさらに処理を実行できます。
Create Table emptable1
(
empid integer,
last_name varchar(100),
first_name varchar(100),
deptid integer,
salary double precision
)
Oracle
次のコードは、Oracle の varchar データ型で複数のレコードを渡す方法を示しています。
DECLARE
StructType Varchar2(1000) Default '[{"empid" : 1, "last_name":"AccName1", "first_name":"AccName1", "deptid":"1", "salary":"1234.578"}
,{"empid" : "2", "last_name":"AccName2", "first_name":"AccName2", "deptid":"2", "salary":"4567.578"}
]';
Begin
Insert Into emptable1 (empid,last_name,first_name,deptid,salary)
With Json As
( Select StructType --'[{"firstName": "Tobias", "lastName":"Jellema"},{"firstName": "Anna", "lastName":"Vink"} ]' doc
from dual
)
Select empid,last_name,first_name,deptid,salary
From json_table( (Select StructType from json) , '$[*]'
Columns ( empid PATH '$.empid'
,last_name Path '$.last_name'
, first_name Path '$.first_name'
,deptid Path '$.deptid'
,salary Path '$.salary'
)
);
End;
SQL Server
次のコードは、上記の Oracle での機能と同じく、SQL Server のテーブル型で複数のレコードを渡す方法を示しています。
--Create Type structure
Create Type empTableType as Table
(
empid integer,
last_name varchar(100),
first_name varchar(100),
deptid integer,
salary double precision
);
--Create Procedure
Create Procedure InsertEmpTable
@InsertEmpt_TVP empTableType READONLY
As
Insert Into emptable1(empid,last_name,first_name,deptid,salary)
Select * FROM @InsertEmpt_TVP;
Go
--Calling the SP with dynamic block and type
Declare @EmpTVP AS empTableType;
Insert Into @EmpTVP(empid,last_name,first_name,deptid,salary)
Values (1,'FirstName','Last_name',1,1234.566),
(2,'FirstName','Last_name',1,1234.566),
(3,'FirstName','Last_name',1,1234.566),
(4,'FirstName','Last_name',1,1234.566),
(5,'FirstName','Last_name',1,1234.566);
Exec InsertEmpTable @EmpTVP;
Go
PostgreSQL
次のコードは、Oracle および SQL Server で上記に示したものと同じ機能において、PostgreSQL で複数のレコードをテキスト型として渡す方法を示しています。
Do $$
Declare
StructType Text Default '[{"empid" : "1", "last_name":"AccName1", "first_name":"AccName1", "deptid":"1", "salary":"1234.578"},
{"empid" : "2", "last_name":"AccName2", "first_name":"AccName2", "deptid":"2", "salary":"4567.578"}]';
Begin
Insert Into emptable
Select * From json_to_recordset(StructType::json)
as x("empid" Int, "last_name" Varchar, "first_name" Varchar, "deptid" Int, "salary" Double Precision);
End $$
ピボットの変換
PostgreSQL ではピボット機能は有効になっていないため、拡張が必要です。拡張機能 tablefuncは、SQL Server や Oracle と同様にピボットテーブルの作成に使用する crosstab 関数を有効にします。以下は、Oracle、SQL Server、PostgreSQL のピボット機能のコードです。
Create Table crosstabFunc (
id Number,
customer_id Number,
product_code Varchar2(5),
quantity Number
);
Insert Into crosstabFunc values (1, 1, 'A', 10);
Insert Into crosstabFunc Values (2, 1, 'B', 20);
Insert Into crosstabFunc Values (3, 1, 'C', 30);
Insert Into crosstabFunc Values (4, 2, 'A', 40);
Insert Into crosstabFunc Values (5, 2, 'C', 50);
Insert Into crosstabFunc Values (6, 3, 'A', 60);
Insert Into crosstabFunc Values (7, 3, 'B', 70);
Insert Into crosstabFunc Values (8, 3, 'C', 80);
Insert Into crosstabFunc Values (9, 3, 'D', 90);
Insert Into crosstabFunc Values (10, 4, 'A', 100);
Oracle
次のコードを使用して、Oracle にピボット機能を実装します。
Select *
From (Select customer_id, product_code, quantity
From crosstabFunc)
Pivot (Sum(quantity) As sum_quantity For (product_code) In ('A' AS a, 'B' AS b, 'C' AS c))
Order By customer_id;
SQL Server
次のコードを使用して、SQL Server にピボット機能を実装します。
Select * From
(Select customer_id, product_code, quantity
From crosstabFunc) as cf
Pivot (Sum(quantity) For product_code In (A,B,C))
as cf1
Order By customer_id
PostgreSQL
次のコードを使用して、PostgreSQL の拡張機能を作成します。
Create Extension tablefunc;
Select * From Crosstab
(' Select customer_id, product_code, quantity
From crosstabFunc' )
as T ( customer_id Int, "A" Int, "B" Int, "C" Int)
配列へのピボット解除
PostgreSQLには Unpivot 関数がありません。SQL Server または Oracle から PostgreSQL に変換する場合、ピボット解除は配列にマッピングされます。例については、次のコードをご参照ください。
Create Table Students
(
Id Int Primary Key Identity,
Student_Name Varchar (50),
Math_marks Int,
English_marks Int,
History_marks Int,
Science_marks Int
)
Go
Insert Into Students Values ('Sally', 87, 56, 78, 91 )
Insert Into Students Values ('Edward', 69, 80, 92, 98)
Oracle
次のサンプルコードを使用して、Oracle のピボット解除機能を実装します。
Select StudentName, course,score
From Students
Unpivot (score For course In (Math_marks AS 'Maths', English_marks AS 'English', History_marks AS 'History', Science_marks As 'Science'));
SQL Server
次のサンプルコードを使用して、SQL Server のピボット解除機能を実装します。
Select Student_Name, Course, Score
From Students
Unpivot
(
Score
For Course in (Math_marks, English_marks, History_marks, Science_marks)
) AS SchoolUnpivot
PostgreSQL
次のサンプルコードを使用して、PostgreSQL のピボット解除機能を実装します。
Select Student_Name, course, score From
(
Select
Student_Name,
Unnest (Array[ 'Math', 'English','History', 'Science']
) As course,
Unnest (Array[ Math_marks, English_marks,History_marks,Science_marks]
) As score
From StudentsP
) AS Unpvt
関数から複数の結果セットを返す
SQL Server が複数の行を持つ複数の結果セットは、簡単に返すことができます。以下のサンプルのようにカーソルを使用すれば、PostgreSQL と Oracle で同じように実行できます。
Oracle
次のコードを使用して、Oracle のプロシージャから複数の結果セットを返します。
Create Procedure Spgetdept23
(P_Cur Out Sys_Refcursor, P_Cur12 Out Sys_Refcursor)
Is
Begin
Open P_Cur For
Select * From employees;
Open P_Cur12 For
Select * From dept;
End;
var cur Refcursor
var cur2 Refcursor
Exec Spgetdept23(:cur,:cur2);
Print cur;
Print cur2;
SQL Server
次のコードを使用して、SQL Server のプロシージャから複数の結果セットを返します。SQL Server では追加のパラメーターは必要ありません。
Create Procedure Dbo.Multiple_Reseultset
As
Begin
Select * From HumanResources.Employee
Select * From HumanResources.Department
End
To execute the procedure in SQL Server, enter the following code.
Exec Dbo.Multiple_Reseultset
To execute the procedure in SQL Server, enter the following code.
Exec Dbo.Multiple_Reseultset
PostgreSQL
次のコードを使用して、PostgreSQL のプロシージャから複数の結果セットを返します。
Create Or Replace Function Multiple_Reseultset()
Returns Setof Refcursor As
$$
Declare
cur1 Refcursor;
cur2 Refcursor;
Begin
Open cur1 For
Select * From HumanResources.employee;
Return Next cur1;
Open cur2 For
Select * From HumanResources.Department;
Return Next cur2;
End
$$ Language 'plpgsql';
次のコードを入力して、PostgreSQL でプロシージャを実行します。
Begin
Select * From Public.Multiple_Reseultset( )
Fetch All In "<unnamed portal 1>"
Fetch All In "<unnamed portal 2>"
End
エイリアスを使用したインラインクエリ
PostgreSQL のセマンティクスでは、インラインビューを Subselect または Subquery として参照する場合があります。Oracle は、内部ステートメントのエイリアスの省略をサポートしています。PostgreSQL および SQL Serverでは、エイリアスの使用は必須です。次のコード例では、エイリアスとして B を使用しています。
Oracle
次のコードは、Oracle のインラインクエリのサンプルです。
Select a.col1, col2_fromSubquery -- you can specify the columns directly from the subquery with out any prefix of subquery unless have common columns names.
from emplyee a,
(select * from salary )
where active=true
SQL Server と PostgreSQL
Oracleで記述された同じインラインクエリのサンプルには、SQL Server と PostgreSQL のエイリアス名が必要です。
Select a.col1, b.col2_fromSubquery
from emplyee a,
(select * from salary ) b
where active=true
データの順序
Oracle または SQL Server から PostgreSQL にデータを移行した後、データの取得順序が変わっている場合があります。挿入の順序、または列とその値のデータ型、あるいは照合順序のいずれかが変わったことが理由の可能性があります。
データの正しい順序を取得するには、ビジネスニーズを特定し、クエリに Order by 句を適用してデータを一致させます。
dblink および外部データラッパー
dblink は、同種および異種データベース間で通信するために使用される機能です。この投稿が書かれている時点で、Amazon RDS と Aurora PostgreSQL は異種データベースでのサポートを行っていませんが、PostgreSQL データベース間での通信はサポートしています。
同種データベース間での通信
PostgreSQL は、データベース間通信のために、dblink および外部データラッパー (FDW) とのデータベース間通信をサポートしています。このセクションでは、dblink と FDW の使用方法について説明します。
外部データラッパーの使用
PostgreSQL は FDW をサポートしています。FDW を使用すると、外部ソースに保存されているデータにアクセスできます。Amazon RDS および Aurora PostgreSQL は、現在、PostgreSQL FDW のみをサポートしています。PostgreSQL FDWを設定するには、次の手順を実行します。
- 次のコードで拡張機能を作成します。
Create Extension postgres_fdw;
- サーバーを作成し、次のコードを使用して外部データベースにリンクします。
Create Server server_name1 Foreign Data Wrapper
postgres_fdw Options (host abcd.rds.amazonaws.com' dbname abcd, port '5432');
- 次のコードを使って、外部データベースからテーブルにアクセスするユーザーマッピングを作成します。
Create User Mapping For Current_User
Server server_name1
Options (user 'pgar1234', password 'pgar1234');
FDW 経由で通信したいすべてのユーザーのユーザーマッピングを作成します。
- すべての外部テーブルをローカルスキーマにインポートし、通常のテーブルにアクセスするのと同じように外部テーブルからデータにアクセスします。以下は、外部データベースとスキーマからテーブルをインポートするサンプルコードです。
Create Schema imported_public2 -- created local schema
Import Foreign Schema public From Server server_name1
Into imported_public2; -- This will import all the tables
Select * From imported_public2.emptable
異種データベース間での通信
PostgreSQL はデータベース間の通信をサポートしていません。異種データベース間の通信を行うには、Amazon Aurora PostgreSQL には制限がありますが、ソース環境 (Oracle または SQL Server など) の dblink をターゲット (PostgreSQL) に実装できます。データをプルまたはプッシュすることも可能です。
詳細については、「Cross-Database Querying in Compose PostgreSQL.」をご参照ください。
dblink を使用して外部データベーステーブルのビューを作成する
dblink は PostgreSQL contrib 拡張機能で、他のデータベースで短いアドホッククエリを実行できます。dblink オプションを使用する場合、ユーザーはパスワードをクリアテキストで提供および保存する必要があります。このパスワードはユーザーに表示されます。他に選択肢がない場合を除き、このオプションはお勧めしません。
詳細については、「外部データラッパー」および「postgres_fdw のドキュメント」をご参照ください。
オプション 1: SQL ステートメント自体にターゲットデータベースアクセスの詳細を指定する
このオプションでは、ホストまたは接続の詳細の変更など、複数の場所を変更する必要があるたびに、ホスト接続とデータベースの認証情報を提供する必要があります。
Create Or Replace View emptable_dblink As
Select emptable.empid, emptable.last_name , emptable.first_name
From Dblink('host=abcd.rds.amazonaws.com user=abcd
password=abcd dbname=abcd port=5432',
Select empid,last_name,first_name FROM emptable')
AS emptable(empid Int,last_name Varchar , first_name Text );
Select * From emptable_dblink;
オプション 2: アクセスの詳細を分離し、接続オブジェクトを使用する
このオプションでは、ホストと接続の詳細が 1 か所で定義され、接続名を使用してデータベース間接続が行われます。
Select Dblink_Connect('conName','dbname=abcd user=abcd
password=abcd host= abcd.rds.amazonaws.com ');
Create Or Replace View mytabview1 As
Select mytable.* From
Dblink('conName', Select empid,last_name,first_name FROM emptable')
As mytable(empid Int,last_name Varchar , first_name Text);
Select * From mytabview1;
dblink を使用した関数呼び出し
次のコードは、整数を返す外部 PostgreSQL データベースの関数です。
Select * From
Dblink('host=abcd.rds.amazonaws.com user=abcd
password=abcd dbname=postgres port=5432',
'Select public.add_ten(10)')
As add_ten(a Int);
次のコードは、テーブル型を返す外部 PostgreSQL データベースの関数です。
Select Dblink_Connect('conName','dbname=pgar1234 user=pgar1234
password=pgar1234 host=pgar1234.ctegx79rcs0q.ap-south-1.rds.amazonaws.com');
Select Dblink_Open('conName','foo2',
'Select * From public.tabletypetest(10)');
Select * From Dblink_Fetch('conName','foo2', 5) As (empid Int, last_name Varchar);
一連の数値の最大値と最小値を見つける
PostgreSQL への移行時には、最大値と最小値が必要となる場合があります。PostgreSQL には、次のコードで示すように、これらの値を見つけるための関数が含まれています。
Select Greatest(1,2,3,50,100)
-> 100
Select Least(1,2,3,50,100)
-> 1
更新のための自己結合を検討する
select ステートメントの from 句で同じソーステーブル (更新するテーブル) を使用している場合、PostgreSQL での更新の動作は SQL Server とは違ったものになります。SQL Server とは異なり、PostgreSQL では from 句の 2 番目の参照が最初の参照から独立しています。また、変更はテーブル全体に適用されます。
次のコード例では、部門 1 の従業員の給与を更新しています。
Update employee
Set salary = employee.salary + employee.salary * 0.10
From Employee e
Join dept d on d.deptid = e.deptid
Where d.deptid=1
この関数は SQL Server でも同じように機能しますが、移行すると、同じ SQL ステートメントは単一の部門ではなくテーブル全体を更新します。PostgreSQL の動作は異なります。SQL Server と違って、2 つの employee テーブルは互いに独立していると想定しています。
単一の部門を更新するには、DML を次のコードに変換します。
Update Employee e
Set salary = e.salary + e.salary * 0.10
From dept d
Where d.deptid = e.deptid
And d.deptid=1
Oracle を使用している場合、DML を次のコードに変換します。
Update Employee e
Set Salary = e.salary + e.salary * 0.10
Where Exists (Select 1 from dept d where d.deptid = e.deptid
And d.deptid=1 )
まとめ
この投稿では、商用データベースから PostgreSQL への移行に取り組む開発者向けのヒントとベストプラクティスを共有しました。移行中に決定しなければならないこと、それらがデータベースのパフォーマンスに与える影響について説明しています。移行中にこうしたパフォーマンスの側面に留意しておくと、後の移行中に起こるパフォーマンスに関する問題を防ぐことができます。
この投稿についてご質問やご意見があれば、コメントセクションでお聞かせください。
著者について
Viswanatha Shastry Medipalli はインドの AWS プロフェッショナルサービスチームのコンサルタントです。SQL データベースの移行に関する幅広い専門知識と経験があります。数多くの成功したデータベースソリューションを設計および設計し、難しいビジネス要件に対応してきました。レポOracle、SQL Server、PostgreSQL を使ったソリューションで、レポート、ビジネスインテリジェンス、アプリケーション、開発サポートを実現しています。自動化やオーケストレーションの知識も豊富です。オンプレミスデータベースの Amazon RDS と Aurora PostgreSQL への同種および異種の移行に、重点的に取り組んでいます。。