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

Décrit des informations sur la structure de métadonnées de suivi des modifications de partition logique (LPCT) et la réécriture de requête avec suivi des modifications de partition logique dans Autonomous Database.

A propos du suivi des modifications de partitions logiques

Le suivi des modifications de partition logique (LPCT) permet de suivre l'étendue des vues matérialisées.

Le suivi des modifications de partition logique vous permet de créer des partitions logiques sur des tables de base. Il évalue le degré de finesse des tables de base pour des 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.

Lorsque des 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 en mode d'intégrité appliqué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 obsolètes au niveau de la partition à des fins d'actualisation et de réécriture. Alors que le suivi des modifications de partitionnement classique (PCT) repose sur le partitionnement physique des tables, LPCT n'a aucune dépendance vis-à-vis des tables physiquement partitionnées ; LPCT peut être utilisé avec des 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 courts 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 convivialité des vues matérialisées, PCT et LPCT permettent également une actualisation incrémentielle des vues matérialisées sans avoir besoin de journaux de vues matérialisées ; l'actualisation peut être à la fois ON DEMAND ou ON COMMIT.

Comme pour 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 sur 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 des modifications de partition logique (LPCT) partitionne logiquement une table à l'aide d'une colonne et d'une méthode de clé 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 table, LPCT peut être librement spécifié indépendamment de la création de table et de sa forme, ce qui permet une plus grande flexibilité pour répondre à vos besoins. La création LPCT n'est que des métadonnées.

Créer des partitions logiques - BNF

Décrit la syntaxe de création de 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 partition logique est prise en charge.
  • La colonne de clé de partitionnement peut présenter les types de données suivants :
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

Choisir la colonne de clé de partition logique

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

La clé de partition 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 partition logique. La clé de partition logique peut être choisie librement et les limites de partition peuvent être rendues flexibles.

Pour choisir une colonne clé de suivi des modifications de partition logique (LPCT), vous pouvez considérer une colonne clusterisée, c'est-à-dire une colonne dans laquelle les données sont près d'être triées par valeur de colonne, qui sont fréquemment référencées 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 des chargements de données, cela 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 à l'aide d'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 partition logique

Le mécanisme de suivi des modifications de 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 au PCT (Partitioning Change Tracking), 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 opérations LMD appliquées à la table de base.

Pendant les DML classiques et les chargements directs, LPCT adopte le même algorithme que PCT utilise pour suivre l'étroitesse. Lors des 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 logiquement à l'aide de plages de clés, une vue matérialisée définie sur la table peut utiliser LPCT pour le suivi de l'ancienneté, l'actualisation et la réécriture d'interrogation, à 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 des vues matérialisées à l'aide du 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.

Avec les informations de finesse de données plus détaillées des tables de base, la vue matérialisée associée serait utilisée plus fréquemment en raison de la réécriture LPCT.

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

Si les lignes de vue matérialisée sont partiellement FRESH par rapport à ces partitions logiques, une réécriture partielle peut avoir lieu pour répondre partiellement à la requête à l'aide de la vue matérialisée, c'est-à-dire des 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 partition logique

L'actualisation du suivi des modifications de partition logique (LPCT) peut être implémentée à l'aide d'une granularité des données plus fine 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 uniquement sur ces partitions logiques.

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

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

Si REFRESH FORCE est spécifié, une actualisation FAST est choisie et exécutée si possible, ou bien 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 PCT (Partitioning Change Tracking).

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 obtenir 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 en cours. Pour plus d'informations, reportez-vous à ALL_MVIEW_DETAIL_PARTITION.

  • DBA_MVIEW_DETAIL_ LOGICAL_PARTITION : affiche les 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 les 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 en cours. Pour plus d'informations, reportez-vous à USER_MVIEW_DETAIL_PARTITION.

Exemple : suivi des modifications de partition logique

Présente les étapes à suivre pour utiliser le 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 la 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 alors 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;
      Cette opération remplit la table MYSALES.
    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 la table MYCUSTOMERS.
      CREATE TABLE mycustomers (cust_id NUMBER, age NUMBER, gender CHAR(1), address VARCHAR(100));
      La table MYCUSTOMERS est alors 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;
      Cette opération remplit la table MYCUSTOMERS.
    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 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 alors créée.
    2. Recherchez la vue du dictionnaire de données 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;

      Indique la sortie suivante.

      
      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 liées aux partitions logiques.
      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;

      Indique la sortie suivante.

      
      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 DML sur votre vue matérialisée.
    1. Introduisez une nouvelle partition logique sur 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);
      Cela introduit une nouvelle partition (partition n° 6) sur la table MYSALES.
    2. Introduisez une nouvelle partition logique sur 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);
      Cela introduit une nouvelle partition (partition n° 6) sur la table MYSALES.
    3. Introduisez une nouvelle partition logique sur 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 sur 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);
      Cela introduit une nouvelle partition (partition #7) sur la table MYSALES.
    5. Introduisez une nouvelle partition logique sur 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. Recherchez la vue du dictionnaire de données 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;

      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. Effectuez une réécriture 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 la requête 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 de 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;