Documentazione dell'infrastruttura Oracle Cloud

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 registrazione delle modifiche alla partizione logica in Autonomous Database.

Argomento principale: Autonomous Database per utenti esperti di Oracle Database

Informazioni sul tracciamento delle modifiche alle partizioni logiche

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

Logical Partition Change Tracking consente di creare partizioni logiche sulle tabelle di base. Valuta la staleness delle tabelle di base per le singole partizioni logiche senza utilizzare un log delle viste materializzate o richiedere la partizionamento di una qualsiasi 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 in base alla 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 a livello di partizione più dettagliato dei dati non più validi sia a scopo di aggiornamento che di riscrittura. Mentre il Partitioning Change Tracking (PCT) classico si basa sul partizionamento fisico delle tabelle, LPCT non ha alcuna dipendenza dalle tabelle che vengono partizionate fisicamente; LPCT può essere utilizzato sia con tabelle partizionate che non partizionate.

Il meccanismo Logical Partition Change Tracking utilizza i sottoinsiemi (partizioni) FRESH delle viste materializzate nonostante altri sottoinsiemi 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 di Logical Partition Change Tracking

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

La sintassi di creazione di Logical Partition Change Tracking è analoga alle partizioni fisiche. A differenza delle partizioni fisiche, che devono essere create come parte della creazione della tabella, LPCT può essere specificato liberamente indipendentemente dalla creazione della tabella e dalla sua forma, consentendo una maggiore flessibilità per soddisfare le vostre esigenze. La creazione LPCT è solo metadati.

Argomento padre: Registrazione delle modifiche alle partizioni logiche e viste materializzate

Creazione di partizioni logiche - BNF

Descrive la sintassi per creare partizioni logiche BNF.

Di seguito è riportata la sintassi per creare 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 di chiavi di partizione logica.
  • La colonna della chiave di partizione può essere dei seguenti tipi di dati:
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

Scelta della colonna di chiavi della partizione logica

La chiave di partizionamento logico viene specificata per definire i limiti di ogni partizione logica.

La chiave di partizione logica non è fisica. Ciò 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 su una chiave diversa dalla chiave di partizione logica. La chiave di partizione logica può essere scelta liberamente e i limiti di partizione possono essere resi flessibili.

Per scegliere una colonna chiave LPCT (Logical Partition Change Tracking), è possibile considerare una colonna in cluster, ovvero una colonna in cui i dati sono vicini all'ordinamento in base al valore della colonna, a cui viene fatto spesso riferimento nei predicati del filtro query. Per una colonna in cluster, è probabile che durante i caricamenti dei dati vengano interessate meno partizioni logiche, pertanto è 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 a Partitioning Change Tracking (PCT) e i vantaggi combinati non vengono massimizzati se il monitoraggio dei dati viene eseguito sulla stessa colonna.

Freschezza delle viste materializzate mediante il tracciamento delle modifiche alle partizioni logiche

Il meccanismo di tracciamento dello stallo LPCT (Logical Partition Change Tracking) registra e consolida automaticamente le statistiche delle modifiche 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 di Partitioning Change Tracking (PCT), che è legato ai limiti di partizione fisica, lo schema LPCT offre flessibilità nella gestione e nel raggruppamento delle modifiche ai dati derivanti da DML applicati alla tabella di base.

Durante i DML e i carichi diretti convenzionali, LPCT adotta lo stesso algoritmo utilizzato da PCT per monitorare la stanchezza. Durante le riscritture delle query, LPCT adotta lo stesso algoritmo utilizzato da PCT per calcolare il contenimento delle riscritture.

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

Nota

Tutti i tipi di viste materializzate sono supportati per LPCT.

Riscrivi con viste materializzate mediante tracciamento 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 sulla staleness 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 di 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 alla query utilizzando parzialmente la vista materializzata, ovvero le partizioni logiche FRESH e utilizzando parzialmente la tabella di base, ovvero le partizioni logiche STALE.

Aggiornamento delle viste materializzate mediante tracciamento modifiche partizione logica

L'aggiornamento LPCT (Logical Partition Change Tracking) può essere implementato utilizzando la scalabilità dei dati con filtro per aggiornare in modo incrementale i sottoinsiemi STALE di una vista materializzata, eliminando il costoso aggiornamento completo o l'aggiornamento rapido basato su log.

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

Per richiamare l'aggiornamento utilizzando la registrazione delle modifiche alla 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 e eseguito, se possibile, un aggiornamento FAST oppure 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).

Registrazione delle modifiche alla partizione logica - Viste del 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 dettagliata LPCT, accessibile all'utente corrente. Per ulteriori informazioni, vedere ALL_MVIEW_DETAIL_PARTITION.

  • DBA_MVIEW_DETAIL_ LOGICAL_PARTITION: visualizza le informazioni sulla freschezza di tutte le viste materializzate nel database rispetto a una partizione logica dettagliata LPCT. Per ulteriori informazioni, vedere DBA_MVIEW_DETAIL_PARTITION.

  • USER_MVIEW_DETAIL_ LOGICAL_PARTITION: visualizza le informazioni sulla freschezza di tutte le viste materializzate rispetto a una partizione logica dettagliata LPCT di proprietà dell'utente corrente. Per ulteriori informazioni, vedere USER_MVIEW_DETAIL_PARTITION.

Esempio: tracciamento modifiche partizione logica

Mostra la procedura per utilizzare Logical Partition Change Tracking (LPCT) utilizzando una vista materializzata contenente 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;
      In questo modo viene popolata la tabella MYSALES.
    3. Creare il tracciamento delle partizioni logiche 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 logico della partizione 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;
      In questo modo viene popolata la tabella MYCUSTOMERS.
    6. Creare il tracciamento delle partizioni logiche 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 logico della partizione per la tabella MYSALES utilizzando la chiave AGE.
  2. Crea una vista materializzata sopra le tabelle con tracciamento 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;
      Viene creata la vista materializzata SALES_AGE_TIME.
    2. Eseguire una query sulla vista 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;

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

      Mostra 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 n. 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 n. 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 n. 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 n. 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 n. 2) nella tabella MYSALES.
    6. Eseguire una query sulla vista 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 su un sottoinsieme di lpart #1 nelle tabelle MYSALES e lpart #0 nelle tabelle 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 una query su REWRITE_TABLE per verificare le riscritture.
      SELECT mv_name, sequence, pass, message FROM rewrite_table;
    9. Eseguire la seguente query.
      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;