Views Oracle Database

Prévia do material em texto

Instituto Federal do Sudeste 
de Minas Gerais
Tecnologia em Sistemas para Internet
Views - Oracle Database
Alunos: Armando Assunção
Richardson William
Samuel Gonçalves
Wagner Almeida
2
Views
● Consultas predefinidas baseadas em uma ou mais tabelas.
● Podem receber consultas e manipular dados assim como uma tabela.
3
Vantagens ao utilizar Views
● Restringir acesso aos dados;
● Realizar consultas complexas de forma mais simplificada;
● Oferecer independência de dados.
4
Tipos de Views
● Views Simples
● Views Complexas
● Views Materializadas
5
Views Simples
● Recupera linhas de uma única tabela base;
● Não contém funções grupo;
● Aceita todas as operações DML (Linguagem de Manipulação de Dados).
6
Views Complexas
● Recupera linhas de várias tabelas;
● Contém funções de grupo;
● Nem sempre permitem operações DML.
7
Views Materializadas
● É uma View onde o seu resultado é armazenado no banco;
● É uma tabela real no banco de dados que é atualizada ao ocorrer alguma 
atualização nas tabelas base;
● Como é uma View, pode ser simples ou complexa.
8
Criação de Views Simples e Complexas
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW Nome_Da_View
[ (alias [, alias ] ... ) ]
AS subquery
[ WITH CHECK OPTION [ CONSTRAINT Nome_Constraint ] ]
[ WITH READ ONLY [ CONSTRAINT Nome_Constraint ] ];
9
Parâmetros
● OR REPLACE - A View deverá ser alterada, caso ela já exista;
● FORCE - Força a criação da View mesmo que as tabelas base não existam;
● NOFORCE - Não permite a criação da View se as tabelas base não 
existirem. É o padrão na criação, ou seja, se o FORCE não for declarado o 
NOFORCE é embutido de forma implícita;
10
Parâmetros
● NOME_DA_VIEW - É o nome da visão;
● ALIAS - É o apelido de uma expressão na subconsulta. Deve haver o 
mesmo número de apelidos e de expressões na subconsulta;
● SUBQUERY - É a subconsulta que recupera as linhas das tabelas base. Se 
você estiver usando alias(apelidos), pode usá-los na lista após a instrução 
SELECT.
11
Parâmetros
● WITH CHECK OPTION - Somente as linhas que seriam recuperadas na 
sub-consulta podem ser inseridas, atualizadas ou removidas. Se essa 
cláusula não for utilizada, as linhas não são verificadas;
● NOME_CONSTRAINT - É o nome que será atribuído à restrição WITH 
CHECK OPTION ou WITH READ ONLY;
● WITH READ ONLY - Significa que só podem ser consultadas as linhas da 
tabela base (Não permite operações DML).
12
Views Simples
13
Criando View Simples
CREATE VIEW func_dptos 
AS SELECT id_func, nome_func, salario 
FROM funcionarios;
14
Criando View Simples
● Cria a View FUNC_DPTOS baseada na tabela FUNCIONARIOS;
● A View criada não possui qualquer restrição.
15
Criando View Simples
CREATE VIEW func_dptos_read 
AS SELECT id_func, nome_func, salario 
FROM funcionarios 
WITH READ ONLY constraint apenas_leitura;
16
Criando View Simples
● Diferente do exemplo anterior, a View FUNC_DPTOS_READ só 
disponibilizará consulta aos dados, não sendo possível utilizar operações 
DML (delete, insert, update).
17
Criando View Simples
CREATE VIEW func_option_const 
AS SELECT id_func, nome_func, salario 
FROM funcionarios 
WHERE id_dpto = 4 
WITH CHECK OPTION CONSTRAINT func_4;
18
Criando View Simples
● A View FUNC_OPTION_CONST possui a cláusula WITH CHEK OPTION, 
onde foi criada a constraint func_4; 
● Essa constraint se baseia no que está na cláusula WHERE, que nesse caso 
só irá recuperar as linhas onde o id_dpto seja igual a 4. 
● As operações DML só poderão ser aplicadas onde o código do 
departamento (id_dpto) for igual a 4.
19
Criando View Simples
CREATE VIEW func_dptos_alias(Cod_Func, Nome_Func, Salario_Func)
AS SELECT id_func, nome_func, salario
FROM funcionarios;
20
Criando View Simples
● A View FUNC_DPTOS_ALIAS utiliza apelidos para as colunas que irão 
compor a View;
● Dessa forma, quando forem aplicadas operações DML sobre essa View, 
pode-se utilizar os apelidos usados em sua criação. 
● O número de apelidos deve corresponder ao número de colunas 
envolvidas na subconsulta.
21
Criando View Simples
CREATE VIEW func_dptos_alias_as 
AS SELECT id_func as Cod_Func, 
 nome_func as Nome_Func, 
 salario as Salario_Func 
FROM funcionarios;
22
Criando View Simples
● Na View FUNC_DPTOS_ALIAS_AS possui o mesmo efeito da View do 
exemplo anterior, diferenciando apenas a forma como os apelidos foram 
passados; 
● Nessa View os apelidos para as colunas foram passados direto na 
subconsulta. 
23
Criando View Simples
CREATE FORCE VIEW V_Grupo 
AS SELECT grupo_id, descricao 
FROM grupo;
24
Criando View Simples
● A View V_GRUPO foi baseada na tabela GRUPO, porém a mesma ainda 
não foi criada no banco de dados, por isso a necessidade do parâmetro 
FORCE, para que a criação seja feita independente de existir a tabela base.
25
Views Complexas
26
Criando View Complexa
CREATE VIEW func_e_dpto
AS SELECT id_func, nome_func, nome_dpto
FROM funcionarios F, departamentos D
WHERE F.id_dpto = D.id_dpto;
27
Criando View Complexa
● A View FUNC_E_DPTO foi criada baseada em duas tabelas 
(FUNCIONARIOS e DEPARTAMENTOS); 
● Nesse momento deixa de ser uma View simples e passa a ser uma View 
complexa, pois está sendo aplicada uma subconsulta sobre duas tabelas.
28
Criando View Complexa
CREATE VIEW dpto_media_salario 
AS SELECT id_dpto as Cod_Departamento, 
 CAST(AVG(salario) as NUMBER(6,2)) as Media_Salario 
FROM funcionarios 
GROUP BY id_dpto;
29
Criando View Complexa
● A View DPTO_MEDIA_SALARIO foi criada baseada na tabela 
FUNCIONARIOS; 
● Mesmo sendo utilizada apenas uma tabela, foi usada a função de 
agrupamento AVG e os registros estão sendo agrupados através do 
GROUP BY;
● A utilização da função de agrupamento torna esta View Complexa.
30
Alterando Views Simples e Complexas
CREATE OR REPLACE VIEW func_dptos 
AS SELECT id_func, nome_func, email, salario 
FROM funcionarios;
31
Alterando Views Simples e Complexas
● Para alterar uma View, é necessário usar OR REPLACE que é a opção para 
substituir uma view já existente;
● A view FUNC_DPTOS está sendo alterada com o OR REPLACE, nesse caso 
está sendo inserida a coluna EMAIL que não estava na criação da View 
anteriormente;
● A FUNC_DPTOS será substituída já com a nova coluna que foi incluída na 
subconsulta.
32
Alterando Views Simples e Complexas
CREATE OR REPLACE VIEW func_e_dpto 
AS SELECT nome_func, nome_dpto 
FROM funcionarios F, departamentos D 
WHERE D.id_dpto = F.id_dpto;
33
Alterando Views Simples e Complexas
● A View FUNC_E_DPTO está sendo alterada com OR REPLACE, nesse caso 
está sendo excluída a coluna id_func que foi inserida na criação da View 
(Slide 27);
● A View FUNC_E_DPTO será substituída com a coluna id_func já removida 
da subconsulta.
34
Removendo Views Simples e Complexas
● Quando uma View não é mais necessária, pode ser removida do mesmo 
modo que outros objetos no Oracle Database.
DROP VIEW func_e_dpto;
35
Views Materializadas
36
Aplicações
● Replicação de dados;
● Manter em cache consultas de alto custo em ambientes de Data 
Warehouse;
● Realiza pré-calculo e armazenamento de dados;
● Procura melhorar a performance de consultas, reconhecendo quando uma 
View Materializada pode ser utilizada.
● É uma subdivisão das Views, portanto, pode ser simples ou complexa.
37
Tipos
Views materializadas podem ser:
● Somente leitura (Read-only)
● Atualizável (Updatable)
● Gravável (Writeable)
38
Sintaxe Básica
CREATE MATERIALIZED VIEW NOME_DA_VIEWBUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[ [ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...; 39
Parâmetros
● BUILD 
○ IMMEDIATE: A view materializada será populada imediatamente
○ DEFERRED: A view materializada será populada no próximo pedido de 
atualização (Refresh)
40
Parâmetros
● REFRESH 
○ FAST: Tenta fazer uma atualização rápida. Deve existir arquivos de 
log relacionado as tabelas de origem.
○ COMPLETE: O segmento de tabela que suporta a View Materializada é 
truncado e repopulado utilizando a consulta associada.
○ FORCE: Tenta realizar uma atualização rápida (REFRESH FAST). Se 
não conseguir, realiza uma atualização completa (REFRESH 
COMPLETE).
41
Parâmetros
● ON COMMIT: A atualização é disparada após alteração nas tabelas base.
● ON DEMAND: A atualização é disparada manualmente.
● [ENABLE | DISABLE] QUERY REWRITE: Diz ao otimizador de custo se a 
View Materializada será considerada para operações de reescrita de 
consultas (query rewrite) ou não.
● ON PREBUILT TABLE: Diz ao banco de dados para utilizar uma tabela 
existente.
42
Otimização por custo - Query Rewrite
● Um dos maiores benefícios de se utilizar Views Materializadas.
● Quando as tabelas têm grande quantidade de dados, consultas mais 
complexas têm custo computacional alto, e podem durar minutos, ou 
horas.
● Aproveita o uso das Views Materializadas para verificar a possibilidade de 
otimização as consultas, utilizando as próprias Views.
● Chamada de Query Rewrite.
43
Query Rewrite
● Transforma uma consulta acessando tabelas ou Views fazendo-as 
acessar uma ou mais Views Materializadas.
● Processo sem intervenção do usuário.
● Uma consulta passa por testes para verificar a possibilidade da 
otimização. A consulta com menor custo será a escolhida.
● Pode ter custo alto em relação a poder de processamento e tempo de 
resposta.
44
Query Rewrite - Precisão
Existem três níveis de integridade que podem ser escolhidos:
● ENFORCED: modo padrão, usa apenas dados atualizados.
● TRUSTED: o otimizador acredita que os dados estão corretos.
● STALE TOLERATED: utiliza dados atualizados em conjunto com dados 
envelhecidos. Oferece o máximo de reescrita, mas aumenta o risco de 
obtenção de dados incorretos.
45
Atualização dos dados
● A Oracle garante a atualização dos dados em uma View Materializada 
após modificações nas tabelas originais.
● Método de atualização: incremental (REFRESH FAST) ou completo 
(COMPLETE).
○ Incremental: necessita de um arquivo de log contendo as 
modificações nas tabelas originais.
● Atualização ocorre ou sobre demanda ou em intervalos de tempo 
definidos.
46
Atualização Incremental: por quê um 
arquivo de log?
● Na atualização incremental de Views Materializadas somente as 
modificações nas tabelas originais serão utilizadas para atualização da 
View Materializada.
● Por isto, deve-se criar um arquivo de log associado a cada uma das 
tabelas envolvidas, assim, cada modificação nas tabelas base será 
armazenada no arquivo de log.
47
Criando Views Materializadas
CREATE MATERIALIZED VIEW func_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM funcionarios;
48
Criando Views Materializadas
● BUILD IMMEDIATE faz com, que a View seja imediatamente populada;
● REFRESH FORCE, faz com que a atualização procure por alterações no 
arquivo de log relacionado a tabela FUNCIONARIOS;
● ON DEMAND informa que a atualização será disparada manualmente.
49
Criando Views Materializadas
CREATE MATERIALIZED VIEW func_mv_repli
BUILD IMMEDIATE REFRESH FORCE
START WITH SYSDATE + 1 NEXT SYSDATE + 1/24
WITH PRIMARY KEY
AS
SELECT nome_func, salario FROM funcionarios;
50
Criando Views Materializadas
● START WITH indica a hora em que a View Materializada começara a ser 
replicada (atualizada), no exemplo utiliza a data e hora do sistema 
(SYSDATE) acrescido de 1 (amanhã);
● NEXT indica o intervalo de replicações a partir do início, no exemplo será 
feita de 1 em 1 hora (SYSDATE + 1/24);
● WITH PRIMARY KEY indica que utilizará a chave primária da tabela base.
51
Criando Views Materializadas
CREATE MATERIALIZED VIEW func_e_dpto_mv
BUILD IMMEDIATE REFRESH FORCE ON COMMIT
AS SELECT nome_func, nome_dpto, salario 
FROM funcionarios F, departamentos D 
WHERE D.id_dpto = F.id_dpto;
52
Criando Views Materializadas
● Cria a View Materializada FUNC_E_DPTO_MV utilizando dados das tabelas 
FUNCIONARIOS e DEPARTAMENTOS;
● BUILD IMMEDIATE popula a View Materializada criada imediatamente;
● REFRESH FORCE faz com que nas atualizações, as alterações sejam 
procuradas nos arquivos de log relacionados as tabelas base;
● ON COMMIT faz com que as atualizações sejam disparadas 
automaticamente quando houver alteração nas tabelas base.
53
Alterando Views Materializadas
A sintaxe para se modificar uma view materializada é:
ALTER MATERIALIZED VIEW <NOME> 
… MUITOS PARÂMETROS
54
Alterando Views Materializadas
Pode-se alterar uma ou mais destas características:
● Características de armazenamento;
● Método, modo ou hora de atualização
● Estrutura
● Habilitar ou desabilitar a reescrita de queries.
55
Alterando Views Materializadas
ALTER MATERIALIZED VIEW func_mv
REFRESH COMPLETE
START WITH TRUNC(SYSDATE + 1) + 12/24
NEXT SYSDATE + 1;
56
Alterando Views Materializadas
● Altera a View Materializada para que ela seja replicada de forma 
automática na base de dados;
● REFRESH COMPLETE para que toda View seja atualizada;
● START WITH TRUNC(SYSDATE + 1) + 12/24 para que a replicação inicie 
no próximo dia às 12:00;
● NEXT SYSDATE + 1 para que a replicação se repita 24 horas após o início. 
57
“Problema” de Alterar Views Materializadas
● Alterar uma View Materializada no banco de dados Oracle não é tão trivial 
quanto alterar uma View.
● Uma rápida solução é destruir a View e criá-la novamente.
58
Removendo Views Materializadas
● Para poder remover a View Materializada utiliza-se o seguinte comando:
DROP MATERIALIZED VIEW FUNC_MV;
59
Referências
● oracle-base.com
● docs.oracle.com
● www.devmedia.com.br
● aprendaplsql.com
60