Views Oracle Database

Apresentação sobre o uso Views no Oracle Database. Inclui a criação, alteração e exclusão de views simples e complexas e views materializáveis.

1. Instituto Federal do Sudeste
de Minas Gerais
Tecnologia em Sistemas para Internet

2. Views - Oracle Database
Alunos: Armando Assunção
Richardson William
Samuel Gonçalves
Wagner Almeida
2

3. Views
●Consultas predefinidas baseadas em uma ou mais tabelas.
●Podem receber consultas e manipular dados assim como uma tabela.
3

4. Vantagens ao utilizar Views
●Restringir acesso aos dados;
●Realizar consultas complexas de forma mais simplificada;
●Oferecer independência de dados.
4

5. Tipos de Views
●Views Simples
●Views Complexas
●Views Materializadas
5

6. 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

7. Views Complexas
●Recupera linhas de várias tabelas;
●Contém funções de grupo;
●Nem sempre permitem operações DML.
7

8. 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

9. 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

10. 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

11. 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

12. 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

13. Views Simples
13

14. Criando View Simples
CREATE VIEW func_dptos
AS SELECT id_func, nome_func, salario
FROM funcionarios;

14

15. Criando View Simples
●Cria a View FUNC_DPTOS baseada na tabela FUNCIONARIOS;
● A View criada não possui qualquer restrição.
15

16. Criando View Simples
CREATE VIEW func_dptos_read
AS SELECT id_func, nome_func, salario
FROM funcionarios
WITH READ ONLY constraint apenas_leitura;

16

17. 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

18. 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

19. 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

20. Criando View Simples
CREATE VIEW func_dptos_alias(Cod_Func, Nome_Func, Salario_Func)
AS SELECT id_func, nome_func, salario
FROM funcionarios;
20

21. 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

22. 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

23. 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

24. Criando View Simples
CREATE FORCE VIEW V_Grupo
AS SELECT grupo_id, descricao
FROM grupo;

24

25. 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

26. Views Complexas
26

27. 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

28. 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

29. 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

30. 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

31. Alterando Views Simples e Complexas
CREATE OR REPLACE VIEW func_dptos
AS SELECT id_func, nome_func, email, salario
FROM funcionarios;
31

32. 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

33. 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

34. 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

35. 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

36. Views Materializadas
36

37. 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

38. Tipos
Views materializadas podem ser:
●Somente leitura (Read-only)
●Atualizável (Updatable)
●Gravável (Writeable)
38

39. Sintaxe Básica
CREATE MATERIALIZED VIEW NOME_DA_VIEW
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[ [ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...; 39

40. 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

41. 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

42. 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

43. 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

44. 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

45. 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

46. 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

47. 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

48. Criando Views Materializadas
CREATE MATERIALIZED VIEW func_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM funcionarios;
48

49. 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

50. 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

51. 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

52. 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

53. 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

54. Alterando Views Materializadas
A sintaxe para se modificar uma view materializada é:
ALTER MATERIALIZED VIEW <NOME>
… MUITOS PARÂMETROS
54

55. 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

56. Alterando Views Materializadas
ALTER MATERIALIZED VIEW func_mv
REFRESH COMPLETE
START WITH TRUNC(SYSDATE + 1) + 12/24
NEXT SYSDATE + 1;
56

57. 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

58. “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

59. Removendo Views Materializadas
●Para poder remover a View Materializada utiliza-se o seguinte comando:

DROP MATERIALIZED VIEW FUNC_MV;
59

60. Referências
●oracle-base.com
●docs.oracle.com
●www.devmedia.com.br
●aprendaplsql.com
60