DBMS_SNAPSHOT: Manutenção de materialized views (alternativo ao DBMS_MVIEW)

DBMS_SNAPSHOT: Manutenção de materialized views (alternativo ao DBMS_MVIEW)

Uma materialized view é um objeto de banco de dados que contém os resultados de uma consulta. A cláusula FROM da consulta pode referenciar tabelas, views e outras materialized views. Coletivamente, esses objetos são chamados de tabelas mestre (um termo de replicação) ou tabelas de detalhe (um termo de data warehousing). Este artigo usa “tabelas mestre” para consistência. Os bancos de dados que contêm as tabelas mestre são chamados de bancos de dados mestre.

O pacote DBMS_SNAPSHOT permite que você crie, atualize e gerencie materialized views.

Neste artigo, vamos ver como usar o pacote DBMS_SNAPSHOT para realizar as seguintes tarefas:

• Criar materialized views
• Atualizar materialized views
• Gerenciar materialized views

Criando materialized views

Para criar uma materialized view, você pode usar o comando CREATE MATERIALIZED VIEW com a seguinte sintaxe básica:

CREATE MATERIALIZED VIEW nome_da_view
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE | ON STATEMENT | ON DEMAND ]
ON [COMMIT | DEMAND ]
[ [ENABLE | DISABLE] QUERY REWRITE]
AS SELECT ...;

O parâmetro BUILD indica se a materialized view deve ser populada imediatamente ou na primeira atualização solicitada. O parâmetro REFRESH indica o tipo e a frequência de atualização que deve ser tentada. FAST significa que uma atualização rápida é tentada, usando os logs das tabelas fonte. COMPLETE significa que o segmento de tabela que suporta a materialized view é truncado e repopulado completamente usando a consulta associada. FORCE significa que uma atualização rápida é tentada, e se não for possível, uma atualização completa é realizada. ON STATEMENT significa que a materialized view é atualizada sempre que uma DML é executada em uma das tabelas mestre. ON DEMAND significa que a atualização é iniciada por uma solicitação manual ou uma tarefa agendada. O parâmetro ON indica quando a atualização deve ser disparada. COMMIT significa que a atualização é disparada por uma mudança de dados em uma das tabelas dependentes. DEMAND significa que a atualização é iniciada por uma solicitação manual ou uma tarefa agendada. O parâmetro QUERY REWRITE indica se a materialized view deve ser considerada para operações de reescrita de consulta. A reescrita de consulta permite que o otimizador substitua uma consulta por uma materialized view que contenha o mesmo resultado.

Você também pode usar a cláusula ON PREBUILT TABLE para criar uma materialized view usando um segmento de tabela existente, que deve ter o mesmo nome da materialized view e suportar a mesma estrutura de colunas que a consulta.

Por exemplo, para criar uma materialized view que contém o total de vendas por mês de uma tabela remota chamada orders, você pode usar o seguinte comando:

CREATE MATERIALIZED VIEW vendas_por_mes
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS SELECT to_char(order_date, 'YYYY-MM') as mes, sum(amount) as total
FROM orders@dblink
GROUP BY to_char(order_date, 'YYYY-MM');

Atualizando materialized views

Para atualizar uma materialized view, você pode usar o procedimento REFRESH do pacote DBMS_SNAPSHOT com a seguinte sintaxe:

DBMS_SNAPSHOT.REFRESH (
   list                IN VARCHAR2,
   method              IN VARCHAR2  DEFAULT 'F',
   rollback_seg        IN VARCHAR2  DEFAULT NULL,
   push_deferred_rpc   IN BOOLEAN   DEFAULT TRUE,
   refresh_after_errors IN BOOLEAN   DEFAULT FALSE,
   purge_option        IN BINARY_INTEGER DEFAULT 1,
   parallelism         IN BINARY_INTEGER DEFAULT 0,
   heap_size           IN BINARY_INTEGER DEFAULT 0,
   atomic_refresh      IN BOOLEAN   DEFAULT TRUE);

O parâmetro list indica o nome da materialized view ou uma lista de nomes separados por vírgula. O parâmetro method indica o tipo de atualização, que pode ser F (fast), C (complete), ? (force) ou P (pct). O parâmetro rollback_seg indica o segmento de rollback a ser usado. O parâmetro push_deferred_rpc indica se as chamadas RPC diferidas devem ser enviadas antes da atualização. O parâmetro refresh_after_errors indica se a atualização deve continuar após erros. O parâmetro purge_option indica se os logs devem ser purgados após a atualização. O parâmetro parallelism indica o grau de paralelismo a ser usado. O parâmetro heap_size indica o tamanho do heap a ser usado. O parâmetro atomic_refresh indica se a atualização deve ser atômica ou não.

Por exemplo, para atualizar a materialized view vendas_por_mes usando uma atualização rápida, você pode usar o seguinte comando:

DBMS_SNAPSHOT.REFRESH('vendas_por_mes', 'F');

Gerenciar materialized views

O pacote DBMS_SNAPSHOT também oferece outros procedimentos para gerenciar materialized views, como:

• PURGE_LOG: purga linhas do log da materialized view.
• PURGE_DIRECT_LOAD_LOG: purga linhas do log de carga direta após elas não serem mais necessárias por nenhuma materialized view (usado com data warehousing).
• PURGE_SNAPSHOT_FROM_LOG: purga linhas do log da materialized view para uma determinada materialized view.
• REFRESH_ALL_MVIEWS: atualiza todas as materialized views que não foram atualizadas por causa da carga em massa mais recente em uma tabela de detalhe dependente.
• REFRESH_DEPENDENT: atualiza todas as materialized views baseadas em tabela que dependem de uma tabela de detalhe especificada ou uma lista de tabelas de detalhe.
• REGISTER_SNAPSHOT: permite a administração de materialized views individuais. Invocado em um site de materialized view para registrar uma materialized view.
• UNREGISTER_SNAPSHOT: permite a administraação de materialized views individuais. Invocado em um site mestre para desregistrar uma materialized view.
• BEGIN_TABLE_REORGANIZATION: realiza um processo para preservar os dados da materialized view necessários para a atualização. Deve ser chamado antes de uma tabela mestre ser reorganizada.
• END_TABLE_REORGANIZATION: garante que os dados da materialized view para a tabela mestre são válidos e que a tabela mestre está no estado adequado. Deve ser chamado após uma tabela mestre ser reorganizada.

Para mais informações sobre esses procedimentos, consulte a documentação oficial do Oracle.

Conclusão

Neste artigo, vimos como usar o pacote DBMS_SNAPSHOT para criar, atualizar e gerenciar materialized views. Esse pacote é um alternativo ao pacote DBMS_MVIEW, que é um sinônimo para o mesmo. As materialized views são úteis para melhorar o desempenho de consultas, replicar dados entre sites e implementar data warehousing.

Abs

Referências

• CREATE MATERIALIZED VIEW – Oracle Help Center 
• DBMS_SNAPSHOT – Oracle