Materialized Views

← Recursos do servidor HQbird

Materialized view (MV) possui propriedades tanto de table persistente quanto de view. É definida de forma semelhante a uma view e sua consulta de origem é usada pelo engine para popular a MV table subjacente com linhas de dados. Não é permitido modificar dados de materialized view usando comandos DML. A única forma de atualizar o conteúdo da MV é executar o novo comando especial REFRESH (veja abaixo). Como uma tabela regular, a MV pode ter índices de qualquer tipo. Diferentemente das tabelas, não é permitido criar constraints e triggers para MV.

Sintaxe

Criar, alterar e remover

CREATE [OR ALTER] MATERIALIZED VIEW <view_name> [(columns_names)] 
  AS <query> 
  [WITH [NO] DATA]
  
ALTER MATERIALIZED VIEW <view_name> [(columns_names)] 
  AS <query> 
  [WITH [NO] DATA]

Cria uma nova MV ou modifica uma existente usando a consulta especificada e a lista opcional de nomes de colunas. A cláusula opcional WITH DATA especifica que a MV deve ser preenchida com dados imediatamente após a criação (alteração). Quando WITH NO DATA é especificado, ou quando esta cláusula é omitida, a MV não é populada com dados e permanece vazia. ALTER também remove todos os índices existentes da MV. Se uma view regular (isto é, não materialized) com o mesmo nome existir, um erro é gerado.

RECREATE MATERIALIZED VIEW <view_name> [(columns_names)] 
  AS <query> 
  [WITH [NO] DATA]

Remove a view materialized ou regular, se existir, e cria uma nova view materialized.

Não há comando separado para remover MV, use o comando regular DROP VIEW para fazê-lo.

Comandos especiais

ALTER MATERIALIZED VIEW <view_name> [(columns_names)] 
  AS <query> 
  TO NOT MATERIALIZED

Tipo especial de comando ALTER que converte uma view materialized em uma view regular com o mesmo nome. Objetos dependentes não são afetados. Todos os índices existentes e linhas de dados da antiga MV são removidos.

ALTER VIEW <view_name> [(columns_names)] 
  AS <query> 
  TO MATERIALIZED

Tipo especial de comando ALTER que converte uma view regular em uma view materialized com o mesmo nome. Objetos dependentes não são afetados.

Atualizando dados de materialized views

REFRESH MATERIALIZED VIEW [CONCURRENTLY|DROP DATA] [CASCADE]

Executa a consulta da MV e substitui os dados da MV pelo resultset da consulta. Todos os índices da MV são atualizados correspondentemente. Este é um comando DDL e a execução real é adiada até o COMMIT da transação atual. Existem algumas formas deste comando:

1. Recarga completa em modo exclusivo:

REFRESH MATERIALIZED VIEW <view_name>

desativa todos os índices ativos, exclui todos os dados existentes, preenche a MV table com linhas da consulta da MV e ativa todos os índices que foram desativados anteriormente. Esta é a forma mais rápida de refresh, se houve muitas alterações nas tabelas que compõem a consulta da MV. A reserva exclusiva da MV table é necessária para garantir que nenhuma outra conexão tente consultar a MV enquanto ela está sendo atualizada. Esta abordagem também é usada pelos comandos CREATE [OR ALTER], ALTER e RECREATE para carga inicial de dados.

Nota: se qualquer erro acontecer durante a etapa de carga de dados, então todos os dados recém-carregados serão excluídos, os índices previamente desativados serão ativados (como vazios) e o erro original será reportado ao usuário. Isto é, a MV table será esvaziada e isto não pode ser desfeito nem mesmo por rollback. Espera-se que a causa do erro seja corrigida pelo usuário (por exemplo, corrigindo a consulta da MV) e o comando REFRESH seja repetido novamente.

Nota: se erro(s) acontecer(em) ao ativar algum(ns) índice(s) (após a etapa de carga de dados), então este(s) índice(s) será(ão) deixado(s) em estado inativo. Tal(is) erro(s) não afetará(ão) os dados já carregados nem outros índices. O commit da transação terá sucesso e os erros de construção de índice serão colocados no status-vector como warnings. Tais índices poderão ser ativados posteriormente usando comandos regulares ALTER INDEX <index_name> ACTIVE, se necessário.

2. Atualizar apenas linhas alteradas em modo compartilhado:

REFRESH MATERIALIZED VIEW <view_name> CONCURRENTLY

Esta segunda forma não requer reserva exclusiva e permite que conexões concorrentes executem comandos que referenciam a MV enquanto ela é atualizada. Neste caso, o engine cria dois cursores ordenados para os dados da MV table e para o resultset da consulta da MV. Então o algoritmo de merge é usado para atualizar linhas alteradas, inserir novas linhas e excluir linhas que não existem mais. Todas as alterações das linhas da MV são feitas sob controle de transação, portanto isso proporciona acesso concorrente seguro e consistente à MV table. Ambos os cursores são ordenados com base na lista de colunas do índice único.

Um índice único deve ser construído na MV para permitir que este algoritmo funcione. Além disso, o texto da consulta da view armazenado na coluna RDB$VIEW_SOURCE não deve ser anulado pois é usado pelo engine para criar o comando de merge interno.

Esta forma do comando REFRESH deve ser preferida em relação à primeira quando acesso concorrente é necessário ou quando não houve muitas alterações nas tabelas da consulta da MV desde o refresh anterior.

Nota: se a consulta da MV contém cláusula ORDER BY, a ordem natural das linhas pode ser alterada após o refresh concorrente e não satisfazer mais a cláusula ORDER BY.

3. Excluir todos os dados (modo exclusivo):

REFRESH MATERIALIZED VIEW <view_name> DROP DATA

Este comando exclui dados da MV table e dos índices. É útil quando o DBA precisa liberar espaço ocupado pelos dados e índices da MV. O lock EX é necessário como na primeira forma do comando REFRESH.

4. Refresh em cascata

Use a cláusula opcional CASCADE para atualizar todas as materialized views das quais a MV fornecida depende. A ordem de refresh garante que cada MV seja tratada uma vez e somente após todas as MVs das quais ela depende.

• Usar CONCURRENTLY com CASCADE requer que todas as MVs a atualizar tenham índice único, caso contrário um erro será gerado.
• Usar DROP DATA com CASCADE excluirá os dados de todas as MVs afetadas.
• O comando coloca um warning com o nome de cada MV que será tratada no status-vector. Isso permite saber antecipadamente (antes do commit) a lista e a ordem das MVs afetadas.

Suporte do gbak

gbak backup não requer suporte especial. Os dados da materialized view não vão para o arquivo de backup - o mesmo que para views regulares.

gbak restore atualiza todas as materialized views no final do processo de restore. As MVs são atualizadas na ordem correta, levando em conta suas dependências entre si (veja refresh em cascata).

Há um novo switch de linha de comando para fazer o gbak não atualizar MVs: -NO_MATVIEWS. Pode ser abreviado até as 4 letras iniciais: -NO_M.

A opção correspondente na Services API para a ação isc_action_svc_restore é isc_spb_res_no_matviews.

Nota: backup com materialized view(s) não pode ser restaurado com versão do Firebird que não suporta MVs. Para migrar para tal versão do Firebird, use o comando ALTER MATERIALIZED VIEW ... TO NOT MATERIALIZED antes do backup. Isso pode ser melhorado posteriormente.

Vídeo e apresentação

Webinar sobre materialized views no Firebird / HQbird:

Apresentação (PDF): Materialized Views in Firebird