Documentation Oracle Cloud Infrastructure

Suivi des modifications de partition logique et Vues matérialisées

Décrit les informations relatives à la structure de métadonnées LPCT (Logical Partition Change Tracking) et à la réécriture de requête avec le suivi des modifications de partition logique dans Autonomous Database.

Rubrique parent : Autonomous Database pour les utilisateurs d'Oracle Database expérimentés

A propos du suivi des modifications de partition logique

Le suivi des modifications de partition logique (LPCT) suit la persistance des vues matérialisées.

Le suivi des modifications de partition logique permet de créer des partitions logiques sur des tables de base. Il évalue la persistance des tables de base pour les partitions logiques individuelles sans utiliser de journal de vues matérialisées ni exiger le partitionnement des tables utilisées dans la vue matérialisée.

Lorsqu'une ou plusieurs tables de base dépendantes d'une vue matérialisée sont mises à jour, une vue matérialisée devient STALE et ne peut pas être utilisée pour la réécriture de requête sous le mode d'intégrité imposée par défaut.

Le suivi des modifications de partition logique (LPCT) permet d'exploiter les informations de partitionnement logique fournies par l'utilisateur des tables de base d'une vue matérialisée pour un suivi plus détaillé des données périmées au niveau de la partition à des fins d'actualisation et de réécriture. Alors que le suivi classique des modifications de partitionnement (PCT) repose sur le partitionnement physique des tables, le LPCT ne dépend pas des tables partitionnées physiquement. Le LPCT peut être utilisé avec les tables partitionnées et non partitionnées.

Le mécanisme de suivi des modifications de partition logique utilise les sous-ensembles (partitions) FRESH des vues matérialisées alors que les autres sous-ensembles sont STALE. Des temps de réponse plus rapides peuvent être obtenus pour les requêtes utilisateur, car les résultats précalculés dans les vues matérialisées sont utilisés plus souvent. Outre l'augmentation de la facilité d'utilisation des vues matérialisées, PCT et LPCT permettent également l'actualisation incrémentielle des vues matérialisées sans nécessiter de journaux de vues matérialisées. L'actualisation peut être à la fois ON DEMAND ou ON COMMIT.

Tout comme le suivi des modifications de partitionnement (PCT), le suivi des modifications de partition logique (LPCT) est associé à une table de base et peut identifier avec précision les lignes d'une vue matérialisée affectées par les modifications de données de la table de base, en fonction des limites de partition logique définies.

Pour plus d'informations, voir Vues matérialisées avancées.

Utilisation du suivi des modifications de partitions logiques

Le suivi logique des modifications de partition (LPCT) partitionne logiquement une table à l'aide d'une colonne de clé et d'une méthode spécifiées.

La syntaxe de création du suivi des modifications de partition logique est analogue aux partitions physiques. Contrairement aux partitions physiques, qui doivent être créées dans le cadre de la création de tables, LPCT peut être librement spécifié indépendamment de la création de la table et de sa forme, ce qui permet une plus grande flexibilité pour répondre à vos besoins. La création LPCT ne concerne que les métadonnées.

Rubrique parent : Suivi des modifications de partition logique et Vues matérialisées

Création de partitions logiques - BNF

Décrit la syntaxe pour créer des partitions logiques BNF.

Voici la syntaxe pour créer des partitions logiques BNF :

CREATE LOGICAL PARTITION TRACKING ON table_name
    PARTITION BY RANGE (partition_key)
    INTERVAL (interval_clause)
    (partition_specification);
  • Seules les méthodes de partitionnement logique RANGE et INTERVAL sont prises en charge.
  • Une seule colonne de clé de partitionnement logique est prise en charge.
  • La colonne de clé de partitionnement peut avoir les types de données suivants :
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

Sélection de la colonne de clé de partitionnement logique

La clé de partitionnement logique est spécifiée pour définir les limites de chaque partition logique.

La clé de partitionnement logique n'est pas physique, ce qui signifie que les lignes de table appartenant à une plage de clés ne sont pas séparées en une partition physique distincte. La table peut être non partitionnée ou partitionnée sur une clé différente de la clé de partitionnement logique. La clé de partition logique peut être choisie librement, et les limites de partition peuvent être rendues flexibles.

Pour choisir une colonne de clé de suivi des modifications de partition logique (LPCT), vous pouvez considérer une colonne groupée, c'est-à-dire une colonne dans laquelle les données sont proches de la valeur triée par colonne, fréquemment référencée dans les prédicats de filtre de requête. Pour une colonne clusterisée, moins de partitions logiques sont susceptibles d'être affectées lors du chargement des données, ce qui signifie que moins de partitions logiques STALE doivent être actualisées et plus de partitions logiques FRESH sont prêtes à être utilisées pour les réécritures. Si une table est déjà partitionnée, il est recommandé de créer un LPCT en utilisant une colonne différente de la colonne de clé de partitionnement. LPCT offre des avantages similaires à ceux du suivi des modifications du partitionnement (PCT), et les avantages combinés ne sont pas maximisés si le suivi des données est effectué sur la même colonne.

Fraîcheur des vues matérialisées avec Suivi des modifications de partitions logiques

Le mécanisme de suivi de la persistance de la partition logique (LPCT) enregistre et consolide automatiquement les statistiques de modification en interne en fonction de la clé de partition logique et de la méthode de partitionnement spécifiées lors de chaque modification de données.

Les données de modification adjacentes sont regroupées dans une partition "logique". Contrairement à Partitioning Change Tracking (PCT), qui est lié aux limites de partition physique, le schéma LPCT offre une flexibilité dans la gestion et le regroupement des modifications de données résultant des LMD appliquées à la table de base.

Pendant les LMD classiques et les charges directes, LPCT adopte le même algorithme que PCT utilise pour suivre la stalitude. Pendant les réécritures de requête, LPCT adopte le même algorithme que PCT utilise pour calculer le confinement de réécriture.

Lorsqu'une table est partitionnée de manière logique à l'aide de plages de clés, une vue matérialisée définie sur la table peut utiliser LPCT pour le suivi de la persistance, l'actualisation et la réécriture des requêtes, à condition que la vue matérialisée contienne la clé de partition logique.

Remarque

Tous les types de vue matérialisée sont pris en charge pour LPCT.

Réécriture avec les vues matérialisées avec Suivi des modifications de partition logique

A l'aide du suivi des modifications de partition logique (LPCT), Oracle sait qu'une vue matérialisée est STALE par rapport à certaines partitions logiques de la table de base, mais FRESH par rapport à d'autres parties.

Si les données des tables de base sont plus détaillées, la vue matérialisée associée sera utilisée plus fréquemment en raison de la réécriture du LPCT.

Oracle identifie et utilise de manière transparente le sous-ensemble de vues matérialisées FRESH pour la réécriture des requêtes afin de répondre aux requêtes complexes des tables de base lorsque QUERY_REWRITE_INTEGRITY = ENFORCED |TRUSTED.

Si les lignes de la vue matérialisée sont partiellement FRESH par rapport à ces partitions logiques, une réécriture partielle peut être effectuée pour répondre à la requête partiellement à l'aide de la vue matérialisée, c'est-à-dire les partitions logiques FRESH et partiellement à l'aide de la table de base, c'est-à-dire les partitions logiques STALE.

Actualisation des vues matérialisées avec Suivi des modifications de partitions logiques

L'actualisation du suivi des modifications de partition logique (LPCT) peut être implémentée à l'aide de la persistance des données plus détaillées pour actualiser de manière incrémentielle les sous-ensembles STALE d'une vue matérialisée, ce qui élimine l'actualisation complète coûteuse ou l'actualisation rapide basée sur les journaux.

Si l'actualisation LPCT est spécifiée, les partitions logiques STALE sont identifiées et les opérations d'actualisation ciblées sont effectuées sur ces partitions logiques uniquement.

Pour appeler l'actualisation à l'aide du suivi des modifications de partition logique, vous indiquez ‘L’ ou ‘l’ ("logique") comme méthode d'actualisation.

Par exemple : execute DBMS_MVIEW.REFRESH(<materialized_view_name>,’L’);

Si REFRESH FORCE est indiqué, une actualisation FAST est choisie et effectuée si possible, sinon elle effectue une actualisation COMPLETE. Lors de l'actualisation de la vue matérialisée FORCE, l'actualisation LPCT a la même priorité que l'actualisation du suivi des modifications de partitionnement (PCT).

Suivi des modifications de partition logique – Vues du dictionnaire de données

Décrit les vues du dictionnaire de données pour rechercher des informations sur les partitions logiques.

Interrogez les vues suivantes du dictionnaire de données pour extraire des informations sur les partitions logiques.

  • ALL_MVIEW_DETAIL_LOGICAL_PARTITION : cette vue affiche les informations de fraîcheur des vues matérialisées, par rapport à une partition logique de détail LPCT, accessible à l'utilisateur actuel. Pour plus d'informations, reportez-vous à ALL_MVIEW_DETAIL_PARTITION.

  • DBA_MVIEW_DETAIL_ LOGICAL_PARTITION : affiche des informations de fraîcheur pour toutes les vues matérialisées de la base de données, par rapport à une partition logique de détail LPCT. Pour plus d'informations, reportez-vous à DBA_MVIEW_DETAIL_PARTITION.

  • USER_MVIEW_DETAIL_ LOGICAL_PARTITION : affiche des informations de fraîcheur pour toutes les vues matérialisées, par rapport à une partition logique de détail LPCT, détenue par l'utilisateur actuel. Pour plus d'informations, reportez-vous à USER_MVIEW_DETAIL_PARTITION.

Exemple : Suivi des modifications de partition logique

Affiche les étapes d'utilisation du suivi des modifications de partition logique (LPCT) à l'aide d'une vue matérialisée contenant des jointures et des agrégats.

  1. Créez des tables de base avec des partitions de modification logique.
    1. Créez une table 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));

      La table MYSALES est ainsi créée.

    2. Insérez des enregistrements dans la table 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;
      La table MYSALES est alors renseignée.
    3. Créez un suivi de partition logique pour la table 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'))
  );
      Cela crée un suivi de partition logique pour la table MYSALES en utilisant TIME_ID comme clé.
    4. Créez une table MYCUSTOMERS.
      CREATE TABLE mycustomers (cust_id NUMBER, age NUMBER, gender CHAR(1), address VARCHAR(100));
      La table MYCUSTOMERS est ainsi créée.
    5. Insérez des enregistrements dans la table 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;
      La table MYCUSTOMERS est alors renseignée.
    6. Créez un suivi de partition logique pour la table MYCUSTOMERS.
      CREATE LOGICAL PARTITION TRACKING ON mycustomers
 PARTITION BY RANGE (age) INTERVAL (20.5)
 (PARTITION m0 values less than (20));
      Cela crée un suivi de partition logique pour la table MYSALES en utilisant AGE comme clé.
  2. Créez une vue matérialisée sur les tables avec le suivi des modifications de partition logique.
    1. Créez une vue matérialisée sur les tables MYSALES et 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;
      La vue matérialisée SALES_AGE_TIME est ainsi créée.
    2. Interrogez la vue DBA_MVIEW_DETAIL_LOGICAL_PARTITION du dictionnaire de données.
      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;

      La sortie suivante est affichée.

      
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. Utilisez EXPLAIN_MVIEW pour évaluer les fonctionnalités d'actualisation et de réécriture associées à la partition logique.
      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;

      La sortie suivante est affichée.

      
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. Observez l'impact des instructions LMD sur votre vue matérialisée.
    1. Introduisez une nouvelle partition logique dans la table MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES
      (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      Une nouvelle partition (partition #6) est ainsi introduite dans la table MYSALES.
    2. Introduisez une nouvelle partition logique dans la table MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      Une nouvelle partition (partition #6) est ainsi introduite dans la table MYSALES.
    3. Introduisez une nouvelle partition logique dans la table MYCUSTOMERS.
      INSERT INTO mycustomers(cust_id, age, gender) VALUES (1399, 80, 'F');
      Cela introduit une nouvelle partition (partition #3) sur la table MYCUSTOMERS.
    4. Introduisez une nouvelle partition logique dans la table 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);
      Une nouvelle partition (partition #7) est ainsi introduite dans la table MYSALES.
    5. Introduisez une nouvelle partition logique dans la table 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;
      Cela introduit une nouvelle partition (partition #2) sur la table MYSALES.
    6. Interrogez la vue DBA_MVIEW_DETAIL_LOGICAL_PARTITION du dictionnaire de données.
      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;

      Maintenant, il affiche la sortie suivante.

      
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. Réécrivez LPCT sur le sous-ensemble de lpart #1 sur les tables MYSALES et lpart #0 sur les tables 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. Interrogez REWRITE_TABLE pour vérifier les réécritures.
      SELECT mv_name, sequence, pass, message FROM rewrite_table;
    9. Exécutez l'interrogation suivante.
      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. Consultez le plan d'exécution de la requête ci-dessus pour vérifier les réécritures.
      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. Tirez parti du LPCT pour l'actualisation incrémentielle.
    1. Exécutez le code suivant pour effectuer l'actualisation LPCT.
      EXECUTE DBMS_MVIEW.REFRESH('SALES_AGE_TIME', 'L');
    2. Vérifiez l'actualisation à l'aide de la requête suivante.
      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;