Menu de navegação

[Anderson RibeiroParticipante]

Preciso criar uma view materializada que será atualizada em determinados horários do dia utilizando REFRESH FAST ON DEMAND, como se trata de milhões de registros, a opção ON COMMIT, está descartada. Mesmo criando as tabelas de log para as tabelas de origem, a view não está sendo atualizada quando ocorre inserts nas tabelas de origem.

Ao verificar os logs de execução do job, encontro o log a seguir:

“ORA-12004: REFRESH FAST não pode ser utilizado para view materializada “DESV”.”VM_LOG_EMPRESTIMO”
ORA-06512: em “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3012
ORA-06512: em “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2424
ORA-06512: em “SYS.DBMS_SNAPSHOT_KKXRCA”, line 88
ORA-06512: em “SYS.DBMS_SNAPSHOT_KKXRCA”, line 253
ORA-06512: em “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2405
ORA-06512: em “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2968
ORA-06512: em “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3255
ORA-06512: em “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3287
ORA-06512: em “SYS.DBMS_SNAPSHOT”, line 16
ORA-06512: em “DESV.REFRESH_VM_LOG_EMPRESTIMO”, line 4
”

A seguir, estão todos os códigos:

— criação da view materializada com a opção refresh force on demand (depois é que altero para fast)

create materialized view vm_log_emprestimo
refresh force on demand
as select u.nomeusuario, l.nomelivro, a.nomeautor ,e.dataemprestimo, e.datadevolucao
from usuario u join emprestimo e
on (u.codusuario = e.codusuario) join livro l
on (e.codlivro = l.codlivro) join autor a
on(l.codautor = a.codautor);

— criação das tabelas de log

CREATE MATERIALIZED VIEW log ON usuario WITH ROWID;
CREATE MATERIALIZED VIEW log ON emprestimo WITH ROWID;
CREATE MATERIALIZED VIEW log ON livro WITH ROWID;
CREATE MATERIALIZED VIEW log ON autor WITH ROWID;

— alteração da view para refresh fast on demand

ALTER MATERIALIZED VIEW vm_log_emprestimo REFRESH FAST ON DEMAND;

— criação da procedure para executar o refresh na view

CREATE OR REPLACE PROCEDURE refresh_vm_log_emprestimo
AS
BEGIN
    DBMS_MVIEW.REFRESH('vm_log_emprestimo');
END;
/

— criação do job que vai executar a procedure

BEGIN
dbms_scheduler.create_job (
   job_name           =>  'mview_vm_log_emprestimo',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'refresh_vm_log_emprestimo',
   start_date         =>  SYSTIMESTAMP,
   end_date           => NULL,
   repeat_interval    =>  'FREQ=DAILY; byhour=18,20,22,00',
   enabled            =>  TRUE
);
END;
/

[José Laurindo ChiappaModerador]

Tudo jóia ? Então, pra começo de conversa, a Documentação Oracle (https://docs.oracle.com/database/121/DWHSG/basicmv.htm#GUID-505C24CF-5D56-4820-88AA-2221410950E7 é a do 12c mas no mesmo site docs.oracle.com vc encontra de TODAS AS VERSÕES) já aponta que há uma Série de restrições que TEM que ser atendidas para que uma view materializada possa ser FAST REFRESH – as que se referem à query da vm PARECE que vc atende, já que numa olhada no texto da query da Vm (que vc forneceu) não vi nenhuma ref aos comandos restritos) , *** MAS *** vc Não Nos deu o CREATE TABLE das tabelas, então NÃO SABEMOS se vc não está violando nalguma estrutura das tabelas (DATATYPES ? coluna com índice não-b*tree, XMLindex talvez ? ) – começa por aí, nos dando os CREATE TABLEs e eventuais CREATE INDEX, e ESTUDE a documentação indicada, pra ver se tem mais alguma coisa restrita que vc usa sem saber…

SEGUNDO, outra informação CRUCIAL que vc Simplesmente não nos dá é a VERSÃO e Edition de RDBMS Oracle da qual estamos falando : https://community.oracle.com/tech/developers/discussion/951443/mv-without-rowids-specified-in-its-select-statement por exemplo mostra que até a 10gR2 vc TINHA que ter presente os ROWIDs de TODAS AS TABELAS DO JOIN no SELECT da query da VM para poder usar FAST REFRESH, o que (ao que vi) vc NÃO TEM – manda então os DETALHES da SUA versão E EDITION pra gente poder tentar reproduzir e validar aqui….

E TERCEIRO, é tentar um DEBUG/TROUBLESHOOT aí : tenta um REFRESH COMPLETE FULL nessa view materializada só pra confirmar que Realmente é alguma questão com FAST REFRESH só e apenas, E debuga ela com dbms_mview.explain_mview , que nem https://www.orafaq.com/node/831 e https://www.sqlsnippets.com/en/topic-12884.html mostram….

Abraços,

Chiappa

[Anderson RibeiroParticipante]

Olá Chiappa, estou usando o Oracle 11g Enterprise Edition. VOu verificar esses links que forneceu para ver se deixei passar algo.

Segue as tabelas envolvidas

CREATE TABLE livro (
    codLivro          NUMBER         NOT NULL,
nomeLivro         VARCHAR2(40)     NOT NULL,
quantidade        NUMBER         NOT NULL,
codEditora        NUMBER         NOT NULL,
ISBN              VARCHAR2(20)     NOT NULL,
codItem           NUMBER         NOT NULL,
codAutor          NUMBER         NOT NULL,
codCategoria      NUMBER  NOT NULL
);

CREATE TABLE autor (
codAutor  NUMBER         NOT NULL,
nomeAutor         VARCHAR2(40)     NOT NULL
);

CREATE TABLE acervo (
    codItem           NUMBER         NOT NULL,
ISBN              VARCHAR2(10)     NOT NULL
);

CREATE TABLE categoria (
    codCategoria      NUMBER         NOT NULL,
nomeCategoria     VARCHAR2(20)     NOT NULL
);

CREATE TABLE editora (
    codEditora        NUMBER         NOT NULL,
nomeEditora       VARCHAR2(20)     NOT NULL,
telefone          VARCHAR2(10),
editora_ativa     CHAR(1)         NOT NULL
);

CREATE TABLE emprestimo (
    codEmprestimo     NUMBER         NOT NULL,
codUsuario        NUMBER         NOT NULL,
codLivro          NUMBER         NOT NULL,
dataEmprestimo    DATE            NOT NULL,
dataDevolucao     DATE            NOT NULL
);

CREATE TABLE usuario (
codUsuario       NUMBER          NOT NULL,
nomeUsuario      VARCHAR2(40)     NOT NULL,
sexo             CHAR(1)          NOT NULL,
telefone         VARCHAR2(10)     NOT NULL,
email            VARCHAR2(50)
);

--criação das constraints

ALTER TABLE livro ADD PRIMARY KEY(codLivro);

ALTER TABLE autor ADD PRIMARY KEY(codAutor);

ALTER TABLE acervo ADD PRIMARY KEY(codItem, ISBN);

ALTER TABLE categoria ADD PRIMARY KEY(codCategoria);

ALTER TABLE editora ADD PRIMARY KEY(codEditora);

ALTER TABLE emprestimo ADD PRIMARY KEY(codEmprestimo);

ALTER TABLE usuario ADD PRIMARY KEY(codUsuario);

ALTER TABLE emprestimo ADD FOREIGN KEY(codUsuario) REFERENCES usuario(codUsuario);

ALTER TABLE emprestimo ADD FOREIGN KEY(codLivro) REFERENCES livro(codLivro);

ALTER TABLE livro ADD FOREIGN KEY(codItem, ISBN) REFERENCES acervo(codItem, ISBN);

ALTER TABLE livro ADD FOREIGN KEY(codCategoria) REFERENCES categoria(codCategoria);

ALTER TABLE livro ADD FOREIGN KEY(codEditora) REFERENCES editora(codEditora);

ALTER TABLE livro ADD FOREIGN KEY(codAutor) REFERENCES autor(codAutor);

ALTER TABLE usuario ADD CONSTRAINT sexo_c1 CHECK (sexo IN('F','M'));

ALTER TABLE editora ADD CONSTRAINT active_ck CHECK (editora_ativa IN('S','N'));

--criação de sequences

CREATE SEQUENCE seqUsuario START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE seqAutor START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE seqCategoria START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE seqEditora START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE seqAcervo START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE seqLivro START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE seqEmprestimo START WITH 1 INCREMENT BY 1;

[Anderson RibeiroParticipante]

Uma correção: a versão é Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 – Production

[José Laurindo ChiappaModerador]

Oi : fiz alguns testes e ficaram inconclusivos, mas no exemplo final do link https://www.orafaq.com/node/831 que eu tinha indicado antes, na MV final que também (como vc) fazia um JOIN foi usado :

CREATE MATERIALIZED VIEW LOG ON scott.emp
WITH SEQUENCE, ROWID (JOB, DEPTNO, SAL)
INCLUDING NEW VALUES;

==> ou seja, foi incluído ROWID para a LISTA DE COLUNAS usada, e isso junto com a SEQUENCE – desconfio que o que te falta é algo nesse estilo, é algum componente mais do materialized view log : isso seria CONSISTENTE com essa msg de ORA-12004: REFRESH FAST não pode ser utilizado para view materializada””- não é que o exigido materialized view log não exista, ele não existe COm as propriedades necessários para o REFRESH FAST…

E torno a recomendar, tentar um DEBUG/TROUBLESHOOT aí : experimenta um REFRESH COMPLETE FULL nessa view materializada só pra confirmar que Realmente é alguma questão com FAST REFRESH só e apenas, E debuga ela com dbms_mview.explain_mview …

[Anderson RibeiroParticipante]

Oi Chiappa, estou fazendo algumas tentativas aqui.

Primeiro modifiquei as tabelas de log das tabelas de origem:

CREATE MATERIALIZED VIEW log ON usuario WITH primary key, rowid, sequence including new values;
CREATE MATERIALIZED VIEW log ON emprestimo WITH primary key, rowid, sequence including new values;
CREATE MATERIALIZED VIEW log ON livro WITH primary key, rowid, sequence including new values;
CREATE MATERIALIZED VIEW log ON autor WITH primary key, rowid, sequence including new values;

Depois ao criar a view como fast, obtenho esse erro:

ORA-12015: não pode criar uma view materializada de atualização rápida a partir de uma consulta complexa
12015. 00000 – “cannot create a fast refresh materialized view from a complex query”
*Cause: Neither ROWIDs and nor primary key constraints are supported for
complex queries.
*Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE
option or create a simple materialized view.

[Anderson RibeiroParticipante]

Aí criei a view como force mesmo para executar o debug

MV_REPORT

Not Capable of:

REFRESH_COMPLETE
nenhuma restrição de chave primária na

Tentei algumas variações na criação das tabelas de log passando como parâmetro apenas rowid e os três rowid, sequence e primary key junto com o including new values mas ainda não obtive sucesso ao criar a view com fast.

Só pode ser algo com essas tabelas de criação de log…

[Anderson RibeiroParticipante]

Estou com problema na plataforma para inserir código SQL

Então tive que postar no pastebin

https://pastebin.com/BW4AgHtL

E o resultado que obtenho:

MV_REPORT

Capable of:

REFRESH_COMPLETE

Not Capable of:

REFRESH_FAST

REFRESH_F

[José Laurindo ChiappaModerador]

BLz ? Então, não tive muito tempo pra fazer meus testes, mas além de experimentar com outras formas na criação dos MV logs, eu Ainda reforço o que falei antes, ie : Experimentar incluir os ROWIDs e as colunas PK das tabelas consultadas na própria Materialized View, e validar TAMBÉM as opções na criação da MV em si, como por exemplo ter refresh em commit….
Outro ponto que é POUCO provável mas vale a pena ser explorado é usar a sintaxe de JOIN tradicional na query da MV ao invés do ANSI JOIN, tirando os comandos de JOIN e colocando as condições de JOIN no WHERE – é bem POUCO provável que isso influencie (de modo geral os bugs refferentes à ANSI JOINs já foram corrigidos muito antes do 12c que vc usa) mas experimenta lá…

Abraços,

Chiappa

[MottaParticipante]

Este tópico é o que interessa a fóruns pois pode ser útil à outros, lá no StackOverflowPt ele foi ignorado , talvez na parte de Bancos (só em inglês) desse em algo como aqui.

Tive um problema bem semelhante a publicação exigida , resolvi com uma view alimentando uma tabela que é atualizada com delete/insert , na tabela criei índices para as consustas principais.

Não sei se uma tabela é melhor que uma view materializada , @Chiappa o que diz a melhor prática ?

Na época não pensei na VM por desconhecer a impkementação e a solução da tabela atendeu bem, a atualização é custosa mas fazemos em hora de máquina ociosa.

[Anderson RibeiroParticipante]

Olá Chiappa e Motta,

Tentei de várias formas sugeridas até então e falhei miseravelmente com o REFRESH FAST.

Um saída que achei foi criar a view como REFRESH FORCE ON DEMAND, realizei alguns testes de insert, update e delete e está funcionando. Não precisei nem criar view de log.

Uma última dúvida, uma view materializada criada com REFRESH FORCE ON DEMAND, a cada vez que o job for acionado para que a procedure dê o refresh na view, a atualização da view será feita de forma incremental ou a view será carregada por inteiro novamente?

Obs: estou com receio de ter a segunda resposta, pois o cenário é de milhões de linhas e essa carga tem que ser feita de forma incremental a cada doze horas.

[José Laurindo ChiappaModerador]

“Uma correção: a versão é Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 – Production”

Taí um ponto que PODE ser significativo : cfrme https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/upgrade-validation.html mostra, em princípio isso de Extreme Performance Edition **** NÃO É **** um SGBD Oracle COMUM, mas sim um SGBD Oracle EM CLOUD, talvez até um Autonomous Database… Isso é importante porque há SIM diferenças Fundamentais em alguns casos de database Oracle “comum”, on-premise, ao se comparar com database CLOUD….
Bom, não tenho um banco CLOUD fácil aqui no momento mas vou continuar fazendo alguns testes aqui nos meus on-premise, vamos ver… Porém, se vc TEM suporte na Oracle para esse database, eu recomendo MUITO que vc acione o Suporte Oracle, até pra confirmar essas eventuais diffs de cloud x on-premise…

[José Laurindo ChiappaModerador]

“Tentei de várias formas sugeridas até então e falhei miseravelmente com o REFRESH FAST.”

tá, mas essas ‘várias formas’ ** INCLUÍRAM, tal como sugerido, tentar INCLUIR OS ROWIDs na query, simplfificar/re-escrever a query sem ANSI JOIN, fazer testes inicialmente com a view materializada concenctrando dados de uma só tabela, depois adicionando uma segunda tabela na query e transformando assim num JOIN, depois adicionando uma teceira tabela, etc ?? Plz conta pra gente se vc FEZ ou NÃO esses testes….

“Um saída que achei foi criar a view como REFRESH FORCE ON DEMAND, realizei alguns testes de insert, update e delete e está funcionando. Não precisei nem criar view de log.”
Bom, plz NOTAR que quando vc escreve “REFRESH FORCE ON DEMAND”, vc só está dizendo apenas que o REFRESH (a atualização dos dados) vai ser feita Forçadamente (ie, não importa se os logs eventualmente disserem que os dados estão frescos ainda, vai na força e atualiza) E essa parte do ON DEMAND significa que o REFRESH não vai ser automático… EM NENHUM LUGAR vc Indicou se o REFRESH é completo ou não, ok ?? E no caso, REFRESH INCREMENTAL implica em FAST REFRESH, esse FAST (que quer dizer Rápido) JUSTAMENTE é Rápido PORQUE não precisa reconstruir/re-inserir os dados todos…
Quando vc NÃO INDICA ALGO, tal como vc fez, vc está Confiando nos DEFAULTs, e DEFAULTs podem sim mudar…. SUPONDO que o default seja COMPLETE (que é o refresh FULL) aí na sua versáo, sim, faz sentido vc não ter precisado de um mv log : a funcionalidade quase que ÚNICA do materialized view log é permitir um LOG DOS DADOS QUE MUDARAM, que aí comparando com os dados presentes na mv é fácil para o RDBMS atualizar só o que mudou…

“Uma última dúvida, uma view materializada criada com REFRESH FORCE ON DEMAND, a cada vez que o job for acionado para que a procedure dê o refresh na view, a atualização da view será feita de forma incremental ou a view será carregada por inteiro novamente”
vide a resposta acima, sem materialized views log, como é que o RDBMS saberia QUAIS dados foram alterados, se não fosse lendo as tabelas na íntegra e comparando com os dados dentro da view materializada ?? imho vc VAI SIM TER um refresh full em mãos….

“Obs: estou com receio de ter a segunda resposta, pois o cenário é de milhões de linhas e essa carga tem que ser feita de forma incremental a cada doze horas.”
nem importa TANTO assim a qtdade de linhas mas sim SE O SEU HARDWARE tem a capacidade de fazer a qtdade de I/Os necessárias dentro da sua janela…. Mas realmente, muito melhor é vc INSISTIR com o debug aí pra ver a possibilidade de refresh fast/parcial…. E DETALHE, veja/teste também, direitinho as possibilidades de RFERESH ON COMMIT, pois (via de regra) além de vc não ter que ter a preocupação de controlar JOB que refresca a view materializada, esse cara implica em ZERO CHANCE de conviver com dados DESATUALIZADOS, já que Automagicamente, quando a transação COMMITA, a mv é atualizada…

[José Laurindo ChiappaModerador]

Oi, Mota :

“Tive um problema bem semelhante a publicação exigida , resolvi com uma view alimentando uma tabela que é atualizada com delete/insert , na tabela criei índices para as consustas principais.

Não sei se uma tabela é melhor que uma view materializada , @Chiappa o que diz a melhor prática ?

Na época não pensei na VM por desconhecer a impkementação e a solução da tabela atendeu bem, a atualização é custosa mas fazemos em hora de máquina ociosa.
”

Com CERTEZA, além de ser mais Custosa ainda tem a questão de vc ficar , no (longo) intervalo entre uma e outra execução do job/procedimento de REFRESH , com os dados DEFASADOS – não tem jeito, em termos de melhores práticas com CERTEZA seria tentar algo no estilo de RERESH FAST ON COMMIT…..

[Anderson RibeiroParticipante]

Oi Chiappa, sim tentei incluir colunas de rowid na consulta, fazer as junções no where e também de criar a view com uma única tabela, todas essas tentativas falharam. Criei as tabelas de log com várias opções de parâmetros diferentes e não funcionou.

EM NENHUM LUGAR vc Indicou se o REFRESH é completo ou não, ok ??

Primeiramente a view vai ter que ser criada pegando todos os dados das tabelas de origem, daí para frente o refresh da view terá que ocorrer de forma incremental a cada 12 horas

O refresh por commit está descartado, pois colocaram como justificativa que quando a transação está aberta pela aplicação ela só vai fechar após a atualização da view materializada, o que pode gerar um impacto que não se sabe mensurar. E eu também não tenho ideia disso.

O debug que fiz que já postei anteriormente foi no SQL Developer, já que não tenho acesso ao terminal para o sqlplus, então segui um link que postou logo no começo, aí criei a tabela MV_CAPABILITIES_TABLE e a função my_mv_capabilities, ao debugar é exibido isso aqui

MV_REPORT
Capable of:

REFRESH_COMPLETE

Not Capable of:

REFRESH_FAST

REFRESH_F

A única forma que conseguir fazer executar até então é pelo refresh force on demand via job.

[Autor]

Posts