Récapitulatif Conditions préalables et limitations Architecture Outils Épopées Ressources connexes Informations supplémentaires

Migrer les index basés sur les fonctions d'Oracle vers Postgre SQL

Créé par Veeranjaneyulu Grandhi () et Navakanth Talluri () AWS AWS

Environnement : Production	Source : Oracle	Cible : Poster SQL
Type R : Ré-architecte	Charge de travail : Oracle	Technologies : migration ; bases de données

Récapitulatif

Les index constituent un moyen courant d'améliorer les performances des bases de données. Un index permet au serveur de base de données de rechercher et de récupérer des lignes spécifiques bien plus rapidement qu'il ne le pourrait sans index. Mais les index alourdissent également le système de base de données dans son ensemble, ils doivent donc être utilisés judicieusement. Les index basés sur une fonction, qui sont basés sur une fonction ou une expression, peuvent comporter plusieurs colonnes et expressions mathématiques. Un index basé sur une fonction améliore les performances des requêtes qui utilisent l'expression d'index.

En mode natif, Postgre SQL ne prend pas en charge la création d'index basés sur des fonctions à l'aide de fonctions dont la volatilité est définie comme stable. Cependant, vous pouvez créer des fonctions de volatilité similaires IMMUTABLE et les utiliser dans la création d'index.

Une IMMUTABLE fonction ne peut pas modifier la base de données et il est garanti qu'elle renverra toujours les mêmes résultats avec les mêmes arguments. Cette catégorie permet à l'optimiseur de préévaluer la fonction lorsqu'une requête l'appelle avec des arguments constants.

Ce modèle facilite la migration des index basés sur les fonctions Oracle lorsqu'ils sont utilisés avec des fonctions telles que to_charto_date, et to_number vers l'équivalent Postgre. SQL

Conditions préalables et limitations

Prérequis

Un compte Amazon Web Services (AWS) actif
Une instance de base de données Oracle source avec le service d'écoute configuré et en cours d'exécution
Connaissance des bases de données Postgre SQL

Limites

La limite de taille de la base de données est de 64 To.
Les fonctions utilisées lors de la création d'index doivent êtreIMMUTABLE.

Versions du produit

Toutes les éditions de base de données Oracle pour les versions 11g (versions 11.2.0.3.v1 et ultérieures), 12.2 et 18c
SQLVersions 9.6 et ultérieures de Postgre

Architecture

Pile technologique source

Une base de données Oracle sur site ou sur une instance Amazon Elastic Compute Cloud (AmazonEC2), ou une instance de base de données Amazon RDS pour Oracle

Pile technologique cible

N'importe quel moteur Postgre SQL

Outils

pgAdmin 4 est un outil de gestion open source pour Postgres. L'outil pgAdmin 4 fournit une interface graphique pour créer, gérer et utiliser des objets de base de données.
Oracle SQL Developer est un environnement de développement intégré (IDE) permettant de développer et de gérer Oracle Database dans le cadre de déploiements traditionnels et dans le cloud.

Épopées

Tâche Description Compétences requises

Créez un index basé sur une fonction sur une colonne à l'aide de la fonction to_char.

Tâche	Description	Compétences requises
Créez un index basé sur une fonction sur une colonne à l'aide de la fonction to_char.	Utilisez le code suivant pour créer l'index basé sur les fonctions. `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` Remarque : Postgre SQL n'autorise pas la création d'un index basé sur une fonction sans la clause. `IMMUTABLE`	DBA, Développeur d'applications
Vérifiez la volatilité de la fonction.	Pour vérifier la volatilité de la fonction, utilisez le code de la section Informations supplémentaires.	DBA

Utilisez le code suivant pour créer l'index basé sur les fonctions.


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

Remarque : Postgre SQL n'autorise pas la création d'un index basé sur une fonction sans la clause. IMMUTABLE

DBA, Développeur d'applications

Vérifiez la volatilité de la fonction.

Pour vérifier la volatilité de la fonction, utilisez le code de la section Informations supplémentaires.

DBA

Tâche Description Compétences requises

Créez une fonction wrapper.

Tâche	Description	Compétences requises
Créez une fonction wrapper.	Pour créer une fonction wrapper, utilisez le code de la section Informations supplémentaires.	Développeur Postgre SQL
Créez un index à l'aide de la fonction wrapper.	Utilisez le code de la section Informations supplémentaires pour créer une fonction définie par l'utilisateur avec le mot-clé `IMMUTABLE` dans le même schéma que l'application, et faites-y référence dans le script de création d'index. Si une fonction définie par l'utilisateur est créée dans un schéma commun (dans l'exemple précédent), mettez-la à jour `search_path` comme indiqué. `ALTER ROLE <ROLENAME> set search_path=$user, COMMON;`	DBA, développeur de Postgrex SQL

Pour créer une fonction wrapper, utilisez le code de la section Informations supplémentaires.

Développeur Postgre SQL

Créez un index à l'aide de la fonction wrapper.

Utilisez le code de la section Informations supplémentaires pour créer une fonction définie par l'utilisateur avec le mot-clé IMMUTABLE dans le même schéma que l'application, et faites-y référence dans le script de création d'index.

Si une fonction définie par l'utilisateur est créée dans un schéma commun (dans l'exemple précédent), mettez-la à jour search_path comme indiqué.


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

DBA, développeur de Postgrex SQL

Tâche	Description	Compétences requises
Validez la création de l'index.	Vérifiez que l'index doit être créé, en fonction des modèles d'accès aux requêtes.	DBA
Vérifiez que l'index peut être utilisé.	Pour vérifier si l'index basé sur les fonctions est détecté par le Postgre SQL Optimizer, exécutez une SQL instruction en utilisant explain ou explain analyze. Utilisez le code dans la section Informations supplémentaires. Si possible, rassemblez également les statistiques du tableau. Remarque : Si vous remarquez le plan d'explication, l'SQLoptimiseur Postgre a choisi un index basé sur les fonctions en raison de la condition du prédicat.	DBA

Tâche

Description

Compétences requises

Validez la création de l'index.

Vérifiez que l'index doit être créé, en fonction des modèles d'accès aux requêtes.

DBA

Vérifiez que l'index peut être utilisé.

Pour vérifier si l'index basé sur les fonctions est détecté par le Postgre SQL Optimizer, exécutez une SQL instruction en utilisant explain ou explain analyze. Utilisez le code dans la section Informations supplémentaires. Si possible, rassemblez également les statistiques du tableau.

Remarque : Si vous remarquez le plan d'explication, l'SQLoptimiseur Postgre a choisi un index basé sur les fonctions en raison de la condition du prédicat.

DBA

Ressources connexes

Index basés sur les fonctions (documentation Oracle)
Index des expressions (documentation PostgreSQL)
SQLVolatilité de Postgre (documentation PostgreSQL)
SQLPostgre_path (documentation Postgre) SQL
Manuel de migration d'Oracle Database 19c vers Amazon Aurora Postgrer SQL

Informations supplémentaires

Création d'une fonction wrapper


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;

Créez un index à l'aide de la fonction wrapper


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

Vérifiez la volatilité de la fonction


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;

Validez que l'index peut être utilisé


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)

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Migrer les tables externes Oracle vers Amazon Aurora

Migrer les fonctions natives d'Oracle vers Postgre SQL