Perguntas[materialized-view](dba)

Esta é uma continuação da minha pergunta antiga Múltiplas "REFRESH MATERIALIZED VIEW" simultâneas: como gerenciar?

Agora, infelizmente, estou lutando com a coisa real depois de ter sido mais teórico inicialmente :)

Eu tenho um programa que envia muitas solicitações e, finalmente, atualiza uma visão materializada. Infelizmente, às vezes, dois jobs simultâneos solicitam sobreposição CREATE MATERIALIZED VIEW milestones_files(a visualização é muito grande e leva vários minutos para ser atualizada), então o último falha (obviamente), o que cancela toda a transação (o que é ruim, porque algumas atualizações do banco de dados também são descartadas ).

Aqui está o log quando o erro ocorre (sem todas as linhas SQL para abreviar):

2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone ERREUR:  Bloquage mortel détecté
2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone DÉTAIL:  Le processus 29004 attend AccessExclusiveLock sur relation 181019 de la base de données 19044 ; bloqué par le processus 29174.
2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone ASTUCE :  Voir les journaux applicatifs du serveur pour les détails sur la requête.
2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone INSTRUCTION :  CREATE MATERIALIZED VIEW milestones_files_29000 AS SELECT sa.id AS archive,
2022-03-22 11:17:37.691 UTC [29243] storiq@storiqone ERREUR:  could not open relation with OID 181019
2022-03-22 11:17:37.691 UTC [29243] storiq@storiqone INSTRUCTION :  SELECT REGEXP_REPLACE(pg_catalog.pg_get_ruledef(r.oid, TRUE), '^.*DO INSTEAD  (SELECT.*);$', '\1') FROM pg_catalog.pg_rewrite r WHERE r.ev_class = ( SELECT oid FROM pg_class WHERE relname = 'milestones_files')
2022-03-22 11:17:37.693 UTC [29243] storiq@storiqone ERREUR:  la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc
2022-03-22 11:17:37.693 UTC [29243] storiq@storiqone INSTRUCTION :  UPDATE archivetoarchivemirror SET lastupdate = NOW(), jobrun = $1 WHERE archive = $2 OR jobrun = $1
2022-03-22 11:17:37.696 UTC [29243] storiq@storiqone ERREUR:  l'instruction préparée « update_archivemirrors » n'existe pas
2022-03-22 11:17:39.510 UTC [29004] storiq@storiqone ERREUR:  la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc
2022-03-22 11:17:39.510 UTC [29004] storiq@storiqone INSTRUCTION :  UPDATE archivetoarchivemirror SET lastupdate = NOW(), jobrun = $1 WHERE archive = $2 OR jobrun = $1
2022-03-22 11:17:39.510 UTC [29004] storiq@storiqone ERREUR:  l'instruction préparée « update_archivemirrors » n'existe pas

É claro que eu poderia simplesmente colocar o CREATE MATERIALIZED VIEWcomando fora da transação principal, no entanto, a atualização mais recente ainda falharia, portanto, a exibição não refletiria as últimas alterações no banco de dados, o que ainda é um problema (menos sério).

Idealmente, eu gostaria que a atualização mais recente tivesse precedência, mas sem falhar na transação já em execução no outro processo (o que seria o caso se eu simplesmente matasse o outro processo após a verificação de bloqueios, conforme Kill concorrente materialized view refresh PID ) .

A partir da documentação, não parece que adicionar a CONCURRENTLYopção também resolveria meu problema (que não é sobre SELECTs simultâneos).

Qualquer sugestão bem vinda. É claro que eu poderia tentar implementar a solução proposta na minha pergunta antiga, embora fosse particularmente complexa, dadas as consultas e transações em cascata e o enorme banco de dados ...

Com select matviewname from pg_matviewseu recebo todas as visualizações materializadas no banco de dados. Como posso filtrar o resultado para obter apenas aqueles que o usuário atual tem permissão de seleção (= permissão de leitura)?

Eu tenho uma visão complexa com cálculos, mas uma linha causa problemas

ROUND((((latest_revenue::numeric/ oper_reve_3y_old::numeric) ^ (1/3::numeric)) -1::numeric)*100::numeric)

Quando executo a consulta, o cálculo funciona bem, quando uso em view, tudo funciona bem, mas se tento criar view materializada com essa instrução, recebo erro

"um número negativo elevado a uma potência não inteira produz um resultado complexo"

existe alguma maneira de como posso usar isso na visão materializada? ou existe alguma explicação por que isso não funciona apenas para visualização materializada?

Eu tenho uma consulta que é usada em um relatório BIRT (na plataforma Maximo Asset Management da IBM).

Estou pensando em armazenar a consulta em uma visão materializada para melhorar o desempenho e reduzir a carga no sistema.

Pergunta:

Existe uma maneira de fazer o relatório falhar deliberadamente se o MV estiver desatualizado?

• Em outras palavras, preferimos não ter dados no relatório do que dados errados .

Editar:

Eu removi os detalhes excessivos da pergunta.

Criei logs de visualização materializados em uma tabela WORKORDER em um banco de dados Oracle 19c.

create materialized view log on my_workorder_system.workorder with rowid;  

O plano é integrar os registros WORKORDER a um sistema GIS separado (Oracle 18c) por meio de uma visualização materializada.

A pegada:

A tabela WORKORDER possui registros de ordens de serviço espaciais e não espaciais (ISGIS = 0 ou 1). Eu só preciso sincronizar os registros com o banco de dados GIS WHERE ISGIS = 1.

Se entendi corretamente, minha visualização materializada no GIS informará aos logs de MV no sistema de ordem de serviço para limpar os registros de log depois que eles forem sincronizados. No entanto, a sincronização só acontecerá para ordens de serviço em que ISGIS=1. As ordens de serviço em que ISGIS=0 nunca serão sincronizadas — e, portanto, não são necessárias nos logs de MV.

Existe uma maneira de gerar apenas logs de MV para registros que atendem a uma condição?

Por exemplo:

create materialized view log on my_workorder_system.workorder with rowid where ISGISS=1.

Eu criei uma visão materializada entre um sistema primário (Oracle 18c) e um sistema externo (Oracle 19c):

create materialized view primary_system.workorder_mv
build immediate 
refresh force 
on demand 
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder_vw@gistomax --view in external system that is served up by dblink (gistomax)
    --the view looks like this: select * from workorder where isgis=1


create materialized view log on external_system.workorder with rowid;  

Os documentos dizem que as visualizações materializadas devem ser limpas automaticamente:

O Oracle rastreia automaticamente quais linhas em um log de exibição materializada foram usadas durante as atualizações de exibições materializadas e limpa essas linhas do log para que o log não cresça indefinidamente. Como várias visualizações materializadas simples podem usar o mesmo log de visualização materializada, as linhas já usadas para atualizar uma visualização materializada ainda podem ser necessárias para atualizar outra visualização materializada. O Oracle não exclui linhas do log até que todas as visualizações materializadas as tenham usado.

• Não tenho outras visualizações materializadas que façam referência aos logs de MV.
• Aqui estão as propriedades do log MV (Toad) .
• Aqui está o que all_registered_mviewsparece.
• O MV foi sincronizado com sucesso. Se eu criar ou atualizar registros na tabela WORKORDER no sistema externo, os registros serão sincronizados com o sistema primário no agendamento de 30 segundos.

Pergunta:

Até agora, os dados de log do MV não parecem estar limpando automaticamente:

Por que os logs de MV não são limpos automaticamente assim que os registros são sincronizados?

Como posso garantir que uma Visualização Materializada Oracle seja criada como uma visualização materializada "Gravável" e não como uma Visualização "Atualizável"? Eu nunca quero que essas exibições enviem dados de volta à origem e quero uma atualização completa para substituir qualquer coisa que eu altere.

O problema de origem é que um dos bancos de dados remotos não pode suportar logs de exibição materializados, mas as tabelas têm colunas de carimbo de data/hora modificadas pela última vez. Portanto, gostaria de tentar mantê-los atualizados com as instruções Merge filtrando nessa coluna e, ocasionalmente, fazer atualizações normais de replicação se ocorrer algum problema.

eu tenteiCREATE MATERIALIZED VIEW Test.SomeTable_MVW TABLESPACE SOME_DATA BUILD DEFERRED REFRESH COMPLETE FOR UPDATE AS (SELECT ... FROM SomeTable@OverThere);

Mas recebi o erro:

12013. 00000 -  "updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh"
*Cause:    The updatable materialized view query contained a JOIN, SUBQUERY,
           UNION, CONNECT BY, ORDER BY, or GROUP BY clause, or the master
           table did not have a materialized view log.
*Action:   Make the materialized view simpler. If a join is really needed,
           make multiple simple materialized views then put a view on top of
           them. Make sure a materialized view log is created for the master
           table.

Há apenas uma tabela remota. Não entendo por que esse erro contém, or the master table did not have a materialized view logpois é assim que você obtém uma visualização materializada gravável. Pena que não há uma cláusula "FOR WRITEABLE" para quando você sabe que não deseja enviar dados de volta. E o REFRESH COMPLETE deve indicar que não quero empurrar os dados de volta.

Eu tenho uma visão que combina várias tabelas via UNION ALLe lida com alguns erros básicos com os dados. Devido à maneira como os erros são tratados, a visualização é um pouco mais lenta do que eu esperava. Existe alguma maneira de armazenar em cache a saída da exibição e atualizá-la dinamicamente sempre que qualquer uma das tabelas subjacentes for atualizada?

Algumas peculiaridades do meu sistema:

• O banco de dados só vê as atualizações uma vez por dia, fora do expediente. Portanto, uma solução com uso intensivo de recursos é boa, desde que os recursos sejam consumidos durante essas horas de folga.
• Devido à forma como o sistema está configurado, prefiro não limpar os dados durante o carregamento e prefiro deixar as tabelas subjacentes intocadas.
• Tenho índices de suporte apropriados configurados
• Todas as tabelas contêm registros distintos sem sobreposição, então preciso puxá-los todos

Algumas opções que considerei:

• Uma exibição indexada não funcionará porque a exibição depende do operador UNION.
• Não encontrei nenhuma maneira de armazenar em cache a saída de um TVF.
• Eu poderia criar uma tabela de cache e carregá-la manualmente a qualquer momento que a tabela subjacente fosse modificada, mas isso parece uma solução hacky. Acho que também teria que criar um SQL Agent Job para isso, pois configurá-lo como um gatilho envolveria atualizar a tabela milhões de vezes todas as noites enquanto a tabela é substituída.
• Eu poderia viver com o desempenho ruim, mas isso afetaria negativamente os usuários finais.

Aqui está um exemplo simplificado da visualização:

CREATE OR ALTER VIEW AllNamesAndAccountNumbers AS 
SELECT
    x.AccountNumber
    , COALESCE(x.FullName, n.FullName) AS FullName
FROM (
    SELECT
        AccountNumber
        , FullName 
    FROM TableWithNames
    UNION ALL
    SELECT
        AccountNumber
        , NULL AS FullName
    FROM TableMissingNames
) x
LEFT JOIN NameMappings n -- this join is slow due to the table being really huge
ON n.AccountNumber = x.AccountNumber

Oh - se isso é um antipadrão, e eu deveria estar atacando isso de um ângulo fundamentalmente diferente, por favor, diga isso. Estou preocupado que estou olhando para isso errado.

Eu tenho uma tabela grande (> 126 milhões de linhas) e uma necessidade de otimizar um conjunto especializado de consultas, então criei uma visualização indexada na tabela e codifiquei um procedimento que selecionaria na visualização indexada quando as condições fossem favoráveis ​​para isto.

A execução do procedimento demorou mais do que eu esperava, então olhei o plano de execução, e vejo que a consulta que demora mais é a que seleciona na minha view, e quando passo o mouse sobre o elemento que demora mais é um Busca de Índice Agrupado do IC da tabela subjacente. Não o índice na exibição.

Eu entendi que uma exibição indexada era um tipo de tabela virtual que era armazenada em disco e que, quando consultada, não passava a consulta para a tabela subjacente. Não é este o caso?

No SQL Server, a exibição indexada é um inferno de limitações. Mas eu preciso de um. Eu tenho uma função SQLCLR do formatador que cria uma versão embelezada de uma chave de domínio - os usuários querem poder pesquisar em substrings dessa versão embelezada. Portanto, preciso de um índice de texto completo em uma coluna calculada persistente ou em uma exibição materializada.

No entanto, o formatador depende dos dados armazenados em várias tabelas.

Portanto, isso funciona bem para uma exibição, mas não para uma coluna computada persistente porque elas não podem consultar várias tabelas.

Meu método SQLCLR é preciso e determinístico , portanto, deve ser qualificado para uso em exibições indexadas, mas exibições indexadas não podem ter SQLCLR em suas chaves de índice.

Eu poderia reimplementar meu formatador usando a FORMATfunção T-SQL... mas tambémFORMAT é incompatível com exibições indexadas.

As visualizações indexadas podem fazer alguma coisa ? Nunca ?

Estou aberto à abordagem "role sua própria visão materializada com gatilhos" se alguém puder me indicar um bom documento sobre as melhores práticas para fazer isso. Da última vez que tentei, ele saiu do controle e houve uma tremenda duplicação entre o código para inserção e atualização e a inicialização.

Existe alguma maneira que estou perdendo para obter pesquisas de texto com desempenho em dados calculados em alguns milhões de linhas, sem usar uma exibição materializada/indexada ou uma coluna calculada persistente?

O formatador não realiza acesso aos dados. No entanto, os dados que preciso alimentar no formatador para que seja útil virão de várias tabelas (com uma boa junção regular), portanto, não posso resolver esse problema com colunas computadas persistentes. Não consigo digitar a coluna SQLCLR, portanto, não posso usá-la em um índice de texto completo.

Achei que isso seria simples. Colunas persistentes e exibições indexadas são projetadas para realizar cálculos na gravação e para implementar adequadamente o padrão de observador para que as alterações em suas dependências sejam refletidas no valor calculado.