Migración de índices basados en funciones de Oracle a PostgreSQL

PDF

Creado por Veeranjaneyulu Grandhi (AWS) y Navakanth Talluri (AWS)

Resumen

Los índices son una forma común de mejorar el rendimiento de las bases de datos. Un índice permite al servidor de bases de datos encontrar y recuperar filas específicas mucho más rápido de lo que lo haría sin un índice. Sin embargo, los índices también añaden una sobrecarga al sistema de bases de datos en su conjunto, por lo que deben utilizarse con sensatez. Los índices basados en funciones, que se basan en una función o expresión, pueden incluir varias columnas y expresiones matemáticas. Un índice basado en funciones mejora el rendimiento de las consultas que utilizan la expresión de índice. 

De forma nativa, PostgreSQL no admite la creación de índices basados en funciones mediante funciones cuya volatilidad se define como estable. Sin embargo, puede crear funciones similares con volatilidad IMMUTABLE y utilizarlas en la creación de índices.

Una función IMMUTABLE no puede modificar la base de datos y se garantiza que devolverá los mismos resultados con los mismos argumentos para siempre. Esta categoría permite al optimizador evaluar previamente la función cuando una consulta la llama con argumentos constantes. 

Este patrón ayuda a migrar los índices basados en funciones de Oracle cuando se utilizan con funciones como to_char, to_date y to_number al equivalente de PostgreSQL.

Requisitos previos y limitaciones

Requisitos previos 

• Una cuenta de Amazon Web Services (AWS) activa

• Una instancia de base de datos de Oracle de origen con el servicio de escucha configurado y en ejecución

• Familiaridad con bases de datos PostgreSQL

Limitaciones

• El límite de tamaño de la base de datos es de 64 TB

• Las funciones utilizadas en la creación de índices deben ser INMUTABLES.

Versiones de producto

• Todas las ediciones de bases de datos de Oracle para las versiones 11g (versiones 11.2.0.3.v1 y posteriores) y hasta 12.2, y 18c

• Versiones 9.6 y posteriores de PostgreSQL

Arquitectura

Pila de tecnología de origen

• Una base de datos Oracle local o en una instancia de Amazon Elastic Compute Cloud (Amazon EC2) o una instancia de base de datos Amazon RDS for Oracle

Pila de tecnología de destino

• Cualquier motor de PostgreSQL

Herramientas

• pgAdmin 4 es una herramienta de administración de código abierto para Postgres. La herramienta pgAdmin 4 proporciona una interfaz gráfica para crear, mantener y utilizar objetos de base de datos.

• Oracle SQL Developer es un entorno de desarrollo integrado (IDE) para desarrollar y gestionar bases de datos de Oracle tanto en implementaciones tradicionales como en la nube.

Epics

Cree un índice basado en funciones mediante una función predeterminada

Tarea	Descripción	Habilidades requeridas
Cree un índice basado en funciones en una columna mediante la función to_char.	Utilice el siguiente código para crear el índice basado en funciones. 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   nota PostgreSQL no permite crear un índice basado en funciones sin la cláusula. IMMUTABLE	Administrador de base de datos, desarrollador de aplicaciones
Compruebe la volatilidad de la función.	Para comprobar la volatilidad de la función, utilice el código de la sección Información adicional.	Administrador de base de datos

Cree un índice basado en funciones mediante una función predeterminada

Tarea	Descripción	Habilidades requeridas
Cree un índice basado en funciones en una columna mediante la función to_char.	Utilice el siguiente código para crear el índice basado en funciones. 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   nota PostgreSQL no permite crear un índice basado en funciones sin la cláusula. IMMUTABLE	Administrador de base de datos, desarrollador de aplicaciones
Compruebe la volatilidad de la función.	Para comprobar la volatilidad de la función, utilice el código de la sección Información adicional.	Administrador de base de datos

Crear índices basados en funciones utilizando una función envolvente

Tarea	Descripción	Habilidades requeridas
Cree una función de encapsulación.	Para crear una función de encapsulación, utilice el código de la sección de información adicional.	Desarrollador de PostgreSQL
Cree un índice mediante la función de encapsulación.	Utilice el código de la sección Información adicional para crear una función definida por el usuario con la palabra clave IMMUTABLE en el mismo esquema que la aplicación y consúltela en el script de creación de índices. Si se crea una función definida por el usuario en un esquema común (del ejemplo anterior), actualice search_path tal como se muestra. ALTER ROLE <ROLENAME> set search_path=$user, COMMON;	Administrador de base de datos, desarrollador de PostgreSQL

Crear índices basados en funciones utilizando una función envolvente

Tarea	Descripción	Habilidades requeridas
Cree una función de encapsulación.	Para crear una función de encapsulación, utilice el código de la sección de información adicional.	Desarrollador de PostgreSQL
Cree un índice mediante la función de encapsulación.	Utilice el código de la sección Información adicional para crear una función definida por el usuario con la palabra clave IMMUTABLE en el mismo esquema que la aplicación y consúltela en el script de creación de índices. Si se crea una función definida por el usuario en un esquema común (del ejemplo anterior), actualice search_path tal como se muestra. ALTER ROLE <ROLENAME> set search_path=$user, COMMON;	Administrador de base de datos, desarrollador de PostgreSQL

Validar creación del índice

Tarea	Descripción	Habilidades requeridas
Valide la creación del índice.	Valide que es necesario crear el índice en función de los patrones de acceso a las consultas.	Administrador de base de datos
Valide que se pueda utilizar el índice.	Para comprobar si el optimizador de PostgreSQL recoge el índice basado en funciones, ejecute una instrucción SQL mediante explain o explain analyze. Utilice el código de la sección Información adicional. Si es posible, recopile también las estadísticas de la tabla. nota Si observa el plan de explicación, el optimizador de PostgreSQL ha elegido un índice basado en funciones debido a la condición de predicado.	Administrador de base de datos

Validar creación del índice

Tarea	Descripción	Habilidades requeridas
Valide la creación del índice.	Valide que es necesario crear el índice en función de los patrones de acceso a las consultas.	Administrador de base de datos
Valide que se pueda utilizar el índice.	Para comprobar si el optimizador de PostgreSQL recoge el índice basado en funciones, ejecute una instrucción SQL mediante explain o explain analyze. Utilice el código de la sección Información adicional. Si es posible, recopile también las estadísticas de la tabla. nota Si observa el plan de explicación, el optimizador de PostgreSQL ha elegido un índice basado en funciones debido a la condición de predicado.	Administrador de base de datos

Recursos relacionados

• Índices basados en funciones (documentación de Oracle)

• Índices de expresiones (documentación de PostgreSQL)

• Volatilidad de PostgreSQL (documentación de PostgreSQL)

• PostgreSQL search_path (documentación de PostgreSQL)

• Manual de migración de Oracle Database 19c a Amazon Aurora PostgreSQL 

Información adicional

Crear una función de encapsulación

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;

Crear un índice mediante la función de encapsulación

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

Comprobar la volatilidad de la función

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;

Validar que se pueda utilizar el índice

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)