Documentación de Oracle Cloud Infrastructure

Seguimiento de Cambios de Particiones Lógicas y Vistas Materializadas

Describe información sobre el marco de metadatos de seguimiento de cambios de particiones lógicas (LPCT) y la reescritura de consultas con seguimiento de cambios de particiones lógicas en Autonomous Database.

Tema principal: Autonomous Database para usuarios de Oracle Database con experiencia

Acerca del Seguimiento de Cambios de Partición Lógica

El seguimiento de cambios de particiones lógicas (LPCT) realiza un seguimiento de la caducidad de las vistas materializadas.

El seguimiento de cambios de particiones lógicas permite crear particiones lógicas en tablas base. Evalúa la caducidad de las tablas base para particiones lógicas individuales sin utilizar un log de vista materializada o sin necesidad de particionar ninguna de las tablas utilizadas en la vista materializada.

Cuando se actualizan una o más tablas base dependientes de una vista materializada, una vista materializada se convierte en STALE y no se puede utilizar para la reescritura de consultas en el modo de integridad forzada por defecto.

El seguimiento de cambios de particiones lógicas (LPCT) proporciona la capacidad de aprovechar la información de partición lógica proporcionada por el usuario de las tablas base de una vista materializada para un seguimiento a nivel de partición más detallado de los datos obsoletos con fines de refrescamiento y reescritura. Mientras que el seguimiento de cambios de partición clásico (PCT) se basa en la partición física de las tablas, LPCT no depende de las tablas que se particionan físicamente; LPCT se puede utilizar con tablas particionadas y no particionadas.

El mecanismo de seguimiento de cambios de partición lógica utiliza los subjuegos FRESH (particiones) de vistas materializadas a pesar de que otros subjuegos son STALE. Se pueden lograr tiempos de respuesta más rápidos para las consultas de usuario porque los resultados calculados previamente en las vistas materializadas se utilizan con más frecuencia. Además de aumentar la capacidad de uso de las vistas materializadas, PCT y LPCT también permiten el refrescamiento incremental de las vistas materializadas sin necesidad de logs de vistas materializadas; el refrescamiento puede ser ON DEMAND o ON COMMIT.

Al igual que el seguimiento de cambios de partición (PCT), el seguimiento de cambios de partición lógica (LPCT) está asociado a una tabla base y puede identificar con precisión las filas de una vista materializada afectadas por los cambios de datos en la tabla base, según los límites de partición lógica definidos.

Consulte las vistas materializadas avanzadas Vistas materializadas avanzadas para obtener más información.

Uso del Seguimiento de Cambios de Partición Lógica

El seguimiento de cambios de particiones lógicas (LPCT) realiza particiones lógicas de una tabla mediante una columna y un método clave especificados.

La sintaxis de creación del seguimiento de cambios de particiones lógicas es análoga a las particiones físicas. A diferencia de las particiones físicas, que se deben crear como parte de la creación de tablas, LPCT se puede especificar libremente independientemente de la creación de la tabla y su forma, lo que permite una mayor flexibilidad para satisfacer sus necesidades. La creación de LPCT es solo metadatos.

Tema principal: Seguimiento de cambios de particiones lógicas y vistas materializadas

Creación de Particiones Lógicas: BNF

Describe la sintaxis para crear particiones lógicas de BNF.

A continuación se muestra la sintaxis para crear particiones lógicas de BNF:

CREATE LOGICAL PARTITION TRACKING ON table_name
    PARTITION BY RANGE (partition_key)
    INTERVAL (interval_clause)
    (partition_specification);
  • Solo están soportados los métodos de partición lógica RANGE y INTERVAL.
  • Solo se admite una única columna de clave de partición lógica.
  • La columna de clave de partición puede ser de estos tipos de dato:
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

Selección de la Columna de Clave de Partición Lógica

La clave de partición lógica se especifica para definir los límites de cada partición lógica.

La clave de partición lógica no es física, lo que significa que las filas de tabla que pertenecen a un rango de claves no se separan en una partición física independiente. La tabla puede ser no particionada o particionada en una clave distinta de la clave de partición lógica. La clave de partición lógica se puede elegir libremente y los límites de partición se pueden hacer flexibles.

Para seleccionar una columna de clave de seguimiento de cambios de particiones lógicas (LPCT), puede considerar una columna agrupada, es decir, una columna en la que los datos están cerca de ordenarse por valor de columna, a la que se hace referencia con frecuencia en los predicados de filtro de consulta. Para una columna en cluster, es probable que se vean afectadas menos particiones lógicas durante las cargas de datos, lo que significa que se deben refrescar menos particiones lógicas STALE y que hay más particiones lógicas FRESH listas para su uso para reescrituras. Si una tabla ya está particionada, se recomienda crear un LPCT utilizando una columna diferente a la columna de clave de partición. LPCT ofrece beneficios similares a los del seguimiento de cambios de partición (PCT), y los beneficios combinados no se maximizan si el seguimiento de datos se realiza en la misma columna.

Actualización de Vistas Materializadas mediante el Seguimiento de Cambios de Particiones Lógicas

El mecanismo de seguimiento de caducidad de seguimiento de cambios de particiones lógicas (LPCT) registra y consolida automáticamente las estadísticas de cambios internamente en función de la clave de partición lógica y el método de partición especificados durante cada cambio de datos.

Los datos de cambio adyacentes se agrupan en una partición "lógica". A diferencia del seguimiento de cambios de partición (PCT), que está vinculado a límites de partición física, el esquema LPCT ofrece flexibilidad para gestionar y agrupar los cambios de datos resultantes de los DML aplicados a la tabla base.

Durante los DML convencionales y las cargas directas, el LPCT adopta el mismo algoritmo que el PCT utiliza para rastrear la estanqueidad. Durante las reescrituras de consulta, LPCT adopta el mismo algoritmo que PCT utiliza para calcular la contención de reescritura.

Cuando una tabla se particiona de forma lógica mediante rangos de claves, una vista materializada definida en la tabla puede utilizar LPCT para el seguimiento de caducidad, refrescamiento y reescritura de consultas, siempre que la vista materializada contenga la clave de partición lógica.

Nota

Se soportan todos los tipos de vistas materializadas para LPCT.

Reescritura con vistas materializadas mediante el seguimiento de cambios de particiones lógicas

Mediante el seguimiento de cambios de particiones lógicas (LPCT), Oracle sabe que una vista materializada es STALE con respecto a algunas particiones lógicas de la tabla base, pero FRESH con respecto a otras partes.

Al tener la información de caducidad de datos más detallada de las tablas base, la vista materializada asociada se utilizaría con mayor frecuencia debido a la reescritura de LPCT.

Oracle identifica y utiliza de forma transparente el subjuego FRESH de vistas materializadas para la reescritura de consultas con el fin de responder consultas complicadas de tablas base cuando QUERY_REWRITE_INTEGRITY = ENFORCED |TRUSTED.

Si las filas de vista materializada son parcialmente FRESH con respecto a esas particiones lógicas, se puede producir una reescritura parcial para responder a la consulta parcialmente mediante una vista materializada, es decir, particiones lógicas FRESH, y parcialmente mediante la tabla base, es decir, las particiones lógicas STALE.

Refrescamiento de Vistas Materializadas mediante Seguimiento de Cambios de Particiones Lógicas

El refrescamiento de seguimiento de cambios de particiones lógicas (LPCT) se puede implantar mediante la caducidad de datos más detallada para refrescar de forma incremental los subjuegos STALE de una vista materializada, eliminando el refrescamiento completo costoso o el refrescamiento rápido basado en logs.

Si se especifica el refrescamiento de LPCT, se identifican las particiones lógicas STALE y las operaciones de refrescamiento de destino se realizarán solo en esas particiones lógicas.

Para llamar al refrescamiento mediante el seguimiento de cambios de partición lógica, especifique ‘L’ o ‘l’ ("lógico") como método de refrescamiento.

Por ejemplo: execute DBMS_MVIEW.REFRESH(<materialized_view_name>,’L’);

Si se especifica REFRESH FORCE, se selecciona un refrescamiento FAST y se realiza si es posible, o bien, realiza un refrescamiento COMPLETE. Durante el refrescamiento de la vista materializada FORCE, el refrescamiento de LPCT tiene la misma prioridad que el refrescamiento de seguimiento de cambios de partición (PCT).

Seguimiento de Cambios de Particiones Lógicas: Vistas del Diccionario de Datos

Describe las vistas del diccionario de datos para buscar información sobre particiones lógicas.

Consulte las siguientes vistas del diccionario de datos para recuperar información sobre particiones lógicas.

  • ALL_MVIEW_DETAIL_LOGICAL_PARTITION: esta vista muestra la información de actualización de las vistas materializadas, con respecto a una partición lógica de detalles LPCT, a la que puede acceder el usuario actual. Consulte ALL_MVIEW_DETAIL_PARTITION para obtener más información.

  • DBA_MVIEW_DETAIL_ LOGICAL_PARTITION: muestra información de actualización para todas las vistas materializadas de la base de datos, con respecto a una partición lógica de detalles LPCT. Consulte DBA_MVIEW_DETAIL_PARTITION para obtener más información.

  • USER_MVIEW_DETAIL_ LOGICAL_PARTITION: muestra información de actualización para todas las vistas materializadas, con respecto a una partición lógica de detalles LPCT, propiedad del usuario actual. Consulte USER_MVIEW_DETAIL_PARTITION para obtener más información.

Ejemplo: Seguimiento de Cambios de Partición Lógica

Muestra los pasos para utilizar el seguimiento de cambios de particiones lógicas (LPCT) mediante una vista materializada que contiene uniones y agregados.

  1. Cree tablas base con particiones de cambio lógicas.
    1. Cree la tabla 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));

      De esta forma se crea la tabla MYSALES.

    2. Inserte registros en la tabla 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;
      De esta forma, se rellena la tabla MYSALES.
    3. Cree un seguimiento de partición lógica para la tabla 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'))
  );
      Esto crea un seguimiento de partición lógica para la tabla MYSALES utilizando TIME_ID como clave.
    4. Cree la tabla MYCUSTOMERS.
      CREATE TABLE mycustomers (cust_id NUMBER, age NUMBER, gender CHAR(1), address VARCHAR(100));
      De esta forma se crea la tabla MYCUSTOMERS.
    5. Inserte registros en la tabla 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;
      De esta forma, se rellena la tabla MYCUSTOMERS.
    6. Cree un seguimiento de partición lógica para la tabla MYCUSTOMERS.
      CREATE LOGICAL PARTITION TRACKING ON mycustomers
 PARTITION BY RANGE (age) INTERVAL (20.5)
 (PARTITION m0 values less than (20));
      Esto crea un seguimiento de partición lógica para la tabla MYSALES utilizando AGE como clave.
  2. Cree una vista materializada sobre las tablas con seguimiento de cambios de partición lógica.
    1. Cree una vista materializada en las tablas MYSALES y 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;
      De esta forma se crea la vista materializada SALES_AGE_TIME.
    2. Consulte la vista del diccionario de datos 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;

      Muestra la siguiente salida.

      
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. Utilice EXPLAIN_MVIEW para evaluar las capacidades de refrescamiento y reescritura relacionadas con la partición lógica.
      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;

      Muestra la siguiente salida.

      
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. Observe el impacto de los DML en la vista materializada.
    1. Introduzca una nueva partición lógica en la tabla MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES
      (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      Esto introduce una nueva partición (partición #6) en la tabla MYSALES.
    2. Introduzca una nueva partición lógica en la tabla MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      Esto introduce una nueva partición (partición #6) en la tabla MYSALES.
    3. Introduzca una nueva partición lógica en la tabla MYCUSTOMERS.
      INSERT INTO mycustomers(cust_id, age, gender) VALUES (1399, 80, 'F');
      Esto introduce una nueva partición (partición #3) en la tabla MYCUSTOMERS.
    4. Introduzca una nueva partición lógica en la tabla 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);
      Esto introduce una nueva partición (partición #7) en la tabla MYSALES.
    5. Introduzca una nueva partición lógica en la tabla 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;
      Esto introduce una nueva partición (partición #2) en la tabla MYSALES.
    6. Consulte la vista del diccionario de datos 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;

      Ahora, muestra la siguiente salida.

      
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. Realice la reescritura de LPCT en el subjuego de lpart #1 en MYSALES y lpart #0 en las tablas 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. Consulte REWRITE_TABLE para verificar las reescrituras.
      SELECT mv_name, sequence, pass, message FROM rewrite_table;
    9. Ejecute la siguiente consulta.
      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. Consulte la explicación del plan de la consulta anterior para verificar las reescrituras.
      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. Utilice LPCT para el refrescamiento incremental.
    1. Ejecute el siguiente código para realizar el refrescamiento de LPCT.
      EXECUTE DBMS_MVIEW.REFRESH('SALES_AGE_TIME', 'L');
    2. Verifique el refrescamiento mediante la siguiente consulta.
      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;