Mejorar el rendimiento de las consultas con la caché de tablas externas

Acerca de la caché de tablas externas en Autonomous Database

Una caché de tabla externa es un área de almacenamiento de la instancia de Autonomous Database que almacena los datos de una tabla externa.

Los datos externos no los gestiona la base de datos; sin embargo, puede utilizar las tablas externas para consultar datos fuera de la base de datos. Las consultas en tablas externas no serán tan rápidas como las consultas en tablas de base de datos porque cada vez que acceda a los datos se deben recuperar de los archivos externos almacenados en el almacén de objetos.

La función de caché de tablas externas permite almacenar en caché datos a los que se accede con frecuencia desde tablas externas en Autonomous Database, sin necesidad de realizar modificaciones en las aplicaciones que acceden a los datos, lo que proporciona un acceso más rápido a las tablas externas.

A continuación se muestran algunas de las ventajas de utilizar la caché de tabla externa:

Mejora del rendimiento: las consultas son varias veces más rápidas para los datos externos a los que se accede con frecuencia, lo que resulta ideal para paneles de control, informes y herramientas analíticas que acceden a los mismos datos con regularidad.
100% transparente: el mecanismo de almacenamiento en caché es totalmente transparente; las aplicaciones pueden beneficiarse de una velocidad mejorada sin necesidad de realizar cambios en sus consultas, paneles de control o aplicaciones.
Costos en la nube más bajos: en una aplicación multinube, el almacenamiento en caché reduce la necesidad de repetidas recuperaciones de datos externos del almacenamiento remoto, lo que reduce las tarifas de salida de datos asociadas al acceso a los datos entre regiones o nubes.

Puede crear caché de tablas externas para tablas particionadas y no particionadas creadas en los siguientes tipos de archivos:

Parquet
ORC
AVRO
Tablas de iceberg

Consulte Consulta de datos externos para obtener más información.

Requisitos

Muestra los requisitos para crear la caché de tabla externa.

Debe tener el privilegio EXECUTE en el paquete DBMS_EXT_TABLE_CACHE. Tenga en cuenta que solo puede crear una caché de tabla externa en su propio esquema y para las tablas externas de las que es propietario.
Debe tener asignada una cuota de espacio adecuada para el esquema a fin de garantizar que haya suficiente capacidad de almacenamiento para los datos de caché.
Debe tener credenciales para acceder a los archivos de tabla externa almacenados en el almacén de objetos. No necesita crear credenciales si activa las credenciales de entidad de recurso para acceder al almacén de objetos de Oracle Cloud Infrastructure.

Limitaciones

Muestra notas importantes y restricciones sobre el uso de la caché de tablas externas en una instancia de Autonomous Database.

Solo puede crear una caché de tabla externa en su propio esquema y para las tablas externas que posee.
Una caché de tabla externa solo puede contener archivos de una única tabla externa.
No puede otorgar privilegios en una caché de tabla externa y solo puede acceder a los datos de la caché de tabla externa de la que es propietario.
Cuando se suprime un archivo del almacén de objetos, los datos correspondientes de la caché se marcan como no válidos y no se puede acceder a ellos.

Crear Caché de Tabla Externa

Ejecute DBMS_EXT_TABLE_CACHE.CREATE_CACHE para crear una caché de tabla externa.

Cuando se crea la caché, inicialmente está vacía y activada para relleno. El tamaño de la caché aumenta cada vez que se agrega un archivo, según los límites de cuota de espacio definidos para el esquema, hasta que alcanza los límites asignados. Consulte Definición de Preferencias de Tamaño Opcionales para Caché de Tablas Externas para obtener más información.

Utilice DBMS_EXT_TABLE_CACHE.CREATE_CACHE para crear la caché de tabla externa para el esquema. Por ejemplo:
```
BEGIN
    DBMS_EXT_TABLE_CACHE.CREATE_CACHE (      
      owner          => 'SALES',
      table_name     => 'STORE_SALES',
      partition_type => 'PATH');                                                                 
END;                                                                 
/
```
Esto crea una caché para la tabla STORE_SALES en el esquema SALES. STORE_SALES es una tabla externa que apunta a los datos almacenados en el almacén de objetos.

El parámetro owner especifica el nombre del esquema. En este ejemplo se crea una caché de tabla externa para el usuario SALES.

El parámetro partition_type especifica el método de partición que se va a utilizar para la caché de tabla externa. En este ejemplo, se particiona la caché mediante la columna FILE$PATH. FILE$PATH es una columna invisible que especifica el texto de la ruta de acceso del archivo hasta el principio del nombre del objeto.
Consulte Columnas de metadatos de tabla externa y Procedimiento CREATE_CACHE para obtener más información.
La primera vez que crea una caché de tabla externa, sus metadatos se almacenan en el diccionario de datos, pero no se asigna espacio para los datos de la caché. Puede consultar la vista USER_EXTERNAL_TAB_CACHES para verificar la creación de la caché.
```
SELECT external_table_name, cached, disabled 
  FROM user_external_tab_caches;
```
Consulte Vistas DBA_EXTERNAL_TAB_CACHES y USER_EXTERNAL_TAB_CACHES para obtener más información.
Utilice el procedimiento DBMS_EXT_TABLE_CACHE.VALIDATE para validar una caché de tabla externa. Se informa de un error si no se encuentra la tabla externa a la que se hace referencia en la base de datos.
Por ejemplo:
```
BEGIN
  DBMS_EXT_TABLE_CACHE.VALIDATE (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES',
    raise_errors  => TRUE);
END;
/
```
Consulte Procedimiento VALIDATE para obtener más información.

Rellenar Archivos en Caché de Tabla Externa

Muestra ejemplos para rellenar archivos en la caché de tabla externa.

Después de crear una caché, puede rellenar los archivos en la caché. Al rellenar los archivos, se carga el contenido de los archivos de tabla externa especificados en la caché. Puede elegir rellenar todos los archivos de una tabla, un porcentaje específico de la tabla o especificar una condición de filtro para limitar los archivos que desea rellenar. Por ejemplo, puede filtrar los archivos según sus nombres o un rango de fechas.

Según la cuota de espacio asignada para el esquema, Oracle intenta rellenar los archivos en la caché. Si se alcanza el límite de cuota asignado, Oracle deja de rellenar los archivos a menos que se asigne el espacio necesario.

La caché de tabla externa no se refresca automáticamente. Para actualizar la caché cuando se modifica un archivo del almacén de objetos, debe volver a rellenar el archivo.

Cuando se suprime un archivo del almacén de objetos, los datos almacenados en caché correspondientes se convierten inmediatamente en no válidos y no se pueden recuperar.

Agregar tabla a caché de tabla externa

Utilice DBMS_EXT_TABLE_CACHE.ADD_TABLE para rellenar una tabla completa o un determinado porcentaje de la tabla externa en la caché.

Ejemplos

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

En este ejemplo, se intenta rellenar la tabla STORE_SALES en la caché, omitiendo cualquier archivo existente que ya se haya rellenado.

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

Este ejemplo intenta rellenar el 80% de la tabla STORE_SALES en la caché, omitiendo cualquier archivo existente que ya se haya rellenado.

El parámetro percent_files es opcional; si no especifica este parámetro, toda la tabla se rellena en la caché.

Consulte ADD_TABLE Procedure para obtener más información.

Agregar archivos a caché de tabla externa

Puede utilizar los siguientes procedimientos para agregar uno o más archivos a la caché de tabla externa:

ADD_FILE: para agregar un único archivo a la caché.
ADD_BY_LIKE: para agregar uno o más archivos especificados según los filtros de ruta de acceso especificados.
ADD_LATEST_FILES: para agregar uno o más archivos según el intervalo de tiempo especificado.

Ejemplos

Utilice el procedimiento DBMS_EXT_TABLE_CACHE.ADD_FILE para rellenar un único archivo en la caché de tabla externa. Por ejemplo:

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;
/

En este ejemplo, se rellenan los datos del archivo salesdata.parquet en la caché.

En este ejemplo, se omite rellenar el archivo en la caché si el archivo especificado existe en la caché y no se ha modificado desde la última vez que el archivo se almacenó en caché.

Consulte ADD_FILE Procedure para obtener más información.

Utilice el procedimiento DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE para rellenar uno o más archivos en la caché de tabla externa. Por ejemplo:

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;
/

En este ejemplo, se rellenan los datos de los archivos salesdata.parquet y salesdata1.parquet en la caché, omitiendo cualquier archivo existente que ya se haya rellenado.

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;
/

En este ejemplo, se rellenan los archivos sales_data1.parquet y sales_data2.parquet en la caché.

En este ejemplo, el carácter '#' se define como el carácter de escape. El carácter '_' que sigue a '#' se trata como un guion bajo literal, no como un comodín que coincide con un solo carácter.

Consulte ADD_BY_LIKE Procedure para obtener más información.

Utilice el procedimiento DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES para rellenar uno o más archivos en función de la fecha de última modificación en la caché de tabla externa. Por ejemplo:

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

El parámetro since especifica el intervalo de tiempo; solo los archivos modificados en los últimos siete (7) días son elegibles para rellenarse en la caché.

El parámetro max_files limita el número de archivos que se pueden rellenar en la caché. Este ejemplo rellena sólo cinco (5) archivos.

El parámetro force fuerza la sobrescritura de los archivos especificados en la caché aunque los archivos no se hayan modificado.

Consulte ADD_LATEST_FILES Procedure para obtener más información.

Consulte las siguientes vistas del diccionario de datos para mostrar los archivos almacenados en caché en la caché de tabla externa:

Borrar archivos de la caché de tabla externa

Muestra ejemplos para borrar archivos de la caché de tabla externa.

Puede eliminar todos los archivos de la caché o especificar condiciones de filtro para borrar uno o más archivos de la caché. Por ejemplo, puede filtrar los archivos por sus nombres o según un intervalo de tiempo específico.

Borrar caché de tabla externa

Utilice DBMS_EXT_TABLE_CACHE.CLEAR para borrar todos los archivos de la caché de tabla externa. Por ejemplo:

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

En este ejemplo, se borran todos los archivos de la caché STORE_SALES y se anula la asignación de todo el espacio utilizado por los archivos eliminados.

Consulte Procedimiento LIMPIEZA para obtener más información.

Borrar archivos de la caché de tabla externa

Puede utilizar los siguientes procedimientos para borrar uno o más archivos de la caché de tabla externa:

DROP_FILE: para borrar un único archivo de la caché.
DROP_BY_LIKE: para borrar uno o más archivos de la caché según los filtros de ruta de acceso especificados.
RETIRE_FILES: para borrar uno o más archivos de la caché según el intervalo especificado.

Ejemplos

Utilice DBMS_EXT_TABLE_CACHE.DROP_FILE para borrar un archivo de la caché de tabla externa. Por ejemplo:

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;
/

En este ejemplo, se borra el archivo salesdata.parquet de la caché y se anula la asignación de todo el espacio utilizado por el archivo eliminado.

Consulte DROP_FILE Procedure para obtener más información.

Utilice DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE para borrar uno o más archivos según el parámetro path_filters. Por ejemplo:

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;
/

En este ejemplo, se borran los archivos salesdata.parquet y salesdata1.parquet de la caché y se anula la asignación de todo el espacio utilizado por los archivos eliminados.

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;
/

En este ejemplo, se borran los archivos sales#_data1 y sales#_data2 de la caché y se anula la asignación de todo el espacio utilizado por los archivos eliminados.

En este ejemplo, el carácter '#' se define como el carácter de escape. El carácter '_' que sigue a '#' se trata como un guion bajo literal, no como un comodín que coincide con un solo carácter.

Consulte DROP_BY_LIKE Procedure para obtener más información.

Utilice DBMS_EXT_TABLE_CACHE.RETIRE_FILES para borrar uno o más archivos según el intervalo especificado. Por ejemplo:

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

En este ejemplo, se borran los archivos que tienen más de treinta (30) días de la caché y se anula la asignación de todo el espacio utilizado por los archivos eliminados.

Consulte RETIRE_FILES Procedure para obtener más información.

Los ejemplos anteriores eliminan uno o más archivos de la caché mientras conservan la caché. Puede volver a cargar los archivos en la caché cuando sea necesario. Consulte Relleno de Archivos en Caché de Tabla Externa para obtener más información.

Desactivar y activar caché de tabla externa

Muestra ejemplos para desactivar y activar la caché de tabla externa.

Ejecute DBMS_EXT_TABLE_CACHE.DISABLE para desactivar la caché de tabla externa de la base de datos. La desactivación de una caché no suprime datos de la caché; en su lugar, la caché se marca como DISABLED y el optimizador no puede utilizar la caché para reescrituras de consulta.

Ejemplo

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

En este ejemplo, se desactiva la caché STORE_SALES.

Consulte DISABLE Procedure para obtener más información.

Después de desactivar una caché de tabla externa, utilice DBMS_EXT_TABLE_CACHE.ENABLE para activar la caché.

Por ejemplo:

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

Este ejemplo activa la caché STORE_SALES.

Consulte ENABLE Procedure para obtener más información.

Borrar caché de tabla externa

Muestra un ejemplo para borrar la caché de tabla externa.

Ejecute DBMS_EXT_TABLE_CACHE.DROP_CACHE para borrar una caché de tabla externa. El procedimiento DBMS_EXT_TABLE_CACHE.DROP_CACHE elimina la caché de tabla externa especificada de la base de datos y libera el espacio de almacenamiento asociado a la caché.

Por ejemplo:

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

En este ejemplo se borra la caché STORE_SALES del esquema SALES.

Al borrar una caché, se eliminan los metadatos del diccionario de datos y se suprimen todos los datos almacenados en caché.

Consulte DROP_CACHE Procedure para obtener más información.

Consulte la vista USER_EXTERNAL_TAB_CACHES para verificar que la caché se ha borrado. Por ejemplo:

SELECT external_table_name, cached
  FROM user_external_tab_caches;

Consulte Vistas DBA_EXTERNAL_TAB_CACHES y USER_EXTERNAL_TAB_CACHES para obtener más información.

Ver información de caché de tabla externa

Autonomous Database proporciona vistas que permiten supervisar la caché de tabla externa.

Ver	Descripción
Vistas DBA_EXTERNAL_TAB_CACHES y USER_EXTERNAL_TAB_CACHES	Proporciona información sobre todas las cachés de tablas externas de la base de datos o sobre las cachés de tablas externas de un usuario.
ALL_EXTERNAL_TAB_CACHE_LOCATIONS	Proporciona información sobre los archivos de Cloud Storage a los que puede acceder el usuario actual y que pertenecen a tablas externas almacenadas en caché.
USER_EXTERNAL_TAB_CACHE_LOCATIONS	Proporciona información sobre los archivos del almacenamiento en la nube que son propiedad del usuario actual y que pertenecen a tablas externas almacenadas en caché. Esta vista no muestra la columna `OWNER`.

Definir preferencias de tamaño opcionales para caché de tabla externa

Puede utilizar el procedimiento DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY para definir las preferencias de tamaño y los límites de cuota de espacio en la caché de tabla externa.

Por defecto, la caché de tabla externa está desactivada para un usuario. Para activar y crear la caché de tabla externa, utilice el procedimiento DBMS_EXT_TABLE_CACHE.CREATE_CACHE. La caché se crea en el esquema por defecto y hereda los límites de cuota de espacio definidos para el esquema. Sin embargo, también puede utilizar el procedimiento DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY para definir cuotas de espacio para la caché de tabla externa. Los parámetros PROPERTY_NAME y PROPERTY_VALUE del procedimiento DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY se utilizan para definir los límites de cuota de espacio.

El parámetro PROPERTY_NAME acepta los valores MAX_CACHE_SIZE y MAX_CACHE_PERCENT. La propiedad MAX_CACHE_SIZE especifica el tamaño total de la caché externa en bytes. La propiedad MAX_CACHE_PERCENT especifica el tamaño total de la caché externa como porcentaje de la cuota del usuario especificado.

Antes de definir las propiedades de tamaño de caché, tenga en cuenta el siguiente orden de prioridad:

Cuando se definen MAX_CACHE_SIZE, MAX_CACHE_PERCENT y la cuota de espacio, MAX_CACHE_PERCENT tiene prioridad sobre MAX_CACHE_SIZE.
Cuando solo se define MAX_CACHE_SIZE y no se define MAX_CACHE_PERCENT o cuota de espacio, MAX_CACHE_SIZE tiene prioridad.
Cuando solo se define la cuota de espacio y no se definen MAX_CACHE_SIZE ni MAX_CACHE_PERCENT, la cuota de tamaño de caché se define por defecto en el 10% de la cuota de esquema total.
Cuando no se define MAX_CACHE_SIZE, MAX_CACHE_PERCENT o la cuota de espacio, la cuota de espacio de caché se define por defecto en UNLIMITED.

Ejemplos

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

En este ejemplo se define la preferencia de almacenamiento en caché en MAX_CACHE_PERCENT para el esquema SALES.

property_value es el 50%, que especifica que la cuota de espacio de caché para el esquema SALES es un máximo del 50% de la cuota de espacio total definida para SALES.

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

En este ejemplo se define la preferencia de almacenamiento en caché en MAX_CACHE_SIZE para el esquema SALES.

property_value es 5368709120, que especifica que el tamaño máximo de caché para el esquema SALES es de hasta 5 GB.

Consulte Procedimiento SET_USER_PROPERTY y Procedimiento CREATE_CACHE para obtener más información.

Utilice DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY para recuperar las propiedades de tamaño de caché.

Por ejemplo:

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;                                                                 
/

Consulte GET_USER_PROPERTY Function para obtener más información.

Columnas de metadatos de tabla externa

Los metadatos de la tabla externa ayudan a determinar de dónde proceden los datos al realizar una consulta.

Las tablas externas que crea con DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE o DBMS_CLOUD.CREATE_HYBRID_PART_TABLE incluyen dos columnas invisibles file$path y file$name. Estas columnas ayudan a identificar de qué archivo procede un registro.

file$path: especifica el texto de la ruta de acceso del archivo hasta el principio del nombre del objeto.
file$name: especifica el nombre del objeto, incluido todo el texto que sigue al final "/".

Por ejemplo:

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

Consulte Columnas invisibles para obtener más información sobre las columnas invisibles.