Resumen Requisitos previos y limitaciones Arquitectura Herramientas Epics Recursos relacionados Información adicional

Migre índices basados en funciones de Oracle a Postgre SQL

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

Entorno: producción	Origen: Oracle	Objetivo: Postgre SQL
Tipo R: renovar arquitectura	Carga de trabajo: Oracle	Tecnologías: Migración; bases de datos

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, Postgre SQL no admite la creación de índices basados en funciones que utilicen funciones cuya volatilidad se defina 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 comoto_char, y a su equivalente en Postgre. to_date to_number SQL

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 las bases de datos de Postgre SQL

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 serlo. IMMUTABLE

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
Postgre, SQL versiones 9.6 y posteriores

Arquitectura

Pila de tecnología de origen

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

Pila de tecnología de destino

Cualquier motor de Postgre SQL

Herramientas

pgAdmin 4 es una herramienta de gestión de código abierto para Postgres. La herramienta pgAdmin 4 proporciona una interfaz gráfica para crear, mantener y utilizar objetos de bases de datos.
Oracle SQL Developer es un entorno de desarrollo integrado (IDE) para desarrollar y gestionar Oracle Database tanto en despliegues tradicionales como en la nube.

Epics

Tarea Descripción Habilidades requeridas

Cree un índice basado en funciones en una columna mediante la función to_char.

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: Postgre SQL no permite crear un índice basado en funciones sin la cláusula. `IMMUTABLE`	DBA, 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.	DBA

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: Postgre SQL no permite crear un índice basado en funciones sin la cláusula. IMMUTABLE

DBA, 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.

DBA

Tarea Descripción Habilidades requeridas

Cree una función de encapsulación.

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 Postgreg SQL
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;`	DBA, desarrollador de Postgre SQL

Para crear una función de encapsulación, utilice el código de la sección de información adicional.

Desarrollador de Postgreg SQL

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;

DBA, desarrollador de Postgre SQL

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.	DBA
Valide que se pueda utilizar el índice.	Para comprobar si el SQL optimizador de Postgre recoge el índice basado en funciones, ejecute una SQL sentencia utilizando 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 SQL optimizador de Postgre ha elegido un índice basado en funciones debido a la condición de predicado.	DBA

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.

DBA

Valide que se pueda utilizar el índice.

Para comprobar si el SQL optimizador de Postgre recoge el índice basado en funciones, ejecute una SQL sentencia utilizando 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 SQL optimizador de Postgre ha elegido un índice basado en funciones debido a la condición de predicado.

DBA

Recursos relacionados

Índices basados en funciones (documentación de Oracle)
Índices de expresiones (documentación de Postgre) SQL
Volatilidad de Postgre (documentación de Postgre SQL) SQL
Postgre search_path (documentación de Postgre SQL) SQL
Manual de migración de Oracle Database 19c a Amazon Aurora Postgre SQL

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)

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Migración de tablas externas de Oracle a Amazon Aurora

Migre las funciones nativas de Oracle a Postgre SQL