将基于函数的索引从甲骨文迁移到 Postgre SQL - AWS Prescriptive Guidance
Summary先决条件和限制架构工具操作说明相关资源其他信息

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

将基于函数的索引从甲骨文迁移到 Postgre SQL

由 Veeranjaneyulu Grandhi () 和 Navakanth Talluri () AWS 创作 AWS

环境：生产

源：Oracle

目标：Postgre SQL

R 类型：重构

工作负载：Oracle

技术：迁移；数据库

Summary

索引是增强数据库性能的常用方法。索引允许数据库服务器比无索引时更快地查找和检索特定行。但索引也会增加整个数据库系统的开销，因此应该明智地使用它们。基于函数的索引基于函数或表达式，可以涉及多个列和数学表达式。基于函数的索引可提高使用索引表达式的查询的性能。 

从本质上讲，Postgre SQL 不支持使用波动率定义为稳定的函数创建基于函数的索引。但是，您可创建波动性为 IMMUTABLE 的类似函数，并在创建指数时使用它们。

IMMUTABLE 函数无法修改数据库，并且在给定相同参数的情况下，可以保证永远返回相同的结果。当查询使用常量参数调用函数时，此类别允许优化程序预先对函数求值。 

这种模式有助于将基于 Oracle 函数的索引与to_char、to_date和to_number等效函数一起使用时迁移到 Postg SQL re 等效函数。

先决条件和限制

先决条件

  • 有效的亚马逊 Web Services (AWS) 账户

  • 已设置并运行侦听器服务的源 Oracle 数据库实例

  • 熟悉 Post SQL gre 数据库

限制

  • 数据库大小限制为 64 TB。

  • 创建索引时使用的函数必须是IMMUTABLE。

产品版本

  • 版本 11g（版本 11.2.0.3.v1 及更高版本）以及最高 12.2 和 18c 的所有 Oracle 数据库版本

  • Postgre SQL 版本 9.6 及更高版本

架构

源技术堆栈

  • 本地或亚马逊弹性计算云 (AmazonEC2) 实例上的 Oracle 数据库，或者RDS适用于 Oracle 的亚马逊数据库实例

目标技术堆栈

  • 任何 Postgre引擎 SQL

工具

  • pgAdmin 4 是一款适用于 Postgres 的开源管理工具。 pgAdmin 4 工具提供了一个用于创建、维护和使用数据库对象的图形界面。

  • Oracle SQL 开发人员是一个集成开发环境 (IDE)，用于在传统部署和云部署中开发和管理 Oracle 数据库。

操作说明

任务描述所需技能
使用 to_char 函数在列上创建基于函数的索引。

使用以下代码创建基于函数的索引。

postgres=# create table funcindex( col1 timestamp without time zone);
CREATE TABLE
postgres=# insert into funcindex values (now());
INSERT 0 1
postgres=# select * from funcindex;
            col1
----------------------------
 2022-08-09 16:00:57.77414
(1 rows)
 
postgres=# create index funcindex_idx on funcindex(to_char(col1,'DD-MM-YYYY HH24:MI:SS'));
ERROR:  functions in index expression must be marked IMMUTABLE

 

注意：Postgre SQL 不允许在没有子句的情况下创建基于函数的索引。IMMUTABLE

DBA，应用程序开发者
检查函数的波动性。

要检查函数的波动性，请使用其他信息部分中的代码。 

DBA
任务描述所需技能
创建包装函数。

要创建包装函数，请使用其他信息部分中的代码。

Postgre开发者 SQL
使用包装函数创建索引。

使用其他信息部分中的代码创建用户定义的函数，其关键字 IMMUTABLE 与应用程序处于相同的架构中，并在索引创建脚本中引用该函数。

如果用户定义的函数是在通用架构中创建的(来自前面的示例)，请按所示更新 search_path。

ALTER ROLE <ROLENAME> set search_path=$user, COMMON;
DBA，Postgre 开发者 SQL
任务描述所需技能
验证索引创建。

根据查询访问模式验证是否需要创建索引。

DBA
验证索引是否可以使用。

要检查基于函数的索引是否由 Postgre SQL Optimizer 获取，请使用解释或解释分析运行SQL语句。使用其他信息部分中的代码。如有可能，还要收集表格统计信息。

注意：如果您注意到解释计划，Postgre SQL 优化器会因为谓词条件而选择了基于函数的索引。

DBA

相关资源

其他信息

创建包装函数

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)