Linguagem SQL: Junção, Subconsulta, Operadores de Conjunto, Visões, Sequences, Índices e Transações

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

IMPLEMENTAÇÃO DE BANCO DE DADOS
Revisão para AV2
Conteúdo Programático desta aula
Revisão para AV2
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO
Um comando SELECT pode fazer uma consulta que traz dados de duas ou mais tabelas. Esse é um processo chamado de junção [join]. As tabelas têm uma coluna em comum que é usado para fazer as junções.
 Sintaxe da Junção 
Sintaxe ANSI
Sintaxe tradicional (não ANSI)
Tipos Junção:
Junção Cruzada ou Irrestrita (Produto Cartesiano)
Junção Interior
Junção Exterior
Auto-Junção
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO INTERIOR – SINTAXE TRADICIONAL
Projetando Colunas
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO INTERIOR – SINTAXE ANSI
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO EXTERIOR - ANSI
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO EXTERIOR - TRADICIONAL
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
AUTO JUNÇÃO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
AUTO JUNÇÃO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
ESTE COMANDO PRODUZ O RESULTADO 20 QUE É UTILIZADO NO COMANDO EXTERNO 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILNHAS
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - CORRELATA
SELECT ID, ULT_NOME, SALARIO, ID_DEPTO 
FROM EMPREGADO E
WHERE SALARIO > (SELECT AVG(SALARIO) 
 FROM EMPREGADO I
 WHERE I.ID_DEPTO = E.ID_DEPTO)
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO - UNION
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
UNION 
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR'
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – UNION ALL
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
UNION ALL
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR'
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – INTERSECT
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
INTERSECT 
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR'
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – MINUS
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
MINUS 
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR'
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
VISÕES - CRIAÇÃO
CREATE VIEW EMP_RESUMO AS
SELECT ID, PRIM_NOME, ULT_NOME, CARGO 
FROM EMPREGADO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
VISÕES – MANIPULANDO DADOS
INSERT INTO EMP_RESUMO 
VALUES ( 10, ‘ALBERTO’,’RODRIGUES’, ‘ALMOXARIFE’)
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
VISÕES - ELIMINANDO
DROP VIEW EMP_DEP
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SEQUENCES
CREATE SEQUENCE ID_CLI_SEQ
	INCREMENT BY 3
	START WITH 10
	MAXVALUE 1000
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SEQUENCES - NEXTVAL
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SEQUENCES - CURRVAL
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SEQUENCES - ELIMANDO
DROP SEQUENCE ID_CLI_SEQ
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
Índices - Oracle
CREATE INDEX IND_VEND ON CLIENTE(VENDEDOR)
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
Índices - Oracle
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
TRANSAÇÕES
 
Uma transação é uma unidade de execução de programa que acessa e, possivelmente, atualiza vários itens de dados. Uma transação, geralmente, é o resultado da execução de um programa de usuário escrito em uma linguagem de manipulação de dados, e é delimitada da forma: 
begin transaction 
.........
end transacion. 
A transação consiste de todas as operações ali executadas, entre o começo e o final da transação. Durante a execução de uma transação o banco de dados pode passar por estados de inconsistência por vários motivos como:
Queda de energia,
Falha física,
Etc..
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
EXEMPLOS DE TRANSAÇÃO
BEGIN_TRANSACTION
UPDATE EMP
SET SAL = SAL * 1.1
WHERE CODEPT = 10
COMMIT
BEGIN_TRANSACTION
UPDATE CONTA
SET SALDO = SALDO - 200
WHERE NRCC = 1000
UPDATE CONTA 
SET SALDO = SALDO + 200
WHERE NRCC = 1002
COMMIT
BEGIN_TRANSACTION
UPDATE EMP
SET SAL = SAL * 1.1
WHERE CODEPT = 10
ROLLBACK
IMPLEMENTAÇÃO DE BANCO DE DADOS
PROPRIEDADES DAS TRANSAÇÕES
- ATOMICIDADE
	- CONSISTÊNCIA
		 - ISOLAMENTO
			-DURABILIDADE		
A C I D
IMPLEMENTAÇÃO DE BANCO DE DADOS
ESTADOS DA TRANSAÇÃO
IMPLEMENTAÇÃO DE BANCO DE DADOS
Execução em seqüência
T1
T2
T1
T2
read(A);
A := A – 50;
write(A);
read(B);
B := B + 50;
write(B);
read(A);
temp := A * 0,1;
A := A – temp;
write(A);
read(B);
B := B + temp;
write(B);
read(A);
A := A – 50;
write(A);
read(B);
B := B + 50;
write(B);
read(A);
temp := A * 0,1;
A := A – temp;
write(A);
read(B);
B := B + temp;
write(B);
Escalas de execução em seqüência: observe que o estado do BD é sempre 
consistente.
IMPLEMENTAÇÃO DE BANCO DE DADOS
Execução concorrente ...
T1
T2
read(A);
A := A – 50;
write(A);
read(B);
B := B + temp;
write(B);
read(B);
B := B + 50;
write(B);
read(A);
temp := A * 0,1;
A := A – temp;
write(A);
Correta
T1
T2
read(A);
A := A – 50;
write(A);
read(B);
B := B + 50;
write(B);
read(A);
temp := A * 0,1;
A := A – temp;
write(A);
read(B);
B := B + temp;
write(B);
Incorreta
IMPLEMENTAÇÃO DE BANCO DE DADOS
Protocolos com Base em Bloqueio (Métodos Pessimistas)
Uma das formas de garantir que apenas escalonamentos serializáveis sejam produzidos é obrigar que o acesso aos itens de dados seja feito de maneira mutuamente exclusiva:
Enquanto uma transação acessa um item de dados, nenhuma outra transação pode modificá-lo.
Para implementar isso pode-se usar o método de bloqueio (lock).
IMPLEMENTAÇÃO DE BANCO DE DADOS
Esquema de Multiversão
Os esquemas de controle de concorrência por bloqueio ou por timestamp garantem a serialização atrasando a operação ou abortando a transação responsável por tal operação.
Essas dificuldades podem ser evitadas se o sistema providenciar cópias de cada item de dado.
Em um sistema de banco de dados multiversão, cada operação write(Q) cria uma nova versão de Q. Quando é emitida uma operação read(Q), o sistema seleciona uma das versões de Q para ser lida.
O esquema de controle de concorrência precisa garantir que a seleção da versão lida seja tal que assegure a serialização.
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
FASES DE EXECUÇÃO DE COMANDO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização de Consultas
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimizador de consultas
• Abordagens clássicas:
– Otimização por regras: Plano de execução é gerado ao se aplicar heurísticas tidas como eficientes (independente de estatísticas). Por exemplo, o otimizador poderia sempre escolher utilizar um índice que esteja disponível sobre uma coluna.
– Otimização por custo: Determina que plano de execução é o mais eficiente ao considerar diferentes “caminhos de acesso” usando as estatísticas sobre os objetos do esquema (tabelas e índices) acessados pelo comando SQL (mais eficiente e recomendado)
A otimização por custo demanda estatísticas
IMPLEMENTAÇÃO DE BANCO DE DADOS
Planos de Execução
O plano de execução é o roteiro que o SGBD segue para recuperar as linhas especificadas pela consulta. Uma série de detalhes sobre como a consulta deve ser processada é especificada no plano. Em especial, quais operações serão aplicadas para resolver a consulta e em que ordem elas serão processadas.
• O que é especificado no plano de execução:
– Ordem de acesso às tabelas
– Ordem de operações de seleção, projeção e junção
– Índices utilizados
– Tipos de junção
– Ordenações
– Tabelas intermediárias
• Existem dois tipos básicos de operação:
– Métodos de acesso (varreduras seqüenciais e indexadas)
– Outras operações (junções, uniões, ordenação, etc...)
IMPLEMENTAÇÃO DE BANCO DE DADOS
Varredura Seqüencial 
IMPLEMENTAÇÃO DE BANCO DE DADOS
Varredura Indexada 
IMPLEMENTAÇÃO DE BANCO DE DADOS
Junções
• Os algoritmos clássicos utilizados em junções são:
– Loops Aninhados (Nested Loops Join)
– Ordenação/Intercalação (Merge Join)
– Hashing (Hash Join)
IMPLEMENTAÇÃO DE BANCO DE DADOS
Nested Loops
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
ORACLE - REGRAS
Rank
Caminho de Acesso
1
SinglerowbyROWID
2
Single row by cluster join
3
Single row by hash cluster key with unique or primary key
4
Single row by unique or primary key
5
Clusterjoin
6
Hashclusterkey
7
Indexedclusterkey
8
Compositeindex
9
Single-columnindex
10
Bounded range search on indexed columns
11
Unbounded range search on indexed columns
12
Sort-mergejoin
13
MAX or MIN of indexed column
14
ORDER BY on indexed column
15
Fulltablescan
55
IMPLEMENTAÇÃO DE BANCO DE DADOS
ORACLE – COMPUTANDO ESTATÍSTICAS
56
IMPLEMENTAÇÃO DE BANCO DE DADOS
ORACLE – ACESSO AO PLANO DE EXECUÇÃO
57
IMPLEMENTAÇÃO DE BANCO DE DADOS
OBRIGADO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS