Documentação do Oracle Cloud Infrastructure

Rastreamento da Alteração da Partição Lógica e Views Materializadas

Descreve informações sobre a estrutura de metadados LLPCT (Logical Partition Change Tracking) e a Reescrita de Consultas com Rastreamento de Alterações de Partições Lógicas no Autonomous Database.

Tópico principal: Usuários do Autonomous Database para Experientes no Oracle Database

Sobre o Rastreamento de Alteração de Partição Lógica

O LPCT (Logical Partition Change Tracking) rastreia a paralisação das views materializadas.

O Rastreamento de Alteração de Partição Lógica permite criar partições lógicas em tabelas base. Ele avalia a rigidez das tabelas de base para partições lógicas individuais sem usar um log de view materializada ou exigir que qualquer uma das tabelas usadas na view materializada seja particionada.

Quando uma ou mais tabelas de base dependentes de uma view materializada são atualizadas, uma view materializada se torna STALE e não pode ser usada para regravação de consulta no modo de integridade imposta padrão.

O LPCT (Logical Partition Change Tracking) oferece a capacidade de aproveitar as informações de particionamento lógico fornecidas pelo usuário de tabelas base de uma view materializada para um rastreamento mais detalhado e em nível de partição de dados desatualizados para fins de atualização e reescrita. Embora o Rastreamento de Alterações de Particionamento clássico (PCT) dependa do particionamento físico de tabelas, o LPCT não depende da partição física de tabelas; o LPCT pode ser usado com tabelas particionadas e não particionadas.

O mecanismo de Rastreamento de Alteração de Partição Lógica faz uso dos subconjuntos FRESH (partições) de views materializadas, apesar de outros subconjuntos serem STALE. É possível obter tempos de resposta mais rápidos para consultas do usuário porque os resultados pré-calculados em views materializadas são usados com mais frequência. Além de aumentar a usabilidade das views materializadas, o PCT e o LPCT também permitem a atualização incremental das views materializadas sem a necessidade de logs de view materializada; a atualização pode ser ON DEMAND ou ON COMMIT.

Semelhante ao Rastreamento de Alteração de Particionamento (PCT), o Rastreamento de Alteração de Partição Lógica (LPCT) é associado a uma tabela base e pode identificar com precisão as linhas em uma View Materializada afetadas por alterações de dados na tabela base, de acordo com os limites de partição lógica definidos.

Consulte Views Materializadas Avançadas para obter mais informações.

Usando o Rastreamento de Alteração de Partição Lógica

O LPCT (Logical Partition Change Tracking) faz a partição lógica de uma tabela usando uma coluna e um método de chave especificados.

A sintaxe de criação do Rastreamento de Alteração de Partição Lógica é análoga às partições físicas. Ao contrário das partições físicas, que devem ser criadas como parte da criação de tabelas, o LPCT pode ser especificado livremente, independentemente da criação de tabelas e sua forma, permitindo mais flexibilidade para atender às suas necessidades. A criação de LPCT é somente metadados.

Tópico principal: Rastreamento de Alteração de Partição Lógica e Views Materializadas

Criando Partições Lógicas - BNF

Descreve a sintaxe para criar partições lógicas BNF.

Veja a seguir a sintaxe para criar partições lógicas BNF:

CREATE LOGICAL PARTITION TRACKING ON table_name
    PARTITION BY RANGE (partition_key)
    INTERVAL (interval_clause)
    (partition_specification);
  • Somente métodos de particionamento lógico RANGE e INTERVAL são suportados.
  • Somente uma única coluna de chave de partição lógica é suportada.
  • A coluna de chave da partição pode ser destes tipos de dados:
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

Escolhendo a Coluna de Chave de Partição Lógica

A chave de particionamento lógico é especificada para definir os limites de cada partição lógica.

A chave de partição lógica não é física, isso significa que as linhas de tabela pertencentes a um intervalo de chaves não são segregadas em uma partição física separada. A tabela pode ser não particionada ou particionada em uma chave diferente da chave de partição lógica. A chave de partição lógica pode ser escolhida livremente, e os limites de partição podem ser flexibilizados.

Para escolher uma coluna de chave de Rastreamento de Alteração de Partição Lógica (LPCT), você pode considerar uma coluna clusterizada, ou seja, uma coluna em que os dados estão próximos de serem classificados por valor de coluna, que são frequentemente referenciados nos predicados de filtro de consulta. Para uma coluna clusterizada, é provável que menos partições lógicas sejam afetadas durante os carregamentos de dados; isso significa que menos partições lógicas STALE precisam ser atualizadas e mais partições lógicas FRESH estão prontas para serem usadas para regravações. Se uma tabela já estiver particionada, é recomendável criar um LPCT usando uma coluna diferente da coluna de chave de partição. O LPCT oferece benefícios semelhantes aos do PCT (Partitioning Change Tracking), e os benefícios combinados não serão maximizados se o rastreamento de dados for feito na mesma coluna.

Atualização de Views Materializadas Usando o Rastreamento de Alteração de Partição Lógica

O mecanismo de rastreamento de staleness (LPCT) Logical Partition Change Tracking registra e consolida automaticamente as estatísticas de alteração internamente com base na chave de partição lógica especificada e no método de particionamento durante cada alteração de dados.

Os dados de alteração adjacentes são agrupados em uma partição "lógica". Ao contrário do Rastreamento de Alterações de Particionamento (PCT), que está vinculado a limites de partição física, o esquema LPCT oferece flexibilidade no gerenciamento e agrupamento das alterações de dados resultantes de DMLs aplicadas à tabela base.

Durante DMLs e cargas diretas convencionais, o LPCT adota o mesmo algoritmo que o PCT usa para rastrear a paralisia. Durante as regravações de Consulta, o LPCT adota o mesmo algoritmo que o PCT usa para calcular a contenção de regravação.

Quando uma tabela é logicamente particionada usando intervalos de chaves, uma view materializada definida na tabela é elegível para usar o LPCT para rastreamento de paralisação, atualização e regravação de consulta, desde que a view materializada contenha a chave de partição lógica.

Observação

Todos os tipos de Views Materializadas são suportados para LPCT.

Reescrever com Views Materializadas Usando o Rastreamento de Alteração de Partição Lógica

Usando o LPCT (Logical Partition Change Tracking), a Oracle sabe que uma view materializada é STALE em relação a algumas partições lógicas da tabela base, mas FRESH em relação a outras partes.

Tendo as informações de escassez de dados mais refinadas das tabelas base, a view materializada associada seria usada com mais frequência devido à reescrita de LPCT.

O sistema Oracle identifica e usa de forma transparente o subconjunto FRESH de views materializadas para regravação de consulta para responder a consultas complicadas de tabelas base quando QUERY_REWRITE_INTEGRITY = ENFORCED |TRUSTED.

Se as linhas da view materializada forem parcialmente FRESH com relação a essas partições lógicas, uma regravação parcial poderá ocorrer para responder a consulta parcialmente usando a view materializada, ou seja, as partições lógicas FRESH e parcialmente usando a tabela base, ou seja, as partições lógicas STALE.

Atualização de Views Materializadas Usando o Rastreamento de Alteração de Partição Lógica

A atualização do LPCT (Logical Partition Change Tracking) pode ser implementada usando a paralisação de dados mais refinada para atualizar incrementalmente subconjuntos STALE de uma view materializada, eliminando a atualização completa dispendiosa ou a atualização rápida baseada em log.

Se a atualização de LPCT for especificada, as partições lógicas STALE serão identificadas e as operações de atualização direcionadas serão executadas apenas para essas partições lógicas.

Para chamar a atualização usando o rastreamento de alteração de partição lógica, especifique ‘L’ ou ‘l’ ("lógico") como método de atualização.

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

Se REFRESH FORCE for especificado, uma atualização FAST será escolhida e executada, se possível, ou ela executará uma atualização COMPLETE. Durante a atualização da view materializada FORCE, a atualização do LPCT tem a mesma prioridade que a atualização do PCT (Partitioning Change Tracking).

Rastreamento de Alterações da Partição Lógica – Views de Dicionário de Dados

Descreve as views do dicionário de dados para encontrar informações sobre partições lógicas.

Consulte estas views do dicionário de dados para recuperar informações sobre partições lógicas.

  • ALL_MVIEW_DETAIL_LOGICAL_PARTITION: Esta view exibe as informações de atualização das views materializadas, com relação a uma partição lógica de detalhes de LPCT, acessível ao usuário atual. Consulte ALL_MVIEW_DETAIL_PARTITION para obter mais informações.

  • DBA_MVIEW_DETAIL_ LOGICAL_PARTITION: exibe informações de atualização para todas as views materializadas no banco de dados, com relação a uma partição lógica de detalhes de LPCT. Consulte DBA_MVIEW_DETAIL_PARTITION para obter mais informações.

  • USER_MVIEW_DETAIL_ LOGICAL_PARTITION: exibe informações de atualização para todas as views materializadas, com relação a uma partição lógica de detalhes de LPCT, de propriedade do usuário atual. Consulte USER_MVIEW_DETAIL_PARTITION para obter mais informações.

Exemplo: Rastreamento de Alteração de Partição Lógica

Mostra as etapas para usar o LPCT (Logical Partition Change Tracking) usando uma View Materializada que contém junções e agregações.

  1. Criar tabelas base com partições de alteração lógica.
    1. Crie a tabela 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));

      Isso cria a tabela MYSALES.

    2. Insira registros na tabela 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;
      Isso preenche a tabela MYSALES.
    3. Criar rastreamento de partição lógica para a tabela 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'))
  );
      Isso cria um rastreamento de partição lógica para a tabela MYSALES usando o TIME_ID como chave.
    4. Crie a tabela MYCUSTOMERS.
      CREATE TABLE mycustomers (cust_id NUMBER, age NUMBER, gender CHAR(1), address VARCHAR(100));
      Isso cria a tabela MYCUSTOMERS.
    5. Insira registros na tabela 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;
      Isso preenche a tabela MYCUSTOMERS.
    6. Criar rastreamento de partição lógica para a tabela MYCUSTOMERS.
      CREATE LOGICAL PARTITION TRACKING ON mycustomers
 PARTITION BY RANGE (age) INTERVAL (20.5)
 (PARTITION m0 values less than (20));
      Isso cria um rastreamento de partição lógica para a tabela MYSALES usando o AGE como chave.
  2. Crie uma view materializada sobre as tabelas com rastreamento de alteração de partição lógica.
    1. Crie uma view materializada nas tabelas 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;
      Isso cria a view materializada SALES_AGE_TIME.
    2. Consulte a view do dicionário de dados 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;

      Ela mostra a seguinte saída.

      
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. Use EXPLAIN_MVIEW para avaliar os recursos de atualização e regravação relacionados à partição 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;

      Ela mostra a seguinte saída.

      
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 o impacto das DMLs em sua view materializada.
    1. Introduza uma nova partição lógica na tabela MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES
      (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      Isso introduz uma nova partição (partição nº 6) na tabela MYSALES.
    2. Introduza uma nova partição lógica na tabela MYSALES.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      Isso introduz uma nova partição (partição nº 6) na tabela MYSALES.
    3. Introduza uma nova partição lógica na tabela MYCUSTOMERS.
      INSERT INTO mycustomers(cust_id, age, gender) VALUES (1399, 80, 'F');
      Isto introduz uma nova partição (partição #3) na tabela MYCUSTOMERS.
    4. Introduza uma nova partição lógica na tabela 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);
      Isto introduz uma nova partição (partição #7) na tabela MYSALES.
    5. Introduza uma nova partição lógica na tabela 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;
      Isso introduz uma nova partição (partição nº 2) na tabela MYSALES.
    6. Consulte a view do dicionário de dados 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;

      Agora, ele mostra a saída a seguir.

      
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. Execute a reescrita de LPCT no subconjunto da peça nº 1 nas tabelas MYSALES e da peça nº 0 nas tabelas 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 as regravações.
      SELECT mv_name, sequence, pass, message FROM rewrite_table;
    9. Execute a consulta a seguir.
      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. Visualize o plano de explicação para a consulta acima para verificar as reescritas.
      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. Aproveite o LPCT para atualização incremental.
    1. Execute o código a seguir para executar a atualização de LPCT.
      EXECUTE DBMS_MVIEW.REFRESH('SALES_AGE_TIME', 'L');
    2. Verifique a atualização usando a consulta a seguir.
      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;