Améliorer les performances des requêtes avec le cache de table externe

A propos du cache de table externe dans Autonomous Database

Un cache de table externe est une zone de stockage dans votre instance Autonomous Database qui stocke les données d'une table externe.

Les données externes ne sont pas gérées par la base de données. Toutefois, vous pouvez utiliser les tables externes pour interroger des données en dehors de la base. Les requêtes sur les tables externes ne seront pas aussi rapides que les requêtes sur les tables de base de données car chaque fois que vous accédez aux données, elles doivent être extraites des fichiers externes stockés dans la banque d'objets.

La fonctionnalité de cache de table externe vous permet de mettre en cache les données fréquemment consultées à partir de tables externes dans votre instance Autonomous Database, sans avoir à apporter de modifications aux applications qui accèdent aux données, ce qui permet d'accéder plus rapidement aux tables externes.

Voici quelques-uns des avantages de l'utilisation du cache de table externe :

Performances améliorées : les requêtes sont plusieurs fois plus rapides pour vos données externes fréquemment consultées, ce qui est idéal pour les tableaux de bord, les rapports et les outils d'analyse qui accèdent régulièrement aux mêmes données.
100 % transparent : le mécanisme de mise en cache est entièrement transparent. Les applications peuvent bénéficier d'une vitesse améliorée sans nécessiter de modification de leurs requêtes, tableaux de bord ou applications.
Réduction des coûts liés au cloud : dans une application multicloud, la mise en cache réduit la nécessité d'extraire des données externes répétées à partir du stockage distant, réduisant ainsi les frais de sortie de données associés à l'accès aux données entre les régions ou les clouds.

Vous pouvez créer un cache de table externe pour les tables partitionnées et non partitionnées créées sur les types de fichier suivants :

Parquet
ORC
AVRO
Tables d'iceberg

Pour plus d'informations, reportez-vous à Requête de données externes.

Prérequis

Répertorie les prérequis pour la création du cache de table externe.

Vous devez disposer du privilège EXECUTE sur le package DBMS_EXT_TABLE_CACHE. Notez que vous ne pouvez créer un cache de table externe que dans votre propre schéma et pour les tables externes que vous possédez.
Un quota d'espace approprié doit être alloué à votre schéma pour garantir une capacité de stockage suffisante pour les données de cache.
Vous devez disposer d'informations d'identification pour accéder aux fichiers de table externes stockés dans la banque d'objets. Vous n'avez pas besoin de créer d'informations d'identification si vous activez les informations d'identification de principal de ressource pour accéder à la banque d'objets Oracle Cloud Infrastructure.

Limites

Répertorie les remarques et restrictions importantes relatives à l'utilisation du cache de table externe dans une instance Autonomous Database.

Vous pouvez uniquement créer un cache de table externe dans votre propre schéma et pour les tables externes dont vous êtes propriétaire.
Un cache de table externe ne peut contenir que des fichiers provenant d'une seule table externe.
Vous ne pouvez pas accorder de privilèges sur un cache de table externe et vous pouvez uniquement accéder aux données du cache de table externe dont vous êtes propriétaire.
Lorsqu'un fichier est supprimé de la banque d'objets, les données correspondantes dans le cache sont marquées comme non valides et ne sont pas accessibles.

Créer un cache de table externe

Exécutez DBMS_EXT_TABLE_CACHE.CREATE_CACHE pour créer un cache de table externe.

Lorsque la mémoire cache est créée, elle est initialement vide et activée pour l'alimentation. La taille du cache augmente chaque fois qu'un fichier est ajouté, en fonction des limites de quota d'espace définies pour le schéma, jusqu'à ce qu'il atteigne les limites affectées. Pour plus d'informations, reportez-vous à Définition de préférences de dimensionnement facultatives pour le cache de table externe.

Utilisez DBMS_EXT_TABLE_CACHE.CREATE_CACHE pour créer un cache de table externe pour votre schéma. Par exemple :
```
BEGIN
    DBMS_EXT_TABLE_CACHE.CREATE_CACHE (      
      owner          => 'SALES',
      table_name     => 'STORE_SALES',
      partition_type => 'PATH');                                                                 
END;                                                                 
/
```
Cela crée un cache pour la table STORE_SALES dans le schéma SALES. STORE_SALES est une table externe pointant vers les données stockées dans la banque d'objets.

Le paramètre owner indique le nom du schéma. Cet exemple crée un cache de table externe pour l'utilisateur SALES.

Le paramètre partition_type indique la méthode de partitionnement à utiliser pour le cache de table externe. Cet exemple partitionne le cache à l'aide de la colonne FILE$PATH. FILE$PATH est une colonne invisible qui indique le texte du chemin du fichier jusqu'au début du nom de l'objet.
Pour plus d'informations, reportez-vous à Colonnes de métadonnées de table externe et à Procédure CREATE_CACHE.
Lorsque vous créez un cache de table externe pour la première fois, ses métadonnées sont stockées dans le dictionnaire de données. Toutefois, aucun espace n'est alloué aux données du cache. Vous pouvez interroger la vue USER_EXTERNAL_TAB_CACHES pour vérifier la création du cache.
```
SELECT external_table_name, cached, disabled 
  FROM user_external_tab_caches;
```
Pour plus d'informations, reportez-vous aux vues DBA_EXTERNAL_TAB_CACHES et USER_EXTERNAL_TAB_CACHES.
Utilisez la procédure DBMS_EXT_TABLE_CACHE.VALIDATE pour valider un cache de table externe. Une erreur est signalée si la table externe référencée est introuvable dans la base de données.
Exemple :
```
BEGIN
  DBMS_EXT_TABLE_CACHE.VALIDATE (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES',
    raise_errors  => TRUE);
END;
/
```
Pour plus d'informations, reportez-vous à Procédure VALIDATE.

Renseigner les fichiers dans le cache de table externe

Affiche des exemples de remplissage de fichiers dans le cache de table externe.

Une fois que vous avez créé un cache, vous pouvez l'alimenter. Le remplissage des fichiers charge le contenu des fichiers de table externes spécifiés dans le cache. Vous pouvez choisir de remplir tous les fichiers d'une table, un pourcentage spécifique de la table, ou spécifier une condition de filtre pour limiter les fichiers que vous souhaitez remplir. Par exemple, vous pouvez filtrer les fichiers en fonction de leur nom ou d'une plage de dates.

En fonction du quota d'espace alloué au schéma, Oracle tente d'alimenter les fichiers dans le cache. Si la limite de quota affectée est atteinte, Oracle arrête de remplir les fichiers, sauf si l'espace requis est alloué.

Le cache de table externe n'est pas actualisé automatiquement. Pour mettre à jour le cache lorsqu'un fichier de la banque d'objets est modifié, vous devez remplir à nouveau le fichier.

Lorsqu'un fichier est supprimé de la banque d'objets, les données mises en cache correspondantes deviennent immédiatement non valides et ne peuvent pas être extraites.

Ajouter une table au cache de table externe

Utilisez DBMS_EXT_TABLE_CACHE.ADD_TABLE pour remplir une table entière ou un certain pourcentage de la table externe dans le cache.

Exemples

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_TABLE (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES');
END;
/

Cet exemple tente de remplir la table STORE_SALES dans le cache, en ignorant tous les fichiers existants qui ont déjà été remplis.

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_TABLE (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES',
    percent_files => 80);
END;
/

Cet exemple tente de remplir 80 % de la table STORE_SALES dans le cache, en ignorant tous les fichiers existants qui ont déjà été remplis.

Le paramètre percent_files est facultatif. Si vous n'indiquez pas ce paramètre, la table entière est alimentée dans le cache.

Pour plus d'informations, reportez-vous à Procédure ADD_TABLE.

Ajouter des fichiers au cache de table externe

Vous pouvez utiliser les procédures suivantes pour ajouter des fichiers au cache de table externe :

ADD_FILE : pour ajouter un fichier unique dans le cache.
ADD_BY_LIKE : pour ajouter des fichiers spécifiés en fonction des filtres de chemin indiqués.
ADD_LATEST_FILES : pour ajouter des fichiers en fonction de l'intervalle de temps indiqué.

Exemples

Utilisez la procédure DBMS_EXT_TABLE_CACHE.ADD_FILE pour remplir un fichier unique dans le cache de table externe. Exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_FILE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    file_url     => 'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet'
);
END;
/

Cet exemple renseigne les données du fichier salesdata.parquet dans le cache.

Cet exemple ignore l'alimentation du fichier dans le cache si le fichier spécifié existe dans le cache et n'a pas été modifié depuis le dernier cache du fichier.

Pour plus d'informations, reportez-vous à Procédure ADD_FILE.

Utilisez la procédure DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE pour remplir un ou plusieurs fichiers dans le cache de table externe. Exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata1.parquet"]'
);
END;
/

Cet exemple renseigne les données des fichiers salesdata.parquet et salesdata1.parquet dans le cache, en ignorant les fichiers existants déjà renseignés.

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data1.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data2.parquet"]',
    esc_char     => '#',
    force        => TRUE);
END;
/

Cet exemple renseigne les fichiers sales_data1.parquet et sales_data2.parquet dans le cache.

Dans cet exemple, le caractère '#' est défini comme caractère d'échappement. Le caractère '_' suivant '#' est traité comme un trait de soulignement littéral et non comme un caractère générique correspondant à un caractère unique.

Pour plus d'informations, reportez-vous à Procédure ADD_BY_LIKE.

Utilisez la procédure DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES pour remplir un ou plusieurs fichiers en fonction de la date de dernière modification dans le cache de table externe. Exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    since        => INTERVAL '7' DAY,
    max_files    => 5,
    force        => TRUE);
END;
/

Le paramètre since indique l'intervalle de temps. Seuls les fichiers modifiés au cours des sept (7) derniers jours peuvent être renseignés dans le cache.

Le paramètre max_files limite le nombre de fichiers pouvant être renseignés dans le cache. Cet exemple ne renseigne que cinq (5) fichiers.

Le paramètre force force l'écrasement des fichiers indiqués dans le cache même s'ils n'ont pas été modifiés.

Pour plus d'informations, reportez-vous à Procédure ADD_LATEST_FILES.

Interrogez les vues suivantes du dictionnaire de données pour répertorier les fichiers mis en cache dans le cache de table externe :

Supprimer des fichiers du cache de table externe

Affiche des exemples de suppression de fichiers du cache de table externe.

Vous pouvez supprimer tous les fichiers du cache ou spécifier des conditions de filtre pour supprimer un ou plusieurs fichiers du cache. Par exemple, vous pouvez filtrer les fichiers en fonction de leur nom ou d'un intervalle de temps spécifique.

Effacer le cache de table externe

Utilisez DBMS_EXT_TABLE_CACHE.CLEAR pour supprimer tous les fichiers du cache de table externe. Exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.CLEAR (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES');
END;
/

Cet exemple supprime tous les fichiers du cache STORE_SALES et libère tout l'espace utilisé par les fichiers supprimés.

Pour plus d'informations, reportez-vous à la section Procédure CLEAR.

Supprimer des fichiers du cache de table externe

Vous pouvez utiliser les procédures suivantes pour supprimer un ou plusieurs fichiers du cache de table externe :

DROP_FILE : permet de supprimer un seul fichier du cache.
DROP_BY_LIKE : permet de supprimer des fichiers du cache en fonction des filtres de chemin indiqués.
RETIRE_FILES : permet de supprimer des fichiers du cache en fonction de l'intervalle indiqué.

Exemples

Utilisez DBMS_EXT_TABLE_CACHE.DROP_FILE pour supprimer un fichier du cache de table externe. Exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_FILE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    file_url     => 'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet'
);
END;
/

Cet exemple supprime le fichier salesdata.parquet du cache et libère tout l'espace utilisé par le fichier supprimé.

Pour plus d'informations, reportez-vous à Procédure DROP_FILE.

Utilisez DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE pour supprimer des fichiers en fonction du paramètre path_filters. Exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata1.parquet"]'
);
END;
/

Cet exemple supprime les fichiers salesdata.parquet et salesdata1.parquet du cache et libère tout l'espace utilisé par les fichiers supprimés.

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data1.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data1.parquet"]'
);
END;
/

Cet exemple supprime les fichiers sales#_data1 et sales#_data2 du cache et libère tout l'espace utilisé par les fichiers supprimés.

Dans cet exemple, le caractère '#' est défini comme caractère d'échappement. Le caractère '_' suivant '#' est traité comme un trait de soulignement littéral et non comme un caractère générique correspondant à un caractère unique.

Pour plus d'informations, reportez-vous à Procédure DROP_BY_LIKE.

Utilisez DBMS_EXT_TABLE_CACHE.RETIRE_FILES pour supprimer des fichiers en fonction de l'intervalle spécifié. Exemple :

BEGIN
 DBMS_EXT_TABLE_CACHE.RETIRE_FILES (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    before       => INTERVAL '30' DAY);
END;
/

Cet exemple supprime les fichiers qui datent de plus de trente (30) jours du cache et libère tout l'espace utilisé par les fichiers supprimés.

Pour plus d'informations, reportez-vous à Procédure RETIRE_FILES.

Les exemples ci-dessus suppriment un ou plusieurs fichiers du cache tout en conservant le cache. Si nécessaire, vous pouvez charger à nouveau les fichiers dans le cache. Pour plus d'informations, reportez-vous à Alimentation de fichiers dans le cache de table externe.

Désactivation et activation du cache de table externe

Affiche des exemples de désactivation et d'activation du cache de table externe.

Exécutez DBMS_EXT_TABLE_CACHE.DISABLE pour désactiver le cache de table externe de la base de données. La désactivation d'un cache ne supprime pas les données du cache. Au lieu de cela, le cache est marqué comme DISABLED et l'optimiseur ne peut pas utiliser le cache pour les réécritures de requête.

Exemple

BEGIN
    DBMS_EXT_TABLE_CACHE.DISABLE (      
      owner          => 'SALES',
      table_name     => 'STORE_SALES');                                                                 
END;                                                                 
/

Cet exemple désactive le cache STORE_SALES.

Pour plus d'informations, reportez-vous à Procédure DISABLE.

Une fois que vous avez désactivé un cache de table externe, utilisez DBMS_EXT_TABLE_CACHE.ENABLE pour activer le cache.

Par exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.ENABLE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES'
 );
END;
/

Cet exemple active le cache STORE_SALES.

Pour plus d'informations, reportez-vous à Procédure ACTIVABLE.

Supprimer le cache de table externe

Affiche un exemple de suppression du cache de table externe.

Exécutez DBMS_EXT_TABLE_CACHE.DROP_CACHE pour supprimer un cache de table externe. La procédure DBMS_EXT_TABLE_CACHE.DROP_CACHE supprime le cache de table externe indiqué de la base de données et libère l'espace de stockage associé au cache.

Par exemple :

BEGIN
    DBMS_EXT_TABLE_CACHE.DROP_CACHE (      
      owner          => 'SALES',
      table_name     => 'STORE_SALES');                                                             
END;                                                                 
/

Cet exemple supprime le cache STORE_SALES du schéma SALES.

La suppression d'un cache enlève ses métadonnées du dictionnaire de données et supprime toutes ses données en cache.

Pour plus d'informations, reportez-vous à Procédure DROP_CACHE.

Interrogez la vue USER_EXTERNAL_TAB_CACHES pour vérifier que le cache a été supprimé. Exemple :

SELECT external_table_name, cached
  FROM user_external_tab_caches;

Pour plus d'informations, reportez-vous aux vues DBA_EXTERNAL_TAB_CACHES et USER_EXTERNAL_TAB_CACHES.

Afficher les informations de cache de table externe

Autonomous Database fournit des vues qui vous permettent de surveiller le cache de table externe.

Afficher	Description
Vues DBA_EXTERNAL_TAB_CACHES et USER_EXTERNAL_TAB_CACHES	Fournit des informations sur tous les caches de table externe de la base de données ou sur les caches de table externe d'un utilisateur.
ALL_EXTERNAL_TAB_CACHE_LOCATIONS	Fournit des informations sur les fichiers du stockage cloud accessibles à l'utilisateur en cours et appartenant à des tables externes mises en cache.
USER_EXTERNAL_TAB_CACHE_LOCATIONS	Fournit des informations sur les fichiers du stockage cloud détenus par l'utilisateur en cours et appartenant à des tables externes mises en cache. Cette vue n'affiche pas la colonne `OWNER`.

Définir les préférences de dimensionnement facultatives pour le cache de table externe

Vous pouvez utiliser la procédure DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY pour définir les préférences de dimensionnement et les limites de quota d'espace sur le cache de table externe.

Par défaut, le cache de table externe est désactivé pour un utilisateur. Pour activer et créer le cache de table externe, utilisez la procédure DBMS_EXT_TABLE_CACHE.CREATE_CACHE. Le cache est créé dans le schéma par défaut et hérite des limites de quota d'espace définies pour le schéma. Toutefois, vous pouvez également utiliser la procédure DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY pour définir des quotas d'espace pour le cache de table externe. Vous utilisez les paramètres PROPERTY_NAME et PROPERTY_VALUE de la procédure DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY pour définir les limites de quota d'espace.

Le paramètre PROPERTY_NAME accepte les valeurs MAX_CACHE_SIZE et MAX_CACHE_PERCENT. La propriété MAX_CACHE_SIZE indique la taille totale du cache externe en octets. La propriété MAX_CACHE_PERCENT indique la taille totale du cache externe en pourcentage du quota de l'utilisateur spécifié.

Avant de définir les propriétés de taille du cache, notez l'ordre de priorité suivant :

Lorsque MAX_CACHE_SIZE, MAX_CACHE_PERCENT et le quota d'espace sont définis, MAX_CACHE_PERCENT est prioritaire sur MAX_CACHE_SIZE.
Lorsque seul MAX_CACHE_SIZE est défini et que MAX_CACHE_PERCENT ou le quota d'espace n'est pas défini, MAX_CACHE_SIZE est prioritaire.
Lorsque seul le quota d'espace est défini et que MAX_CACHE_SIZE et MAX_CACHE_PERCENT ne sont pas définis, le quota de taille de cache est par défaut égal à 10 % du quota de schéma total.
Lorsque MAX_CACHE_SIZE, MAX_CACHE_PERCENT ou le quota d'espace n'est pas défini, le quota d'espace de cache est défini par défaut sur UNLIMITED.

Exemples

BEGIN
    DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY (
    property_name  => 'MAX_CACHE_PERCENT',          
    property_value => 50,                   
    owner          => 'SALES');                                                                
END;                                                                 
/

Cet exemple définit la préférence de mise en cache sur MAX_CACHE_PERCENT pour le schéma SALES.

property_value est égal à 50 %, ce qui indique que le quota d'espace de cache pour le schéma SALES correspond au maximum à 50 % du quota d'espace total défini pour SALES.

BEGIN
    DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY (
    owner          => 'SALES',   
    property_name  => 'MAX_CACHE_SIZE',          
    property_value => 5368709120);                                                                
END;                                                                 
/

Cet exemple définit la préférence de mise en cache sur MAX_CACHE_SIZE pour le schéma SALES.

property_value est 5368709120, ce qui indique que la taille maximale du cache pour le schéma SALES est de 5 Go maximum.

Pour plus d'informations, reportez-vous à Procédure SET_USER_PROPERTY et à Procédure CREATE_CACHE.

Utilisez DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY pour extraire les propriétés de taille de cache.

Par exemple :

SET SERVEROUTPUT ON
    DECLARE
    max_cache_sz NUMBER,
    BEGIN
    max_cache_sz := DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY (
        property_name       => 'MAX_CACHE_SIZE',         
        owner               => 'SALES');                                                                
END;                                                                 
/

Pour plus d'informations, reportez-vous à Fonction GET_USER_PROPERTY.

Colonnes de métadonnées de table externe

Les métadonnées de table externe vous aident à déterminer d'où proviennent les données lorsque vous exécutez une requête.

Les tables externes que vous créez avec DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE ou DBMS_CLOUD.CREATE_HYBRID_PART_TABLE incluent deux colonnes invisibles file$path et file$name. Ces colonnes permettent d'identifier le fichier d'où provient un enregistrement.

file$path : indique le texte du chemin du fichier jusqu'au début du nom de l'objet.
file$name : indique le nom de l'objet, y compris tout le texte qui suit le "/" final.

Exemple :

SELECT genre_id, name, file$name, file$path FROM ext_genre
     WHERE rownum <= 2;

genre_id      name        file$name     file$path
--------      ---------   -----------   ----------------------
1             Action      genre.csv     https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre
2             Adventure   genre.csv     https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre

Pour plus d'informations sur les colonnes invisibles, voir Colonnes invisibles.