本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# 將函數型索引從 Oracle 遷移至 PostgreSQL
<a name="migrate-function-based-indexes-from-oracle-to-postgresql"></a>

*Veeranjaneyulu Grandhi 和 Navakanth Talluri，Amazon Web Services*

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

索引是增強資料庫效能的常見方式。索引可讓資料庫伺服器比沒有索引時更快地尋找和擷取特定資料列。但索引也會為資料庫系統整體增加額外負荷，因此應該合理地使用它們。以函數為基礎的索引，以函數或表達式為基礎，可以涉及多個欄和數學表達式。以函數為基礎的索引可改善使用索引表達式的查詢效能。 

原本，PostgreSQL 不支援使用波動定義為穩定的函數建立以函數為基礎的索引。不過，您可以建立具有波動的類似函數，`IMMUTABLE`並在建立索引時使用它們。

`IMMUTABLE` 函數無法修改資料庫，並保證永遠傳回相同的結果與相同的引數。此類別可讓最佳化工具在查詢使用常數引數呼叫函數時預先評估函數。 

此模式有助於將 Oracle 函數型索引與 `to_char`、 `to_date`和 等函數搭配使用時遷移`to_number`至 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 版及更新版本

## Architecture
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-architecture"></a>

**來源技術堆疊**
+ 內部部署或 Amazon Elastic Compute Cloud (Amazon EC2) 執行個體或 Amazon RDS for Oracle 資料庫執行個體上的 Oracle 資料庫

**目標技術堆疊**
+ 任何 PostgreSQL 引擎

## 工具
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-tools"></a>
+ **pgAdmin 4** 是 Postgres 的開放原始碼管理工具。pgAdmin 4 工具提供圖形界面，用於建立、維護和使用資料庫物件。
+ **Oracle SQL Developer** 是整合式開發環境 (IDE)，用於在傳統和雲端部署中開發和管理 Oracle 資料庫。

## 史詩
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-epics"></a>

### 使用預設函數建立以函數為基礎的索引
<a name="create-a-function-based-index-using-a-default-function"></a>


| 任務 | Description | 所需的技能 | 
| --- | --- | --- | 
| 使用 to\_char 函數在資料欄上建立以函數為基礎的索引。 | 使用下列程式碼來建立以函數為基礎的索引。<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><br /> PostgreSQL 不允許在沒有 `IMMUTABLE`子句的情況下建立以函數為基礎的索引。 | DBA、App 開發人員 | 
| 檢查函數的波動。 | 若要檢查函數波動，請使用*其他資訊*區段中的程式碼。   | DBA | 

### 使用包裝函式建立以函式為基礎的索引
<a name="create-function-based-indexes-using-a-wrapper-function"></a>


| 任務 | Description | 所需的技能 | 
| --- | --- | --- | 
| 建立包裝函式。 | 若要建立包裝函式，請使用*其他資訊區段*中的程式碼。 | PostgreSQL 開發人員 | 
| 使用包裝函式建立索引。 | 使用*其他資訊*區段中的程式碼，在與應用程式相同的結構描述`IMMUTABLE`中建立具有 關鍵字的使用者定義函數，並在索引建立指令碼中參考它。<br />如果在一般結構描述中建立使用者定義的函數 （從先前的範例），請更新顯示的 `search_path`。<pre>ALTER ROLE <ROLENAME> set search_path=$user, COMMON;</pre> | DBA、PostgreSQL 開發人員 | 

### 驗證索引建立
<a name="validate-index-creation"></a>


| 任務 | Description | 所需的技能 | 
| --- | --- | --- | 
| 驗證索引建立。 | 驗證是否需要根據查詢存取模式建立索引。 | DBA | 
| 驗證索引是否可以使用。 | 若要檢查 PostgreSQL Optimizer 是否收取函數型索引，請使用 explain 或 explain analysis 執行 SQL 陳述式。使用*其他資訊*區段中的程式碼。如果可能，也請收集資料表統計資料。如果您注意到解釋計畫，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\_path](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) (PostgreSQL 文件）
+ [Oracle 資料庫 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)
```