Documentazione di Oracle Cloud Infrastructure

Registrazione delle modifiche alle partizioni logiche e viste materializzate

Descrive le informazioni sul framework di metadati LPCT (Logical Partition Change Tracking) e la riscrittura delle query con il monitoraggio delle modifiche della partizione logica in Autonomous Database.

Argomento padre: Autonomous Database per utenti esperti di Oracle Database

Informazioni su Logical Partition Change Tracking

Logical Partition Change Tracking (LPCT) traccia la staleness delle viste materializzate.

Tracciamento modifiche partizione logica consente di creare partizioni logiche nelle tabelle di base. Valuta lo stato delle tabelle di base per le singole partizioni logiche senza utilizzare un log delle viste materializzate o senza richiedere il partizionamento delle tabelle utilizzate nella vista materializzata.

Quando vengono aggiornate una o più tabelle di base dipendenti di una vista materializzata, una vista materializzata diventa STALE e non può essere utilizzata per la riscrittura delle query con la modalità di integrità applicata predefinita.

Logical Partition Change Tracking (LPCT) offre la possibilità di sfruttare le informazioni di partizionamento logico fornite dall'utente delle tabelle di base di una vista materializzata per un tracciamento più dettagliato a livello di partizione dei dati non più validi per scopi di aggiornamento e riscrittura. Mentre il classico Partitioning Change Tracking (PCT) si basa sul partizionamento fisico delle tabelle, LPCT non ha alcuna dipendenza dalle tabelle partizionate fisicamente; LPCT può essere utilizzato sia con le tabelle partizionate che con quelle non partizionate.

Il meccanismo di registrazione delle modifiche delle partizioni logiche utilizza i subset (partizioni) FRESH delle viste materializzate nonostante gli altri subset siano STALE. È possibile ottenere tempi di risposta più rapidi per le query utente poiché i risultati precalcolati nelle viste materializzate vengono utilizzati più spesso. Oltre ad aumentare l'usabilità delle viste materializzate, PCT e LPCT consentono anche l'aggiornamento incrementale delle viste materializzate senza la necessità di log delle viste materializzate; l'aggiornamento può essere sia ON DEMAND che ON COMMIT.

Simile a Partitioning Change Tracking (PCT), Logical Partition Change Tracking (LPCT) è associato a una tabella di base e può identificare con precisione le righe in una vista materializzata interessata dalle modifiche ai dati nella tabella di base, in base ai limiti di partizione logica definiti.

Per ulteriori informazioni, vedere Viste materializzate avanzate.

Uso del controllo delle modifiche logico alla partizione

Logical Partition Change Tracking (LPCT) suddivide logicamente una tabella utilizzando una colonna chiave e un metodo specificati.

La sintassi di registrazione delle modifiche alla partizione logica è analoga a quella delle partizioni fisiche. A differenza delle partizioni fisiche, che devono essere create come parte della creazione di tabelle, LPCT può essere liberamente specificato indipendentemente dalla creazione della tabella e dalla sua forma, consentendo una maggiore flessibilità per soddisfare le tue esigenze. La creazione LPCT è solo metadati.

Argomento padre: Registrazione delle modifiche della partizione logica e viste materializzate

Creazione di partizioni logiche - BNF

Descrive la sintassi per creare partizioni logiche BNF.

Di seguito è riportata la sintassi per creare le partizioni logiche BNF.

CREATE LOGICAL PARTITION TRACKING ON table_name
    PARTITION BY RANGE (partition_key)
    INTERVAL (interval_clause)
    (partition_specification);
  • Sono supportati solo i metodi di partizionamento logico RANGE e INTERVAL.
  • È supportata una sola colonna chiave di partizione logica.
  • Di seguito sono riportati i tipi di dati disponibili nella colonna chiave di partizione.
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

Scelta della colonna chiave di partizione logica

La chiave di partizionamento logico è specificata per definire i limiti di ciascuna partizione logica.

La chiave di partizione logica non è fisica, il che significa che le righe di tabella appartenenti a un intervallo di chiavi non sono separate in una partizione fisica separata. La tabella può essere non partizionata o partizionata in una chiave diversa dalla chiave di partizione logica. La chiave di partizione logica può essere scelta liberamente e i limiti della partizione possono essere resi flessibili.

Per scegliere una colonna chiave LCT (Logical Partition Change Tracking), è possibile prendere in considerazione una colonna in cluster, ovvero una colonna in cui i dati sono vicini al valore della colonna ordinato in base al valore, a cui viene fatto spesso riferimento nei predicati del filtro query. Per una colonna in cluster, è probabile che le partizioni meno logiche vengano interessate durante il caricamento dei dati. Ciò significa che è necessario aggiornare meno partizioni logiche STALE e più partizioni logiche FRESH sono pronte per essere utilizzate per le riscritture. Se una tabella è già partizionata, si consiglia di creare un LPCT utilizzando una colonna diversa dalla colonna chiave di partizione. LPCT offre vantaggi simili al Partitioning Change Tracking (PCT) e i vantaggi combinati non vengono massimizzati se il tracciamento dei dati viene eseguito sulla stessa colonna.

Aggiornamento delle viste materializzate mediante la registrazione delle modifiche alle partizioni logiche

Il meccanismo di tracciamento dello staleness di Logical Partition Change Tracking (LPCT) registra e consolida automaticamente le statistiche di modifica internamente in base alla chiave di partizione logica e al metodo di partizionamento specificati durante ogni modifica dei dati.

I dati di modifica adiacenti sono raggruppati in una partizione "logica". A differenza del Partitioning Change Tracking (PCT), che è legato ai limiti delle partizioni fisiche, lo schema LPCT offre flessibilità nella gestione e nel raggruppamento delle modifiche ai dati derivanti dai DML applicati alla tabella di base.

Durante i DML convenzionali e i carichi diretti, LPCT adotta lo stesso algoritmo che PCT utilizza per tracciare la staleness. Durante la riscrittura delle query, LPCT adotta lo stesso algoritmo utilizzato da PCT per calcolare il contenimento della riscrittura.

Quando una tabella viene partizionata logicamente utilizzando intervalli di chiavi, una vista materializzata definita nella tabella è idonea a utilizzare LPCT per la registrazione dello stato, l'aggiornamento e la riscrittura delle query, a condizione che la vista materializzata contenga la chiave di partizione logica.

Nota

Per LPCT sono supportati tutti i tipi di viste materializzate.

Riscrivi con viste materializzate mediante registrazione modifiche partizione logica

Utilizzando Logical Partition Change Tracking (LPCT), Oracle sa che una vista materializzata è STALE rispetto ad alcune partizioni logiche della tabella di base, ma FRESH rispetto ad altre porzioni.

Avendo le informazioni sullo spessore dei dati più fini delle tabelle di base, la vista materializzata associata verrebbe utilizzata più frequentemente a causa della riscrittura LPCT.

Oracle identifica e utilizza in modo trasparente il subset FRESH delle viste materializzate per la riscrittura delle query per rispondere a query complesse delle tabelle di base quando QUERY_REWRITE_INTEGRITY = ENFORCED |TRUSTED.

Se le righe delle viste materializzate sono parzialmente FRESH rispetto a tali partizioni logiche, è possibile che venga eseguita una riscrittura parziale per rispondere parzialmente alla query utilizzando la vista materializzata, ovvero le partizioni logiche FRESH, e parzialmente utilizzando la tabella di base, ovvero le partizioni logiche STALE.

Aggiornamento delle viste materializzate mediante la registrazione delle modifiche della partizione logica

L'aggiornamento LPCT (Logical Partition Change Tracking) può essere implementato utilizzando lo staleness dei dati a grana più fine per aggiornare in modo incrementale i sottoinsiemi STALE di una vista materializzata, eliminando l'aggiornamento completo costoso o l'aggiornamento rapido basato su log.

Se si specifica l'aggiornamento LPCT, vengono identificate le partizioni logiche STALE e le operazioni di aggiornamento mirato verranno eseguite solo a tali partizioni logiche.

Per richiamare l'aggiornamento utilizzando la registrazione delle modifiche della partizione logica, specificare ‘L’ o ‘l’ ("logico") come metodo di aggiornamento.

Ad esempio: execute DBMS_MVIEW.REFRESH(<materialized_view_name>,’L’);

Se si specifica REFRESH FORCE, viene scelto ed eseguito un aggiornamento FAST, se possibile, altrimenti viene eseguito un aggiornamento COMPLETE. Durante l'aggiornamento della vista materializzata FORCE, l'aggiornamento LPCT ha la stessa priorità dell'aggiornamento PCT (Partitioning Change Tracking).

Tracciamento modifiche partizione logica - Viste dizionario dati

Descrive le viste del dizionario dati per trovare informazioni sulle partizioni logiche.

Eseguire una query sulle viste del dizionario dati riportate di seguito per recuperare informazioni sulle partizioni logiche.

  • ALL_MVIEW_DETAIL_LOGICAL_PARTITION: questa vista visualizza le informazioni sulla freschezza delle viste materializzate, rispetto a una partizione logica di dettaglio LPCT, accessibile all'utente corrente. Vedere ALL_MVIEW_DETAIL_PARTITION.

  • DBA_MVIEW_DETAIL_ LOGICAL_PARTITION: visualizza le informazioni sulla freschezza per tutte le viste materializzate nel database rispetto a una partizione logica di dettaglio LPCT. Vedere DBA_MVIEW_DETAIL_PARTITION.

  • USER_MVIEW_DETAIL_ LOGICAL_PARTITION: visualizza le informazioni sulla freschezza per tutte le viste materializzate rispetto a una partizione logica dei dettagli LPCT di proprietà dell'utente corrente. Vedere USER_MVIEW_DETAIL_PARTITION.

Esempio: registrazione modifica partizione logica

Mostra i passi per utilizzare Logical Partition Change Tracking (LPCT) utilizzando una vista materializzata che contiene join e aggregati.

  1. Creare tabelle di base con partizioni di modifica logica.
    1. Creare la tabella MYSALES.
      CREATE TABLE mysales ( time_id DATE, prod_id NUMBER, cust_id NUMBER, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold
      NUMBER(3), amount_sold NUMBER(10,2))    
      PARTITION BY LIST (prod_id) 
      (PARTITION p1 VALUES (1,2,3),
      PARTITION p2 VALUES (4,5,6),
      PARTITION p3 VALUES (7,8,9),
      PARTITION p4 VALUES(DEFAULT));

      Viene creata la tabella MYSALES.

    2. Inserire i record nella tabella MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2007-06-05','yyyy-mm-dd'), 1, 2088, 189.98);
INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2007-07-05','yyyy-mm-dd'), 2, 1354, 12.99);
INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2007-09-05','yyyy-mm-dd'), 5, 2088, 189.98);
INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2007-10-05','yyyy-mm-dd'), 18, 2088, 42);
COMMIT;
      Questa operazione popola la tabella MYSALES.
    3. Creare la registrazione della partizione logica per la tabella MYSALES.
      CREATE LOGICAL PARTITION TRACKING ON mysales
  PARTITION BY RANGE (time_id)
  INTERVAL(NUMTOYMINTERVAL(2, 'YEAR'))
  (
   PARTITION p0 VALUES LESS THAN (TO_DATE('7-15-2005', 'MM-DD-YYYY')),
   PARTITION p1 VALUES LESS THAN (TO_DATE('7-15-2007', 'MM-DD-YYYY'))
  );
      In questo modo viene creato un tracciamento della partizione logica per la tabella MYSALES utilizzando la chiave TIME_ID.
    4. Creare la tabella MYCUSTOMERS.
      CREATE TABLE mycustomers (cust_id NUMBER, age NUMBER, gender CHAR(1), address VARCHAR(100));
      Viene creata la tabella MYCUSTOMERS.
    5. Inserire i record nella tabella MYCUSTOMERS.
      INSERT INTO mycustomers(cust_id, age, gender) VALUES (2088, 35, 'F');
INSERT INTO mycustomers(cust_id, age, gender) VALUES (1234, 54, 'M');
INSERT INTO mycustomers(cust_id, age, gender) VALUES (1354, 17, 'F');
INSERT INTO mycustomers(cust_id, age, gender) VALUES (6666, 15, 'F');
COMMIT;
      Questa operazione popola la tabella MYCUSTOMERS.
    6. Creare la registrazione della partizione logica per la tabella MYCUSTOMERS.
      CREATE LOGICAL PARTITION TRACKING ON mycustomers
 PARTITION BY RANGE (age) INTERVAL (20.5)
 (PARTITION m0 values less than (20));
      In questo modo viene creato un tracciamento della partizione logica per la tabella MYSALES utilizzando la chiave AGE.
  2. Creare una vista materializzata nella parte superiore delle tabelle con la registrazione delle modifiche alle partizioni logiche.
    1. Creare una vista materializzata nelle tabelle MYSALES e MYCUSTOMERS.
      CREATE MATERIALIZED VIEW sales_age_time
 REFRESH FAST
 ENABLE QUERY REWRITE
 AS SELECT SUM(s.amount_sold) amount_total, c.age, s.time_id
 FROM mysales s, mycustomers c
 WHERE s.cust_id = c.cust_id
 GROUP BY c.age, s.time_id;
      In questo modo viene creata la vista materializzata SALES_AGE_TIME.
    2. Eseguire una query sulla vista del dizionario dati DBA_MVIEW_DETAIL_LOGICAL_PARTITION.
      SELECT mview_name, DETAILOBJ_NAME, DETAIL_LOGICAL_PARTITION_NAME LPARTNAME,
 DETAIL_LOGICAL_PARTITION_NUMBER LPART#, FRESHNESS
 FROM DBA_MVIEW_DETAIL_LOGICAL_PARTITION 
 WHERE mview_name = 'SALES_AGE_TIME' 
 ORDER BY 1,2,3;

      Viene visualizzato l'output riportato di seguito.

      
MVIEW_NAME        DETAILOBJ_NAME     LPARTNAME      LPART#      FRESHNESS
---------------   ---------------    ----------     ----------  ---------
SALES_AGE_TIME    MYCUSTOMERS        M0             0 	      FRESH
SALES_AGE_TIME    MYSALES            P0             0 	      FRESH
SALES_AGE_TIME    MYSALES            P1             1 	      FRESH
    3. Utilizzare EXPLAIN_MVIEW per valutare le funzionalità di aggiornamento e riscrittura correlate alla partizione logica.
      EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('sales_age_time');

SELECT CAPABILITY_NAME, RELATED_TEXT, POSSIBLE
FROM MV_CAPABILITIES_TABLE
WHERE MVNAME = 'SALES_AGE_TIME' AND CAPABILITY_NAME LIKE '%LPT%'
ORDER BY 1, 2;

      Viene visualizzato l'output riportato di seguito.

      
CAPABILITY_NAME               RELATED_TEXT                 POSSIBLE
-------------------------     -------------------------    –--------------
LPT                                                         Y
LPT_TABLE                     MYCUSTOMERS                   Y
LPT_TABLE                     MYSALES                       Y
LPT_TABLE_REWRITE             MYCUSTOMERS                   Y
LPT_TABLE_REWRITE             MYSALES                       Y
REWRITE_LPT                                                 Y
REFRESH_FAST_LPT                                            Y
  3. Osservare l'impatto dei DML sulla vista materializzata.
    1. Introdurre una nuova partizione logica nella tabella MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES
      (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      Viene introdotta una nuova partizione (partizione #6) nella tabella MYSALES.
    2. Introdurre una nuova partizione logica nella tabella MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      Viene introdotta una nuova partizione (partizione #6) nella tabella MYSALES.
    3. Introdurre una nuova partizione logica nella tabella MYCUSTOMERS.
      INSERT INTO mycustomers(cust_id, age, gender) VALUES (1399, 80, 'F');
      Viene introdotta una nuova partizione (partizione #3) nella tabella MYCUSTOMERS.
    4. Introdurre una nuova partizione logica nella tabella MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-09','yyyy-mm-dd'), 99, 1997, 79.9);
      Viene introdotta una nuova partizione (partizione #7) nella tabella MYSALES.
    5. Introdurre una nuova partizione logica nella tabella MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2010-02-09','yyyy-mm-dd'), 110, 1997, 108.98);
COMMIT;
      Viene introdotta una nuova partizione (partizione #2) nella tabella MYSALES.
    6. Eseguire una query sulla vista del dizionario dati DBA_MVIEW_DETAIL_LOGICAL_PARTITION.
      SELECT mview_name, DETAILOBJ_NAME, DETAIL_LOGICAL_PARTITION_NAME LPARTNAME,
 DETAIL_LOGICAL_PARTITION_NUMBER LPART#, FRESHNESS
 FROM DBA_MVIEW_DETAIL_LOGICAL_PARTITION 
 WHERE mview_name = 'SALES_AGE_TIME' 
 ORDER BY 1,2,3;

      Ora, mostra il seguente output.

      
MVIEW_NAME        DETAILOBJ_NAME     LPARTNAME      LPART#      FRESHNESS
---------------   ---------------    ----------     ----------  ---------
SALES_AGE_TIME    MYCUSTOMERS        M0             0             FRESH
SALES_AGE_TIME    MYCUSTOMERS        SYS_88904P3    3             STALE
SALES_AGE_TIME    MYSALES            P1             0             FRESH
SALES_AGE_TIME    MYSALES            P1             1             FRESH
SALES_AGE_TIME    MYSALES            SYS_88899P3    2             STALE
SALES_AGE_TIME    MYSALES            SYS_88899P7    6             STALE
    7. Eseguire la riscrittura LPCT nel subset di lpart #1 nella tabella MYSALES e lpart #0 nella tabella MYCUSTOMERS.
      DELETE FROM rewrite_table;
DECLARE    
 stmt varchar2(2000) := q'#select sum(s.amount_sold) amount_total,
 c.age, s.time_id
 FROM mysales s, mycustomers c
 WHERE s.cust_id = c.cust_id
 AND s.time_id < TO_DATE ('07-07-2007', 'MM-DD-YYYY')
 AND c.age < 18
 GROUP BY c.age, s.time_id#';
BEGIN 
 dbms_mview.explain_rewrite (stmt,'sales_age_time');
END;
/
SELECT mv_name, sequence, pass, message FROM rewrite_table;
    8. Eseguire la query REWRITE_TABLE per verificare le riscritture.
      SELECT mv_name, sequence, pass, message FROM rewrite_table;
    9. Eseguire la query seguente.
      SELECT SUM(s.amount_sold) amount_total,
 c.age, s.time_id
 FROM mysales s, mycustomers c
 WHERE s.cust_id = c.cust_id
 AND s.time_id < TO_DATE ('07-07-2007', 'MM-DD-YYYY')
 AND c.age < 18
 GROUP BY c.age, s.time_id;
    10. Visualizzare l'explain plan per la query precedente per verificare le riscritture.
      SELECT * FROM TABLE(dbms_xplan.display_cursor);
      PLAN_TABLE_OUTPUT
__________________________________________________
SQL_ID  ampuzk8tbp6df, child number 0
-------------------------------------
SELECT SUM(s.amount_sold) amount_total,
c.age, s.time_id
FROM mysales s, mycustomers c
WHERE s.cust_id = c.cust_id
AND s.time_id < TO_DATE ('07-07-2007', 'MM-DD-YYYY')
AND c.age < 18
GROUP BY c.age, s.time_id;

Plan hash
        value: 3902795718 
        -----------------------------------------------------------------------------------------------
        | Id  | Operation                    | Name           | Rows  | Bytes | Cost
        (%CPU)| Time     
        |-----------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT             |                |      
          |       |     2 (100)|         
        ||*  1 |  MAT_VIEW
        REWRITE ACCESS FULL| SALES_AGE_TIME |     1 |    35 |    
          2   (0)| 00:00:01 
        |----------------------------------------------------------------------------------------------- 
        Predicate Information (identified by operation id):
        ---------------------------------------------------    
        1 - filter(("SALES_AGE_TIME"."TIME_ID"<TO_DATE('2007-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALES_AGE_TIME"."AGE"<18)) 
Note
-----   
   - dynamic statistics used: dynamic sampling (level=2)  
26 rows selected.
  4. Utilizza LPCT per l'aggiornamento incrementale.
    1. Eseguire il codice seguente per eseguire l'aggiornamento LPCT.
      EXECUTE DBMS_MVIEW.REFRESH('SALES_AGE_TIME', 'L');
    2. Verificare l'aggiornamento utilizzando la query seguente.
      SELECT mview_name, DETAILOBJ_NAME, DETAIL_LOGICAL_PARTITION_NAME LPARTNAME, DETAIL_LOGICAL_PARTITION_NUMBER LPART#, FRESHNESS 
 FROM DBA_MVIEW_DETAIL_LOGICAL_PARTITION
 WHERE mview_name = 'SALES_AGE_TIME' 
 ORDER BY 1,2,3;