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

PDF

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

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, PostgreSQL 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 vers to_number l'équivalent de PostgreSQL.

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 PostgreSQL

Limites

• La limite de taille de la base de données est de 64 To.

• Les fonctions utilisées dans la création d'index doivent être IMMUABLES.

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

• PostgreSQL 9.6 et versions ultérieures

Architecture

Pile technologique source

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

Pile technologique cible

• N'importe quel moteur PostgreSQL

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

Création d'un index basé sur une fonction à l'aide d'une fonction par défaut

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   Note PostgreSQL n'autorise pas la création d'un index basé sur une fonction sans cette 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

Création d'un index basé sur une fonction à l'aide d'une fonction par défaut

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   Note PostgreSQL n'autorise pas la création d'un index basé sur une fonction sans cette 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

Créez des index basés sur les fonctions à l'aide d'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 PostgreSQL
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 PostgreSQL

Créez des index basés sur les fonctions à l'aide d'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 PostgreSQL
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 PostgreSQL

Valider la création d'index

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 PostgreSQL Optimizer, exécutez une instruction SQL en utilisant explain ou explain analyze. Utilisez le code dans la section Informations supplémentaires. Si possible, rassemblez également les statistiques du tableau. Note Si vous remarquez le plan d'explication, cela signifie que l'optimiseur PostgreSQL a choisi un index basé sur les fonctions en raison de la condition du prédicat.	DBA

Valider la création d'index

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 PostgreSQL Optimizer, exécutez une instruction SQL en utilisant explain ou explain analyze. Utilisez le code dans la section Informations supplémentaires. Si possible, rassemblez également les statistiques du tableau. Note Si vous remarquez le plan d'explication, cela signifie que l'optimiseur PostgreSQL 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)

• Volatilité de PostgreSQL (documentation PostgreSQL)

• chemin de recherche PostgreSQL (documentation de PostgreSQL)

• Manuel de migration d'Oracle Database 19c vers Amazon Aurora PostgreSQL 

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)