Summary Prerequisites and limitations Architecture Tools Epics Related resources Additional information

Migrate function-based indexes from Oracle to PostgreSQL

Created by Veeranjaneyulu Grandhi (AWS) and Navakanth Talluri (AWS)

Environment: Production	Source: Oracle	Target: PostgreSQL
R Type: Re-architect	Workload: Oracle	Technologies: Migration; Databases

Summary

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly. Function-based indexes, which are based on a function or expression, can involve multiple columns and mathematical expressions. A function-based index improves the performance of queries that use the index expression.

Natively, PostgreSQL doesn't support creating function-based indexes using functions that have volatility defined as stable. However, you can create similar functions with volatility as IMMUTABLE and use them in index creation.

An IMMUTABLE function cannot modify the database, and it’s guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments.

This pattern helps in migrating the Oracle function-based indexes when used with functions such as to_char, to_date, and to_number to the PostgreSQL equivalent.

Prerequisites and limitations

Prerequisites

An active Amazon Web Services (AWS) account
A source Oracle database instance with the listener service set up and running
Familiarity with PostgreSQL databases

Limitations

Database size limit is 64 TB.
Functions used in index creation must be IMMUTABLE.

Product versions

All Oracle database editions for versions 11g (versions 11.2.0.3.v1 and later) and up to 12.2, and 18c
PostgreSQL versions 9.6 and later

Architecture

Source technology stack

An Oracle database on premises or on an Amazon Elastic Compute Cloud (Amazon EC2) instance, or an Amazon RDS for Oracle DB instance

Target technology stack

Any PostgreSQL engine

Tools

pgAdmin 4 is an open source management tool for Postgres. The pgAdmin 4 tool provides a graphical interface for creating, maintaining, and using database objects.
Oracle SQL Developer is an integrated development environment (IDE) for developing and managing Oracle Database in both traditional and cloud deployments.

Epics

Task Description Skills required

Create a function-based index on a column using the to_char function.

Task	Description	Skills required
Create a function-based index on a column using the to_char function.	Use the following code to create the function-based index. `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` Note: PostgreSQL doesn’t allow creating a function-based index without the `IMMUTABLE` clause.	DBA, App developer
Check the volatility of the function.	To check the function volatility, use the code in the Additional information section.	DBA

Use the following code to create the function-based index.


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

Note: PostgreSQL doesn’t allow creating a function-based index without the IMMUTABLE clause.

DBA, App developer

Check the volatility of the function.

To check the function volatility, use the code in the Additional information section.

DBA

Task Description Skills required

Create a wrapper function.

Task	Description	Skills required
Create a wrapper function.	To create a wrapper function, use the code in the Additional information section.	PostgreSQL developer
Create an index by using the wrapper function.	Use the code in the Additional information section to create a user-defined function with the keyword `IMMUTABLE` in the same schema as the application, and refer to it in the index-creation script. If a user-defined function is created in a common schema (from the previous example), update the `search_path` as shown. `ALTER ROLE <ROLENAME> set search_path=$user, COMMON;`	DBA, PostgreSQL developer

To create a wrapper function, use the code in the Additional information section.

PostgreSQL developer

Create an index by using the wrapper function.

Use the code in the Additional information section to create a user-defined function with the keyword IMMUTABLE in the same schema as the application, and refer to it in the index-creation script.

If a user-defined function is created in a common schema (from the previous example), update the search_path as shown.


ALTER ROLE <ROLENAME> set search_path=$user, COMMON;

DBA, PostgreSQL developer

Task	Description	Skills required
Validate index creation.	Validate that the index needs to be created, based on query access patterns.	DBA
Validate that the index can be used.	To check whether the function-based index is picked up by the PostgreSQL Optimizer, run an SQL statement using explain or explain analyze. Use the code in the Additional information section. If possible, gather the table statistics as well. Note: If you notice the explain plan, PostgreSQL optimizer has chosen a function-based index because of the predicate condition.	DBA

Task

Description

Skills required

Validate index creation.

Validate that the index needs to be created, based on query access patterns.

DBA

Validate that the index can be used.

To check whether the function-based index is picked up by the PostgreSQL Optimizer, run an SQL statement using explain or explain analyze. Use the code in the Additional information section. If possible, gather the table statistics as well.

Note: If you notice the explain plan, PostgreSQL optimizer has chosen a function-based index because of the predicate condition.

DBA

Related resources

Function-based indexes (Oracle documentation)
Indexes on Expressions (PostgreSQL documentation)
PostgreSQL volatility (PostgreSQL documentation)
PostgreSQL search_path (PostgreSQL documentation)
Oracle Database 19c to Amazon Aurora PostgreSQL Migration Playbook

Additional information

Create a wrapper function


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;

Create an index by using the wrapper function


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

Check the volatility of the function


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;

Validate that the index can be used


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)

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Migrate Oracle external tables to Amazon Aurora

Migrate Oracle native functions to PostgreSQL