Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

# Migrer les index basés sur les fonctions d'Oracle vers PostgreSQL
<a name="migrate-function-based-indexes-from-oracle-to-postgresql"></a>

*Veeranjaneyulu Grandhi et Navakanth Talluri, Amazon Web Services*

## Résumé
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-summary"></a>

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

## Conditions préalables et limitations
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-prereqs"></a>

**Conditions préalables**
+ 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 lors de 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
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-architecture"></a>

**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
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-tools"></a>
+ **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
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-epics"></a>

### Création d'un index basé sur une fonction à l'aide d'une fonction par défaut
<a name="create-a-function-based-index-using-a-default-function"></a>


| Sous-tâche | Description | Compétences requises | 
| --- | --- | --- | 
| Créez un index basé sur une fonction sur une colonne à l'aide de la fonction to\$1char. | Utilisez le code suivant pour créer l'index basé sur les fonctions.<pre>postgres=# create table funcindex( col1 timestamp without time zone);<br />CREATE TABLE<br />postgres=# insert into funcindex values (now());<br />INSERT 0 1<br />postgres=# select * from funcindex;<br />            col1<br />----------------------------<br /> 2022-08-09 16:00:57.77414<br />(1 rows)<br /> <br />postgres=# create index funcindex_idx on funcindex(to_char(col1,'DD-MM-YYYY HH24:MI:SS'));<br />ERROR:  functions in index expression must be marked IMMUTABLE</pre> 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
<a name="create-function-based-indexes-using-a-wrapper-function"></a>


| Sous-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é.<pre>ALTER ROLE <ROLENAME> set search_path=$user, COMMON;</pre> | DBA, développeur PostgreSQL | 

### Valider la création d'index
<a name="validate-index-creation"></a>


| Sous-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.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
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-resources"></a>
+ [Index basés sur les fonctions (documentation Oracle](https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_indexes.htm#ADFNS00505))
+ [Index des expressions (documentation](https://www.postgresql.org/docs/9.4/indexes-expressional.html) PostgreSQL)
+ [Volatilité de PostgreSQL (documentation PostgreSQL](https://www.postgresql.org/docs/current/xfunc-volatility.html))
+ chemin de [recherche PostgreSQL (documentation de PostgreSQL)](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH)
+ [Manuel de migration d'Oracle Database 19c vers Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html) 

## Informations supplémentaires
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-additional"></a>

**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)
```