翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。

# 関数ベースのインデックスを Oracle から PostgreSQL に移行する
<a name="migrate-function-based-indexes-from-oracle-to-postgresql"></a>

*Amazon Web Services、Veeranjaneyulu Grandhi、Navakanth Talluri*

## 概要
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-summary"></a>

インデックスはデータベースのパフォーマンスを向上させる一般的な方法です。インデックスを使用すると、データベースサーバーはインデックスがない場合よりもずっと速く特定の行を検索して取得できます。ただし、インデックスはデータベースシステム全体にオーバーヘッドも発生させるため、適切に使用する必要があります。関数または式に基づく関数ベースのインデックスには、複数の列や数式が含まれる場合があります。関数ベースのインデックスは、インデックス式を使用するクエリのパフォーマンスを向上させます。 

PostgreSQL はもともと、安定と定義されているボラティリティを持つ関数を使った関数ベースのインデックスの作成をサポートしていません。ただし、`IMMUTABLE` としてのボラティリティを持つ関数を作成して、インデックスの作成に使用することはできます。

`IMMUTABLE` 関数はデータベースを変更することはできず、同じ引数が与えられてもずっと同じ結果を返すことが保証されています。このカテゴリにより、クエリが定数引数で関数を呼び出したときに、オプティマイザーは関数を事前評価できます。 

このパターンは、`to_char`、`to_date`、`to_number`などの関数と一緒に使用すると、Oracle の関数ベースのインデックスを同等の PostgreSQL に移行するのに役立ちます。

## 前提条件と制限事項
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-prereqs"></a>

**前提条件**
+ アクティブな Amazon Web Services (AWS) アカウント
+ リスナーサービスがセットアップされて実行されているソース Oracle データベースインスタンス
+ PostgreSQL データベースに関する知識

**制限事項**
+ データベースのサイズ制限は 64 TB です。
+ インデックス作成に使用される関数は IMMUTABLE でなければなりません。

**製品バージョン**
+ バージョン 11g (バージョン 11.2.0.3.v1 以降) および、12.2 および 18c までのすべての Oracle データベースエディション
+ PostgreSQL バージョン 9.6 以降

## アーキテクチャ
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-architecture"></a>

**ソーステクノロジースタック**
+ オンプレミスまたは Amazon Elastic Compute Cloud (Amazon EC2) インスタンス上の Oracle データベース、または Amazon RDS for Oracle DB インスタンス

**ターゲットテクノロジースタック**
+ PostgreSQL エンジンのすべて

## ツール
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-tools"></a>
+ **pgAdmin 4** は Postgres 向けのオープンソース管理ツールです。pgAdmin 4 ツールは、データベースオブジェクトを作成、管理、使用するためのグラフィカルインターフェイスを提供します。
+ **Oracle SQL Developer** は、従来のデプロイとクラウドデプロイの両方で Oracle データベースを開発および管理するための統合開発環境 (IDE) です。

## エピック
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-epics"></a>

### 規定の関数を使用して関数ベースのインデックスを作成する
<a name="create-a-function-based-index-using-a-default-function"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| to\$1char 関数を使用して列に関数ベースのインデックスを作成する。 | 次のコードを使用して、関数を作成します。<pre>postgres=# create table funcindex( col1 timestamp without time zone);<br />CREATE TABLE<br />postgres=# insert into funcindex values (now());<br />INSERT 0 1<br />postgres=# select * from funcindex;<br />            col1<br />----------------------------<br /> 2022-08-09 16:00:57.77414<br />(1 rows)<br /> <br />postgres=# create index funcindex_idx on funcindex(to_char(col1,'DD-MM-YYYY HH24:MI:SS'));<br />ERROR:  functions in index expression must be marked IMMUTABLE</pre> PostgreSQL では、`IMMUTABLE` 句なしで関数ベースのインデックスを作成することはできません。 | DBA、アプリ開発者 | 
| 関数のボラティリティをチェックします。 | 関数のボラティリティをチェックするには、「*追加情報*」セクションのコードを使用してください。   | DBA | 

### ラッパー関数を使用して関数ベースのインデックスを作成する
<a name="create-function-based-indexes-using-a-wrapper-function"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| ラッパー関数を作成する。 | ラッパー関数を作成するには、「*追加情報*」セクションのコードを使用してください。 | PostgreSQL 開発者 | 
| ラッパー関数を使用してインデックスを作成します。 | 「*追加情報*」セクションのコードを使用して、アプリケーションと同じスキーマにある `IMMUTABLE` キーワードを含むユーザー定義関数を作成し、索引作成スクリプトで参照します。ユーザー定義の関数が (前の例の) 共通のスキーマで作成されている場合は、`search_path` を次のように更新します。<pre>ALTER ROLE <ROLENAME> set search_path=$user, COMMON;</pre> | DBA、PostgreSQL 開発者 | 

### インデックス作成の検証
<a name="validate-index-creation"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| インデックス作成についての検証を行います。 | クエリのアクセスパターンに基づき、インデックスの作成が必要であることを検証します。 | DBA | 
| インデックスが使用可能であることを検証します。 | PostgreSQL オプティマイザーが関数ベースのインデックスを取得しているか確認するには、「explain」または「explain analyze」で SQL ステートメントを実行します。*「追加情報」セクションのコードを使用してください。*可能であれば、テーブル統計情報も収集します。explain の計画を見ると、PostgreSQL オプティマイザーが述語条件に基づいて関数ベースのインデックスを選択していることがわかります。 | DBA | 

## 関連リソース
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-resources"></a>
+ [関数ベースのインデックス](https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_indexes.htm#ADFNS00505) (Oracle ドキュメント)
+ [式のインデックス](https://www.postgresql.org/docs/9.4/indexes-expressional.html) (PostgreSQL ドキュメント)
+ [PostgreSQL のボラティリティ](https://www.postgresql.org/docs/current/xfunc-volatility.html) (PostgreSQL ドキュメント)
+ [PostgreSQL search\$1path](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) (PostgreSQL ドキュメント)
+ [Oracle Database 19c から Amazon Aurora PostgreSQL への移行プレイブック](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html) 

## 追加情報
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-additional"></a>

**ラッパー関数を作成する**

```
CREATE OR REPLACE FUNCTION myschema.to_char(var1 timestamp without time zone, var2 varchar) RETURNS varchar AS $BODY$ select to_char(var1, 'YYYYMMDD'); $BODY$ LANGUAGE sql IMMUTABLE;
```

**ラッパー関数を使用してインデックスを作成する**

```
postgres=# create function common.to_char(var1 timestamp without time zone, var2 varchar) RETURNS varchar AS $BODY$ select to_char(var1, 'YYYYMMDD'); $BODY$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
postgres=# create index funcindex_idx on funcindex(common.to_char(col1,'DD-MM-YYYY HH24:MI:SS'));
CREATE INDEX
```

**関数のボラティリティをチェックする**

```
SELECT DISTINCT p.proname as "Name",p.provolatile as "volatility" FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
 WHERE n.nspname OPERATOR(pg_catalog.~) '^(pg_catalog)$' COLLATE pg_catalog.default AND p.proname='to_char'GROUP BY p.proname,p.provolatile
ORDER BY 1;
```

**インデックスが使用可能であることを検証する**

```
explain analyze <SQL>
 
 
postgres=# explain select col1 from funcindex where common.to_char(col1,'DD-MM-YYYY HH24:MI:SS') = '09-08-2022 16:00:57';
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Scan using funcindex_idx on funcindex  (cost=0.42..8.44 rows=1 width=8)
   Index Cond: ((common.to_char(col1, 'DD-MM-YYYY HH24:MI:SS'::character varying))::text = '09-08-2022 16:00:57'::text)
(2 rows)
```