Use External Table Cache to Improve Performance for External Tables

External Table Cache in Oracle Autonomous Database enables you to cache frequently accessed data from external tables in your database.

Note:

External Table Cache is only supported for Oracle Database 23ai.

Parent topic: Features

About External Table Cache in Autonomous Database

An external table cache is a storage area in your Autonomous Database that stores the data from an external table.

External data is not managed by the database; however, you can use the external tables to query data outside of the database. Queries on external tables will not be as fast as queries on database tables because each time you access the data it needs to be fetched from the external files stored on Object Store.

The external table cache feature enables you to cache frequently accessed data from external tables within your Autonomous Database, without requiring modifications to your applications that access the data, thereby providing faster access to external tables.

The following are some of the benefits of using the external table cache:

  • Improved Performance: Queries are several times faster for your frequently accessed external data - ideal for dashboards, reports, and analytical tools that access the same data regularly.

  • 100% Transparent: The caching mechanism is entirely transparent; applications can benefit from improved speed without requiring any changes to their queries, dashboards, or applications.

  • Lower Cloud Costs: In a multi-cloud application, caching reduces the need for repeated external data retrievals from remote storage, thereby reducing data egress fees associated with accessing data across regions or clouds.

You can create external table cache for partitioned and non-partitioned tables created on the following file types:

  • Parquet

  • JSON

  • Iceberg Tables

See Query External Data for more information.

Parent topic: Use External Table Cache to Improve Performance for External Tables

Prerequisites to Create External Table Cache

Lists the prerequisites to create the external table cache.

  • You must have the EXECUTE privilege on the DBMS_EXT_TABLE_CACHE package. Note that you can only create an external table cache in your own schema and for the external tables that you own.

  • You must have an appropriate space quota allocated for your schema to ensure there is sufficient storage capacity for the cache data.

  • You must have credentials to access external table files stored on Object Store. You don't need to create credentials if you enable resource principal credentials for accessing Oracle Cloud Infrastructure Object Store.

Parent topic: Use External Table Cache to Improve Performance for External Tables

Create and Manage External Table Cache in Autonomous Database

Using the external table cache, you can cache the data from external tables within your database.

The external table cache is created as a schema object in your database, which is allocated physical space similar to how tables and indexes are stored in data files. When you create an external table cache, a new table is created in your schema, and any space quota limits that are set for your schema also apply to the external table cache.

Parent topic: Use External Table Cache to Improve Performance for External Tables

Create External Table Cache

Run DBMS_EXT_TABLE_CACHE.CREATE_CACHE to create an external table cache.

When the cache is created, it is initially empty and enabled for population. The cache size increases each time a file is added, depending on the defined space quota limits for the schema, until it reaches the assigned limits. See Set Optional Sizing Preferences for External Table Cache for more information.

  1. Use DBMS_EXT_TABLE_CACHE.CREATE_CACHE to create external table cache for your schema. For example:
    BEGIN
    DBMS_EXT_TABLE_CACHE.CREATE_CACHE (      
      owner          => 'SALES',
      table_name     => 'store_sales',
      partition_type => 'PATH');                                                                 
END;                                                                 
/

    This creates a cache for the store_sales table in the SALES schema. The store_sales is an external table pointing to data stored on Object Store.

    The owner parameter specifies the schema name. This example creates an external table cache for the SALES user.

    The partition_type parameter specifies the partitioning method to use for the external table cache. This example partitions the cache using the FILE$PATH column. The FILE$PATH is an invisible column that specifies the file path text up to the beginning of the object name.

    See the following for more information:

    CREATE_CACHE Procedure

    External Table Metadata Columns

  2. When you first create an external table cache, its metadata is stored in the data dictionary, however no space is allocated for the cache data. You can query the USER_EXTERNAL_TAB_CACHES view to verify the cache creation.
    SELECT external_table_name, cached, disabled 
  FROM user_external_tab_caches;

    See DBA_EXTERNAL_TAB_CACHES and USER_EXTERNAL_TAB_CACHES Views for more information.

    Use the DBMS_EXT_TABLE_CACHE.VALIDATE procedure to validate an external table cache. An error is reported if the referenced external table is not found in the database.

    For example:
    BEGIN
  DBMS_EXT_TABLE_CACHE.VALIDATE (
    owner         => 'SALES', 
    table_name    => 'store_sales',
    raise_errors  => TRUE);
END;
/

    See VALIDATE Procedure for more information.

Parent topic: Create and Manage External Table Cache in Autonomous Database

Populate Files into External Table Cache

Shows examples to populate files into the external table cache.

After you create a cache, you can populate files into the cache. Populating files loads the contents of the specified external table files into the cache. You can choose to populate all files from a table, a specific percentage of the table, or specify a filter condition to limit the files you want to populate. For example, you can filter the files based on their names or a date range.

Note:

  • Depending on the space quota allocated for the schema, Oracle attempts to populate files into the cache. If the assigned quota limit is reached, Oracle stops populating files unless the required space is allocated.

  • The external table cache does not refresh automatically. To update the cache when a file on the Object Store is modified, you must repopulate the file.

  • When a file is deleted from the Object Store, the corresponding cached data immediately becomes invalid and cannot be retrieved.

Add Table to External Table Cache

Use DBMS_EXT_TABLE_CACHE.ADD_TABLE to populate an entire table or a certain percentage of the external table into the cache.

Examples

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

This example attempts to populate the store_sales table into the cache, skipping any existing files that have already been populated. 

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

This example attempts to populate 80% of the store_sales table into the cache, skipping any existing files that have already been populated.

The percent_files parameter is optional; if you do not specify this parameter, the entire table is populated into the cache.

See ADD_TABLE Procedure for more information.

Add Files to External Table Cache

You can use the following procedures to add one or more files to the external table cache:

  • ADD_FILE: to add a single file into the cache.

  • ADD_BY_LIKE: to add one or more specified files based on the specified path filters.

  • ADD_LATEST_FILES: to add one or more files based on the specified time interval.

Examples

Use the DBMS_EXT_TABLE_CACHE.ADD_FILE procedure to populate a single file into the external table cache. For example:
BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_FILE (
    owner        => 'SALES', 
    table_name   => 'store_sales',
    file_url     => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/salesdata.parquet';
END;
/

This example populates data from the salesdata.parquet file into the cache.

This example skips populating the file into the cache if the specified file exists in the cache and has not been modified since the file was last cached.

See ADD_FILE Procedure for more information.

Use the DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE procedure to populate one or more files into the external table cache. For example:
BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE (
    owner        => 'SALES', 
    table_name   =>'store_sales',
    path_filters => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/salesdata.parquet',
                    'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/salesdata1.parquet');
END;
/

This example populates data from the salesdata.parquet and salesdata1.parquet files into the cache, skipping any existing files that have already been populated. 

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE (
    owner        => 'SALES', 
    table_name   =>'store_sales',
    path_filters => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/sales#_data1.parquet',
                    'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/sales#_data2.parquet',
    esc_char     => '#',
    force        => TRUE);
END;
/

This example populates the sales_data1.parquet and sales_data2.parquet files into the cache.

In this example, '#' character is defined as the escape character. The '_' character following '#' is treated as a literal underscore, not as a wildcard matching any single character.

See ADD_BY_LIKE Procedure for more information.

Use the DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES procedure to populate one or more files based on the last modified date into the external table cache. For example:
BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES (
    owner        => 'SALES', 
    table_name   => 'store_sales',
    since        => INTERVAL '7', DAY,
    max_files    => 5,
    force        => TRUE);
END;
/

The since parameter specifies the time interval; only files modified within the last seven (7) days are eligible to be populated into the cache.

The max_files parameter limits the number of files that can be populated into the cache. This example populates only five (5) files.

The force parameter forces the specified files to be overwritten in the cache even if the files were not modified.

See ADD_LATEST_FILES Procedure for more information.

Query the following data dictionary views to list the files cached in the external table cache:

Parent topic: Create and Manage External Table Cache in Autonomous Database

Drop Files from External Table Cache

Shows examples to drop files from external table cache.

You can remove all files from the cache, or specify filter conditions to drop one or more files from the cache. For example, you can filter the files by their names or based on a specific time interval.

Clear External Table Cache

Use DBMS_EXT_TABLE_CACHE.CLEAR to drop all files from the external table cache. For example: 

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

This example drops all files from the store_sales cache and deallocates all space used by the removed files.

See CLEAR Procedure for more information.

Drop Files from External Table Cache

You can use the following procedures to drop one or more files from the external table cache:

  • DROP_FILE: to drop a single file from the cache.

  • DROP_BY_LIKE: to drop one or more files from the cache based on the specified path filters.

  • RETIRE_FILES: to drop one or more files from the cache based on the specified interval.

Examples

Use DBMS_EXT_TABLE_CACHE.DROP_FILE to drop a file from the external table cache. For example: 

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_FILE (
    owner        => 'SALES', 
    table_name   => 'store_sales',
    file_url     => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/salesdata.parquet';
END;
/

This example drops the salesdata.parquet file from the cache and deallocates all space used by the removed file.

See DROP_FILE Procedure for more information.

Use DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE to drop one or more files based on the path_filters parameter. For example: 

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'store_sales',
    path_filters => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/salesdata.parquet',
                    'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/salesdata1.parquet');
END;
/

This example drops the salesdata.parquet and salesdata1.parquet files from the cache and deallocates all space used by the removed files. 

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'store_sales',
    path_filters => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/sales#_data1.parquet',
                    'https://objectstorage.us-ashburn-1.oraclecloud.com/n/your_namespace/b/your_bucket/o/sales#_data1.parquet');
END;
/

This example drops the sales#_data1 and sales#_data2 files from the cache and deallocates all space used by the removed files.

In this example, the '#' character is defined as the escape character. The '_' character following '#' is treated as a literal underscore, not as a wildcard matching any single character.

See DROP_BY_LIKE Procedure for more information.

Use DBMS_EXT_TABLE_CACHE.RETIRE_FILES to drop one or more files based on the specified interval. For example: 

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

This example drops files that are older than thirty (30) days from the cache and deallocates all space used by the removed files.

See RETIRE_FILES Procedure for more information.

The above examples remove one or more files from the cache while retaining the cache. You can load files again into the cache when necessary. See Populate Files into External Table Cache for more information.

Parent topic: Create and Manage External Table Cache in Autonomous Database

Disable and Enable External Table Cache

Shows examples to disable and enable the external table cache.

Run DBMS_EXT_TABLE_CACHE.DISABLE to disable external table cache from the database. Disabling a cache does not delete data from the cache; instead, the cache is flagged as DISABLED, and the optimizer cannot use the cache for query rewrites.

Example

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

This example disables the store_sales cache.

See DISABLE Procedure for more information.

After you disable an external table cache, use DBMS_EXT_TABLE_CACHE.ENABLE to enable the cache.

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

This example enables the store_sales cache.

See ENABLE Procedure for more information.

Parent topic: Create and Manage External Table Cache in Autonomous Database

Drop External Table Cache

Shows an example to drop the external table cache.

Run DBMS_EXT_TABLE_CACHE.DROP_CACHE to drop an external table cache. The DBMS_EXT_TABLE_CACHE.DROP_CACHE procedure removes the specified external table cache from the database and releases the storage space associated with the cache.

Example:

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

This example drops the store_sales cache from the SALES schema.

Dropping a cache removes its metadata from the data dictionary and deletes all its cached data.

See DROP_CACHE Procedure for more information.

Query the USER_EXTERNAL_TAB_CACHES view to verify that the cache has been dropped. For example:
SELECT external_table_name, cached
  FROM user_external_tab_caches;
See DBA_EXTERNAL_TAB_CACHES and USER_EXTERNAL_TAB_CACHES Views for more information.

Parent topic: Create and Manage External Table Cache in Autonomous Database

View External Table Cache Information

Autonomous Database provides views that allow you to monitor the external table cache.

View Description
DBA_EXTERNAL_TAB_CACHES and USER_EXTERNAL_TAB_CACHES Views

Provides information either about all external table caches in the database or about a user's external table caches.

ALL_EXTERNAL_TAB_CACHE_LOCATIONS

Provides information about the files in Cloud Storage that are accessible to the current user and belong to cached external tables.

USER_EXTERNAL_TAB_CACHE_LOCATIONS

Provides information about the files in cloud storage that are owned by the current user and belong to cached external tables. This view does not display the OWNER column.

Parent topic: Create and Manage External Table Cache in Autonomous Database

Set Optional Sizing Preferences for External Table Cache

You can use the DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY procedure to set sizing preferences and space quota limits on the external table cache.

By default, the external table cache is disabled for a user. To enable and create the external table cache use the DBMS_EXT_TABLE_CACHE.CREATE_CACHE procedure. The cache is created in your default schema and inherits any space quota limits defined for your schema. However, you can also use the DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY procedure to define space quotas for external table cache. You use the PROPERTY_NAME and PROPERTY_VALUE parameters of the DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY procedure to set the space quota limits.

The PROPERTY_NAME parameter accepts MAX_CACHE_SIZE and MAX_CACHE_PERCENT values. The MAX_CACHE_SIZE property specifies the total external cache size in bytes. The MAX_CACHE_PERCENT property specifies the total external cache size as a percentage of the specified user's quota.

Before you set the cache size properties, note the following precedence order:

  • When MAX_CACHE_SIZE, MAX_CACHE_PERCENT, and the space quota are defined, MAX_CACHE_PERCENT takes precedence over MAX_CACHE_SIZE.

  • When only MAX_CACHE_SIZE is defined, and MAX_CACHE_PERCENT or space quota is not defined, MAX_CACHE_SIZE takes precedence.

  • When only the space quota is defined and MAX_CACHE_SIZE and MAX_CACHE_PERCENT are not defined, the cache size quota defaults to 10% of the total schema quota.

  • When MAX_CACHE_SIZE, MAX_CACHE_PERCENT, or the space quota is not defined, the cache space quota defaults to UNLIMITED.

Examples

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

This example sets the caching preference to MAX_CACHE_PERCENT for the SALES schema.

The property_value is 50%, which specifies that the cache space quota for the SALES schema is a maximum of 50% of the total space quota defined for SALES. 

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

This example sets the caching preference to MAX_CACHE_SIZE for the SALES schema.

The property_value is 5368709120, which specifies that the maximum cache size for the SALES schema is up to 5GB.

See SET_USER_PROPERTY Procedure and CREATE_CACHE Procedure for more information.

Use DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY to retrieve the cache size properties.

Example:

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

See GET_USER_PROPERTY Function for more information.

Parent topic: Use External Table Cache to Improve Performance for External Tables

External Table Cache Notes

Lists important notes and restrictions about using external table cache in an Autonomous Database.

  • You can only create an external table cache in your own schema and for the external tables that you own.

  • An external table cache can contain files only from a single external table.

  • You cannot grant privileges on an external table cache, and you can only access data from the external table cache that you own.

  • When a file is deleted from Object Store, the corresponding data in the cache is flagged as invalid and cannot be accessed.

Parent topic: Use External Table Cache to Improve Performance for External Tables