Usa cache tabella esterna per migliorare le prestazioni per le tabelle esterne

Quando viene creata, la cache è inizialmente vuota e abilitata per la popolazione. La dimensione della cache aumenta ogni volta che un file viene aggiunto, a seconda dei limiti di quota di spazio definiti per lo schema, fino a quando non raggiunge i limiti assegnati. Per ulteriori informazioni, vedere Impostazione delle preferenze di dimensionamento facoltative per le cache basate su criteri.

Dopo aver creato una cache, è possibile inserire i file nella cache. Il popolamento dei file carica il contenuto dei file di tabella esterni specificati nella cache. È possibile scegliere di popolare tutti i file di una tabella, una percentuale specifica della tabella o specificare una condizione di filtro per limitare i file da popolare. Ad esempio, è possibile filtrare i file in base ai nomi o a un intervallo di date.

Aggiungi tabella a cache tabella esterna

Utilizzare DBMS_EXT_TABLE_CACHE.ADD_TABLE per popolare un'intera tabella o una determinata percentuale della tabella esterna nella cache.

Esempi

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

Questo esempio tenta di popolare la tabella STORE_SALES nella cache, saltando tutti i file esistenti che sono già stati popolati.

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

Questo esempio tenta di popolare l'80% della tabella STORE_SALES nella cache, saltando tutti i file esistenti che sono già stati popolati.

Il parametro percent_files è facoltativo; se non si specifica questo parametro, l'intera tabella viene popolata nella cache.

Per ulteriori informazioni, vedere ADD_TABLE Procedure.

Aggiungi file a cache tabella esterna

Esempi

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

In questo esempio vengono inseriti nella cache i dati del file salesdata.parquet.

Questo esempio salta l'inserimento del file nella cache se il file specificato esiste nella cache e non è stato modificato dall'ultima inserimento del file nella cache.

Per ulteriori informazioni, vedere ADD_FILE Procedure.

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%.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/customer%.parquet"]'
);
END;
/

Questo esempio popola tutti i file con nomi che iniziano con sales o customer, escludendo i file che sono già stati popolati.

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

In questo esempio vengono inseriti nella cache i file sales_data1.parquet e sales_data2.parquet.

In questo esempio, il carattere '#' è definito come carattere di escape. Il carattere '_' che segue '#' viene considerato come carattere di sottolineatura letterale, non come carattere jolly che corrisponde a un singolo carattere.

Per ulteriori informazioni, vedere ADD_BY_LIKE Procedure.

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

Il parametro since specifica l'intervallo di tempo. Solo i file modificati negli ultimi sette (7) giorni possono essere inseriti nella cache.

Il parametro max_files limita il numero di file che è possibile inserire nella cache. In questo esempio vengono inseriti solo cinque (5) file.

Il parametro force forza la sovrascrittura dei file specificati nella cache anche se i file non sono stati modificati.

Per ulteriori informazioni, vedere ADD_LATEST_FILES Procedure.

Cancella cache tabella esterna

Utilizzare DBMS_EXT_TABLE_CACHE.CLEAR per eliminare tutti i file dalla cache delle tabelle esterne. Ad esempio:

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

Questo esempio elimina tutti i file dalla cache STORE_SALES e disalloca tutto lo spazio utilizzato dai file rimossi.

Per ulteriori informazioni, vedere Procedura CLEAR.

Elimina file dalla cache tabella esterna

Esempi

Utilizzare DBMS_EXT_TABLE_CACHE.DROP_FILE per eliminare un file dalla cache delle tabelle esterne. Ad esempio:

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

In questo esempio il file salesdata.parquet viene eliminato dalla cache e viene disallocato tutto lo spazio utilizzato dal file rimosso.

Per ulteriori informazioni, vedere DROP_FILE Procedure.

Utilizzare DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE per eliminare uno o più file in base al parametro path_filters. Ad esempio:

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

Questo esempio elimina i file salesdata.parquet e salesdata1.parquet dalla cache e disalloca tutto lo spazio utilizzato dai file rimossi.

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

Questo esempio elimina i file sales#_data1 e sales#_data2 dalla cache e disalloca tutto lo spazio utilizzato dai file rimossi.

In questo esempio, il carattere '#' è definito come carattere di escape. Il carattere '_' che segue '#' viene considerato come carattere di sottolineatura letterale, non come carattere jolly che corrisponde a un singolo carattere.

Per ulteriori informazioni, vedere DROP_BY_LIKE Procedure.

Utilizzare DBMS_EXT_TABLE_CACHE.RETIRE_FILES per eliminare uno o più file in base all'intervallo specificato. Ad esempio:

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

Questo esempio elimina i file che risalgono a più di trenta (30) giorni dalla cache e disalloca tutto lo spazio utilizzato dai file rimossi.

Per ulteriori informazioni, vedere RETIRE_FILES Procedure.

Gli esempi precedenti rimuovono uno o più file dalla cache mantenendo la cache. È possibile caricare di nuovo i file nella cache quando necessario. Per ulteriori informazioni, vedere Inserimento dei file nella cache delle tabelle esterne.

Eseguire DBMS_EXT_TABLE_CACHE.DISABLE per disabilitare la cache delle tabelle esterne dal database. La disabilitazione di una cache non elimina i dati dalla cache; invece, la cache è contrassegnata come DISABLED e l'ottimizzatore non può utilizzare la cache per la riscrittura delle query.

Esempio

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

In questo esempio viene disabilitata la cache STORE_SALES.

Per ulteriori informazioni, vedere Procedura DISABILE.

Dopo aver disabilitato una cache di tabella esterna, utilizzare DBMS_EXT_TABLE_CACHE.ENABLE per abilitare la cache.

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

Questo esempio abilita la cache STORE_SALES.

Per ulteriori informazioni, vedere Procedura ENABLE.

Eseguire DBMS_EXT_TABLE_CACHE.DROP_CACHE per eliminare una cache di tabella esterna. La procedura DBMS_EXT_TABLE_CACHE.DROP_CACHE rimuove la cache delle tabelle esterne specificata dal database e rilascia lo spazio di memorizzazione associato alla cache.

Esempio:

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

In questo esempio viene eliminata la cache STORE_SALES dallo schema SALES.

L'eliminazione di una cache comporta la rimozione dei metadati dal dizionario dati e l'eliminazione di tutti i dati inseriti nella cache.

Per ulteriori informazioni, vedere DROP_CACHE Procedure.

SELECT external_table_name, cached
  FROM user_external_tab_caches;

Per ulteriori informazioni, vedere Viste DBA_EXTERNAL_TAB_CACHES e USER_EXTERNAL_TAB_CACHES.

Per impostazione predefinita, la cache delle tabelle esterne è disabilitata per un utente. Per abilitare e creare la cache delle tabelle esterne, utilizzare la procedura DBMS_EXT_TABLE_CACHE.CREATE_CACHE. La cache viene creata nello schema predefinito ed eredita tutti i limiti di quota di spazio definiti per lo schema. Tuttavia, è anche possibile utilizzare la procedura DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY per definire le quote di spazio per la cache delle tabelle esterne. Per impostare i limiti di quota di spazio, utilizzare i parametri PROPERTY_NAME e PROPERTY_VALUE della procedura DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY.

Il parametro PROPERTY_NAME accetta i valori MAX_CACHE_SIZE e MAX_CACHE_PERCENT. La proprietà MAX_CACHE_SIZE specifica la dimensione totale della cache esterna in byte. La proprietà MAX_CACHE_PERCENT specifica la dimensione totale della cache esterna come percentuale della quota dell'utente specificato.

Esempi

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

In questo esempio la preferenza di inserimento nella cache viene impostata su MAX_CACHE_PERCENT per lo schema SALES.

property_value è il 50%, che specifica che la quota di spazio della cache per lo schema SALES è un massimo del 50% della quota di spazio totale definita per SALES.

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

In questo esempio la preferenza di inserimento nella cache viene impostata su MAX_CACHE_SIZE per lo schema SALES.

property_value è 5368709120, che specifica che la dimensione massima della cache per lo schema SALES è fino a 5 GB.

Per ulteriori informazioni, vedere SET_USER_PROPERTY Procedure e CREATE_CACHE Procedure.

Utilizzare DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY per recuperare le proprietà delle dimensioni della cache.

Esempio:

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

Per ulteriori informazioni, vedere GET_USER_PROPERTY Function.

Per impostare le proprietà delle dimensioni della cache, tenere presente l'ordine di precedenza riportato di seguito.

Per impostazione predefinita, l'inserimento automatico nella cache è disabilitato. È possibile abilitare l'inserimento automatico nella cache per le tabelle esterne a livello globale per tutti gli utenti del database o per un utente specifico. Dopo aver abilitato l'inserimento automatico nella cache, il database crea automaticamente le cache delle tabelle esterne in base alle statistiche di utilizzo delle query delle tabelle esterne disponibili in SQL Tuning Set (STS). Tutte le nuove cache create vengono contrassegnate come AUTO. Le cache esistenti continuano a seguire le impostazioni di gestione della cache basate su criteri.

Quando si abilita l'inserimento automatico nella cache, è possibile specificare facoltativamente l'ambito dell'aggiornamento della cache, ovvero le cache idonee per l'aggiornamento durante ogni ciclo di aggiornamento, e definire la finestra temporale consentita per completare il processo di aggiornamento.

Utilizzare DBMS_CACHE.SET_GLOBAL_PROPERTY o DBMS_CACHE.SET_USER_PROPERTY per impostare le proprietà di inserimento nella cache AUTO, incluse le proprietà per abilitare l'inserimento automatico nella cache a livello globale o per un utente specificato, rispettivamente. Le preferenze di inserimento nella cache a livello utente hanno la precedenza sulle preferenze di inserimento nella cache globali. Utilizzare i parametri PROPERTY_NAME e PROPERTY_VALUE di queste procedure per impostare i limiti di quota spazio per le cache delle tabelle esterne.

A seconda della quota di spazio specificata, Oracle crea le cache e tenta di popolare tutti i dati della tabella esterna nella cache. Il processo di popolamento non riesce se la dimensione della cache non è sufficiente per contenere l'intero dato della tabella esterna.

In alternativa, è anche possibile utilizzare la procedura DBMS_CACHE.REFRESH per eseguire un aggiornamento su richiesta per tutte le cache di un utente specificato.

Durante ogni ciclo di aggiornamento, le cache non valide e le cache a cui si accede meno di recente vengono eliminate dal database. In alternativa, è possibile utilizzare la procedura DBMS_CACHE.CLEAR per eliminare tutte le cache per un utente specificato.