Migliora prestazioni query con cache tabella esterna

Informazioni sulla cache delle tabelle esterne in Autonomous Database

Una cache tabella esterna è un'area di storage in Autonomous Database che memorizza i dati da una tabella esterna.

I dati esterni non sono gestiti dal database; tuttavia, è possibile utilizzare le tabelle esterne per eseguire query sui dati al di fuori del database. Le query sulle tabelle esterne non saranno veloci quanto le query sulle tabelle di database perché ogni volta che si accede ai dati è necessario recuperarli dai file esterni memorizzati nell'area di memorizzazione degli oggetti.

La funzione di cache delle tabelle esterne consente di inserire nella cache i dati ad accesso frequente da tabelle esterne all'interno di Autonomous Database, senza richiedere modifiche alle applicazioni che accedono ai dati, offrendo così un accesso più rapido alle tabelle esterne.

Di seguito sono riportati alcuni dei vantaggi derivanti dall'utilizzo della cache delle tabelle esterne.

Prestazioni migliorate: le query sono diverse volte più veloci per i dati esterni ad accesso frequente, ideali per dashboard, report e strumenti analitici che accedono regolarmente agli stessi dati.
100% trasparente: il meccanismo di inserimento nella cache è completamente trasparente; le applicazioni possono trarre vantaggio da una maggiore velocità senza richiedere modifiche alle query, ai dashboard o alle applicazioni.
Costi cloud inferiori: in un'applicazione multi-cloud, l'inserimento nella cache riduce la necessità di recuperi ripetuti dei dati esterni dallo storage remoto, riducendo così i costi di uscita dei dati associati all'accesso ai dati tra region o cloud.

È possibile creare cache di tabelle esterne per tabelle partizionate e non partizionate create nei seguenti tipi di file:

Parquet
ORC
AVRO
Tabelle iceberg

Per ulteriori informazioni, vedere Query di dati esterni.

Prerequisiti

Elenca i prerequisiti per la creazione della cache delle tabelle esterne.

Il privilegio EXECUTE deve esistere sul pacchetto DBMS_EXT_TABLE_CACHE. Si noti che è possibile creare una cache di tabella esterna solo nello schema personale e per le tabelle esterne di cui si è proprietari.
È necessario disporre di una quota di spazio appropriata allocata per lo schema per garantire una capacità di storage sufficiente per i dati della cache.
È necessario disporre delle credenziali per accedere ai file di tabella esterni memorizzati nell'area di memorizzazione degli oggetti. Non è necessario creare le credenziali se si abilitano le credenziali del principal risorsa per accedere all'area di memorizzazione degli oggetti Oracle Cloud Infrastructure.

Limitazioni

Elenca note e limitazioni importanti sull'uso della cache delle tabelle esterne in un Autonomous Database.

È possibile creare una cache di tabella esterna solo nello schema personale e per le tabelle esterne di cui si è proprietari.
Una cache di tabella esterna può contenere file solo da una singola tabella esterna.
Non è possibile concedere privilegi su una cache tabella esterna ed è possibile accedere ai dati solo dalla cache tabella esterna di cui si è proprietari.
Quando un file viene eliminato dall'area di memorizzazione degli oggetti, i dati corrispondenti nella cache vengono contrassegnati come non validi e non è possibile accedervi.

Crea cache tabella esterna

Eseguire DBMS_EXT_TABLE_CACHE.CREATE_CACHE per creare una cache di tabella esterna.

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 la cache delle tabelle esterne.

Utilizzare DBMS_EXT_TABLE_CACHE.CREATE_CACHE per creare la cache di tabelle esterne per lo schema. Ad esempio:
```
BEGIN
    DBMS_EXT_TABLE_CACHE.CREATE_CACHE (      
      owner          => 'SALES',
      table_name     => 'STORE_SALES',
      partition_type => 'PATH');                                                                 
END;                                                                 
/
```
In questo modo viene creata una cache per la tabella STORE_SALES nello schema SALES. STORE_SALES è una tabella esterna che punta ai dati memorizzati nell'area di memorizzazione degli oggetti.

Il parametro owner specifica il nome dello schema. In questo esempio viene creata una cache di tabella esterna per l'utente SALES.

Il parametro partition_type specifica il metodo di partizionamento da utilizzare per la cache delle tabelle esterne. In questo esempio la cache viene partizionata utilizzando la colonna FILE$PATH. FILE$PATH è una colonna invisibile che specifica il testo del percorso del file fino all'inizio del nome dell'oggetto.
Per ulteriori informazioni, vedere Colonne dei metadati delle tabelle esterne e CREATE_CACHE Procedure.
Quando si crea per la prima volta una cache di tabella esterna, i relativi metadati vengono memorizzati nel dizionario dati, ma non viene allocato spazio per i dati della cache. È possibile eseguire una query sulla vista USER_EXTERNAL_TAB_CACHES per verificare la creazione della cache.
```
SELECT external_table_name, cached, disabled 
  FROM user_external_tab_caches;
```
Per ulteriori informazioni, vedere Viste DBA_EXTERNAL_TAB_CACHES e USER_EXTERNAL_TAB_CACHES.
Utilizzare la procedura DBMS_EXT_TABLE_CACHE.VALIDATE per convalidare la cache di una tabella esterna. Si è verificato un errore se la tabella esterna di riferimento non è stata trovata nel database.
Ad esempio:
```
BEGIN
  DBMS_EXT_TABLE_CACHE.VALIDATE (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES',
    raise_errors  => TRUE);
END;
/
```
Per ulteriori informazioni, vedere Procedura CONVALIDO.

Popola file nella cache tabella esterna

Mostra esempi per popolare i file nella cache delle tabelle esterne.

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.

A seconda della quota di spazio allocata per lo schema, Oracle tenta di popolare i file nella cache. Se viene raggiunto il limite di quota assegnato, Oracle interrompe l'inserimento dei dati nei file a meno che non venga allocato lo spazio richiesto.

La cache delle tabelle esterne non viene aggiornata automaticamente. Per aggiornare la cache quando viene modificato un file nell'area di memorizzazione degli oggetti, è necessario ripopolare il file.

Quando un file viene eliminato dall'area di memorizzazione degli oggetti, i dati inseriti nella cache corrispondenti diventano immediatamente non validi e non possono essere recuperati.

Aggiungi tabella a cache tabella esterna

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

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

È possibile utilizzare le seguenti procedure per aggiungere uno o più file alla cache delle tabelle esterne:

ADD_FILE: consente di aggiungere un singolo file nella cache.
ADD_BY_LIKE: consente di aggiungere uno o più file specificati in base ai filtri del percorso specificati.
ADD_LATEST_FILES: per aggiungere uno o più file in base all'intervallo di tempo specificato.

Esempi

Utilizzare la procedura DBMS_EXT_TABLE_CACHE.ADD_FILE per inserire un singolo file nella cache delle tabelle esterne. Ad esempio:

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.

Utilizzare la procedura DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE per popolare uno o più file nella cache delle tabelle esterne. Ad esempio:

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

In questo esempio vengono inseriti nella cache i dati dei file salesdata.parquet e salesdata1.parquet, ignorando tutti i file esistenti già 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.

Utilizzare la procedura DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES per popolare uno o più file in base alla data dell'ultima modifica nella cache delle tabelle esterne. Ad esempio:

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.

Eseguire una query sulle viste del dizionario dati riportate di seguito per elencare i file inseriti nella cache della tabella esterna.

Elimina file dalla cache tabella esterna

Mostra esempi per eliminare i file dalla cache delle tabelle esterne.

È possibile rimuovere tutti i file dalla cache oppure specificare le condizioni di filtro per eliminare uno o più file dalla cache. Ad esempio, è possibile filtrare i file in base al nome o a un intervallo di tempo specifico.

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

È possibile utilizzare le seguenti procedure per eliminare uno o più file dalla cache delle tabelle esterne:

DROP_FILE: consente di eliminare un singolo file dalla cache.
DROP_BY_LIKE: consente di eliminare uno o più file dalla cache in base ai filtri del percorso specificati.
RETIRE_FILES: consente di eliminare uno o più file dalla cache in base all'intervallo specificato.

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 rilasciare 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.

Disabilita e abilita cache tabella esterna

Mostra esempi per disabilitare e abilitare la 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.

Esempio:

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.

Elimina cache tabella esterna

Mostra un esempio per eliminare la cache della tabella esterna.

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.

Eseguire una query sulla vista USER_EXTERNAL_TAB_CACHES per verificare che la cache sia stata eliminata. Ad esempio:

SELECT external_table_name, cached
  FROM user_external_tab_caches;

Per ulteriori informazioni, vedere Viste DBA_EXTERNAL_TAB_CACHES e USER_EXTERNAL_TAB_CACHES.

Visualizza informazioni cache tabella esterna

Autonomous Database fornisce viste che consentono di monitorare la cache delle tabelle esterne.

Visualizza	Descrizione
Visualizzazioni DBA_EXTERNAL_TAB_CACHES e USER_EXTERNAL_TAB_CACHES	Fornisce informazioni su tutte le cache delle tabelle esterne nel database o sulle cache delle tabelle esterne di un utente.
ALL_EXTERNAL_TAB_CACHE_LOCATIONS	Fornisce informazioni sui file nello storage cloud accessibili all'utente corrente e appartenenti alle tabelle esterne inserite nella cache.
USER_EXTERNAL_TAB_CACHE_LOCATIONS	Fornisce informazioni sui file nello storage cloud di proprietà dell'utente corrente e appartenenti alle tabelle esterne inserite nella cache. In questa vista non viene visualizzata la colonna `OWNER`.

Imposta preferenze di dimensionamento facoltative per cache tabella esterna

È possibile utilizzare la procedura DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY per impostare le preferenze di dimensionamento e i limiti di quota di spazio nella cache delle tabelle esterne.

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 delle quote 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.

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

Quando si definiscono MAX_CACHE_SIZE, MAX_CACHE_PERCENT e la quota di spazio, MAX_CACHE_PERCENT ha la precedenza su MAX_CACHE_SIZE.
Quando è definito solo MAX_CACHE_SIZE e non è definita MAX_CACHE_PERCENT o quota di spazio, ha la precedenza MAX_CACHE_SIZE.
Quando viene definita solo la quota spazio e non vengono definiti MAX_CACHE_SIZE e MAX_CACHE_PERCENT, la quota dimensione cache viene impostata automaticamente sul 10% della quota totale dello schema.
Se MAX_CACHE_SIZE, MAX_CACHE_PERCENT o la quota spazio non è definita, la quota spazio cache viene impostata automaticamente su UNLIMITED.

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.

Colonne metadati tabella esterna

I metadati della tabella esterna consentono di determinare la provenienza dei dati quando si esegue una query.

Le tabelle esterne create con DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE o DBMS_CLOUD.CREATE_HYBRID_PART_TABLE includono due colonne invisibili file$path e file$name. Queste colonne consentono di identificare il file da cui proviene un record.

file$path: specifica il testo del percorso del file fino all'inizio del nome dell'oggetto.
file$name: specifica il nome dell'oggetto, incluso tutto il testo che segue il "/" finale.

Ad esempio:

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

Per ulteriori informazioni sulle colonne invisibili, vedere Colonne invisibili.