本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

# 将基于函数的索引从 Oracle 迁移到 PostgreSQL
<a name="migrate-function-based-indexes-from-oracle-to-postgresql"></a>

*Veeranjaneyulu Grandhi 和 Navakanth Talluri，Amazon Web Services*

## Summary
<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 EC2) 实例上的 Oracle 数据库，或者适用于 Oracle 的 Amazon RDS 数据库实例

**目标技术堆栈**
+ 任何 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>


| Task | 说明 | 所需技能 | 
| --- | --- | --- | 
| 使用 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` 子句的情况下创建基于函数的索引。 | 数据库管理员，应用程序开发人员 | 
| 检查函数的波动性。 | 要检查函数的波动性，请使用*其他信息*部分中的代码。   | 数据库管理员 | 

### 使用包装函数创建基于函数索引
<a name="create-function-based-indexes-using-a-wrapper-function"></a>


| Task | 说明 | 所需技能 | 
| --- | --- | --- | 
| 创建包装函数。 | 要创建包装函数，请使用*其他信息*部分中的代码。 | PostgreSQL 开发人员 | 
| 使用包装函数创建索引。 | 使用*其他信息*部分中的代码创建用户定义的函数，其关键字 `IMMUTABLE` 与应用程序处于相同的架构中，并在索引创建脚本中引用该函数。<br />如果用户定义的函数是在通用架构中创建的(来自前面的示例)，请按所示更新 `search_path`。<pre>ALTER ROLE <ROLENAME> set search_path=$user, COMMON;</pre> | 数据库管理员、PostgreSQL 开发人员 | 

### 验证索引创建
<a name="validate-index-creation"></a>


| Task | 说明 | 所需技能 | 
| --- | --- | --- | 
| 验证索引创建。 | 根据查询访问模式验证是否需要创建索引。 | 数据库管理员 | 
| 验证索引是否可以使用。 | 要检查基于函数的索引是否由 PostgreSQL 优化器获取，请使用解释或解释分析运行 SQL 语句。使用*其他信息*部分中的代码。如有可能，还要收集表格统计信息。如果您注意到解释计划，PostgreSQL 优化器会因谓词条件而选择基于函数的索引。 | 数据库管理员 | 

## 相关的资源
<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)（PostgreS QL 文档）
+ [PostgreSQL 搜索路径](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)
```