Modificando Materialized Views

Revisado por Marcelo Pivovar - Solution Architect

Este é mais um artigo da série que irei escrever enquanto me preparo para o exame de atualização do OCM para a versão 12c. Estes artigos serão criados antes da prova e serão baseados apenas nos tópicos do exame.

Indice de artigos do tema Data and Performance Management:

• Modificando Materialized Views
• Transportable Database / Tablespaces
• Criando Tabelas Particionadas no Oracle Database 12c
• Flashback Data Archive
• Resource Manager utilizando o SQL*Plus
• Tune SQL statements
• Perform real application testing
• Criando SQL Plan baselines utilizando o OEM Total Cloud Control 12c

Antes do Oracle Database 12c, o refresh de Materialized Views (MVs) era feito de forma in place. Isto significa que as alterações eram sempre feitas diretamente na Materialized View (MV), através dos seguintes métodos: delete/insert, truncate/insert ou append/insert.

No Oracle Database 12c, nós temos a opção de fazer o refresh da MV de forma out of place. Isto significa que o refresh será realizado sem modificar os dados da MV existente.

Neste método, a atualização é realizada através da criação de uma tabela provisória e em seguida ocorre aplicação de todas as alterações na tabela provisória. Após isso, finalmente a tabela provisória toma o lugar da tabela base antiga da MV.

Este novo mecanismo de atualização oferece maior disponibilidade, pois a tabela base da MV permanece intacta e também ocorre a melhora no desempenho do refresh da MV.

Out-Of-Place refresh

Este tipo de refresh é inicializado utilizando a DBMS_MVIEW.REFRESH conforme exemplo abaixo:

DBMS_MVIEW.REFRESH('MVIEW_NAME', method => '<em>REFRESH_METHOD</em>',  
 
 atomic_refresh => FALSE, out_of_place => TRUE);
 
 MVIEW_NAME: Nome da <em>Materialized View.</em>     

REFRESH_METHOD: Complete ('C') ou Fast ('F') ou PCT ('P') ou Force ('?').   
    
  ATOMIC_REFRESH: Deve-se utilizar FALSE para <em>refresh</em> do tipo <em>out-of-place</em>.
  
  OUT-OF-PLACE: Deve-se utilizar o valor TRUE.

Estimando o tamanho e número de registros da MV:

Criando a MV:

Verificando os objetos criados:

Realizando o refresh out-of-place:

Durante o refresh, podemos observar que uma nova tabela é criada. Esta tabela possui o objeto id da tabela existente (185B5) no nome:

Após a finalização do refresh, podemos observar que a tabela base da MV mudou de objeto id, o que indica que um novo objeto foi criado:

Query Rewrite

Query rewrite é o mecanismo que permite ao Oracle automaticamente reescrever as queries para utilizar as MVs.

Verificando os parâmetros:

O parâmetro QUERY_REWRITE_ENABLED deve estar definido como TRUE (default) ou FORCE. O parâmetro QUERY_REWRITE_INTEGRITY é opcional, mas deve estar definido como STALE_TOLERATED, TRUSTED ou ENFORCED (default).

Neste exemplo, irei utilizar o HINT rewrite_or_error para verificar se a MV realmente está sendo utilizada:

Conforme podemos observar no erro acima, a MV não está sendo utilizada.

Para descobrir o motivo, podemos utilizar a package dbms_mview.explain_rewrite:

Após isso basta verificar o que foi gerado na tabela REWRITE_TABLE:

Essa verificação também pode ser feita pelo Enterprise Manager Total Cloud Control 12c (EM12c).

Caminho para acesso a funcionalidade:

Selecionar a MV, escolher a opção “Explain Rewrite” e clicar em “Go”:

Informar a query que será executada no banco de dados:

Através da mensagem acima, podemos concluir que faltou utilizar a cláusula ENABLE QUERY REWRITE na criação da MV:

Após recriar a MV com cláusula ENABLE QUERY REWRITE, podemos observar que o mecanismo de Query rewrite funcionou corretamente:

Verificando o plano de execução:

Verificando através do EM12c:

Fast Refresh

Fast refresh é uma atualização de forma incremental.

Para verificar se podemos utilizar o Fast refresh, podemos utilizar a package dbms_mview.explain_mview:

Após isso, basta verificar o que foi gerado na tabela MV_CAPABILITIES_TABLE:

Essa verificação também pode ser feita pelo Enterprise Manager Total Cloud Control 12c (EM12c), que é a ferramenta que iremos utilizar nesse exemplo.

Caminho para acesso a funcionalidade:

Selecionar a MV, escolher a opção “Explain Materialized View” e clicar em “Go”:

Conforme podemos observar, o Fast refresh não está habilitado para esta MV:

O motivo é a falta de Materialized View Logs (MV Logs), que podem ser criados no caminho abaixo:

Escolher a opção “Create”:

Escolher a tabela SH.CUSTOMERS:

Comando DDL para a criação da MV Log:

Repetir o processo para a tabela SH.SALES:

Comando DDL para a criação da MV Log:

MV Logs geradas:

Conforme podemos observar abaixo, agora é possível utilizar a opção Fast Refresh nessa MV.

Alex Zaballa, formado em Análise de Sistemas, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Servidores de Aplicação e Sistemas Operacionais; trabalha com Oracle há 16 anos, é Oracle ACE Director, certificado OCM Database 11G/Cloud e conta com mais de 200 outras certificações em produtos da Oracle.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.