Aula 10

Prévia do material em texto

IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 10 - Otimização
IMPLEMENTAÇÃO DE BANCO DE DADOS
TRANSAÇÕES
IMPLEMENTAÇÃO DE BANCO DE DADOS
Conteúdo Programático desta aula
• Aprender a criar Visões, Indices e 
Sequences
IMPLEMENTAÇÃO DE BANCO DE DADOS
TRANSAÇÕES
IMPLEMENTAÇÃO DE BANCO DE DADOS
FASES DE EXECUÇÃO DE COMANDO
IMPLEMENTAÇÃO DE BANCO DE DADOS
Analise Sintática
IMPLEMENTAÇÃO DE BANCO DE DADOS
Analisador Sintático
IMPLEMENTAÇÃO DE BANCO DE DADOS
Analise Semântica
IMPLEMENTAÇÃO DE BANCO DE DADOS
Fluxo de Processamento
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Geração de Plano Lógico
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização de Consultas
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização de Consultas
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização Heurística
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização Heurística
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização Heurísticas
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização Heurística
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização Heurística
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização Heurística
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização Heurística
IMPLEMENTAÇÃO DE BANCO DE DADOS
Avaliação de Custos
IMPLEMENTAÇÃO DE BANCO DE DADOS
Componentes de Custos
IMPLEMENTAÇÃO DE BANCO DE DADOS
Componentes de Custos
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
Otimização baseada em custo
IMPLEMENTAÇÃO DE BANCO DE DADOS
• Query Transformer: Transforma uma consulta SQL 
em uma expressão algébrica.
• Estimator
– Fator de seletividade
– Cardinalidade
– Custo (CPU + E/S + comunicação)
• Plan Generator: Gera um conjunto de planos 
equivalentes, porém com custos diferentes
– Ordem de junção
– Ordem dos operadores
– Algoritmos de junção (sort-merge, nested loops, hash
join )
Otimização baseada em custo
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização baseada em custo
• Principais caminhos de acesso
– TABLE ACCESS (bom para baixa seletividade)
• FULL SCAN
• BY ROW ID
– INDEX SCAN (bom para baixa seletividade)
• Unique
• Range
• Full
• Hash join
– JOIN
• Nested-Loop
• Sort-Merge
• Hash Join (não disponível em otimização baseada em regra)
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização baseada em regra
• Usa praticamente os mesmos caminhos de acesso 
da otimização baseada em custo
– Não contém HASH JOIN
• Utiliza heurísticas para gerar os planos de 
execução
– Regras de comutatividade e associatividade
• (A U C) JOIN (B U C)  (A JOIN B) U C
– Junção de seleção  Seleção de junção
– Seleção e projeção são antecipadas sobre a junção
– Busca por índices são preferidas
– etc.
IMPLEMENTAÇÃO DE BANCO DE DADOS
Varredura Seqüencial
IMPLEMENTAÇÃO DE BANCO DE DADOS
Varredura Indexada
IMPLEMENTAÇÃO DE BANCO DE DADOS
Varredura Indexada
IMPLEMENTAÇÃO DE BANCO DE DADOS
Varredura Indexada
IMPLEMENTAÇÃO DE BANCO DE DADOS
Ordenação (Sorting)
• Operação básica para diversas outras operações 
físicas
• Resolve a cláusula ORDER BY
• Se o conjunto a ser ordenado não cabe em memória, 
esta operação pode ser muito cara
• Pode ser utilizada em DISTINCT, UNION, 
INTERSECTION, JOIN…
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização baseada em custo
• Exemplos
SELECT * 
FROM EMP
WHERE Name = ‘Jackson’
TABLE ACCESS FULL SCAN
SELECT * 
FROM EMP
WHERE Empno = 7369
TABLE ACCESS BY INDEX ROW ID
INDEX UNIQUE SCAN (PK_EMP)
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização baseada em custo
• Exemplos SELECT *
FROM PROJECT
WHERE PNAME LIKE 'Contas%'
TABLE ACCESS BY INDEX ROW ID
INDEX RANGE SCAN (SYS_C01)
SELECT *
FROM PROJECT
WHERE PNAME LIKE ‘%Contas'
TABLE ACCESS FULL SCAN
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
Nested Loops
IMPLEMENTAÇÃO DE BANCO DE DADOS
Nested Loops
IMPLEMENTAÇÃO DE BANCO DE DADOS
Nested-Loops
SELECT * 
FROM EMP, DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
Merge Join
SELECT * 
FROM EMP, DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
IMPLEMENTAÇÃO DE BANCO DE DADOS
Hash Join
SELECT * 
FROM EMP, DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização baseada em custo
• Exemplos
SELECT W.*
FROM EMPLOYEE E, WORKS_ON W
WHERE E.SSN = W.ESSN AND E.SALARY > 800
NESTED LOOPS
TABLE ACCESS FULL (WORKS_ON)
TABLE ACESS BY INDEX ROWID
INDEX UNIQUE SCAN (SYS_C01)
IMPLEMENTAÇÃO DE BANCO DE DADOS
Otimização baseada em custo
• Exemplos
SELECT W.*
FROM WORKS_ON W, EMPLOYEE E
WHERE E.SSN = W.ESSN AND E.SALARY > 800
NESTED LOOPS
TABLE ACCESS FULL SCAN (EMPLOYEE)
INDEX RANGE SCAN (SYS_C012)
/* – Chave primária de WORKS_ON
IMPLEMENTAÇÃO DE BANCO DE DADOS
ORACLE - REGRAS
Rank Caminho de Acesso
1 Single row by ROWID
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 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite index
9 Single-column index
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort-merge join
13 MAX or MIN of indexed column
14 ORDER BY on indexed column
15 Full table scan
55
IMPLEMENTAÇÃO DE BANCO DE DADOS
ORACLE – COMPUTANDO ESTATÍSTICAS
56
IMPLEMENTAÇÃO DE BANCO DE DADOS
ORACLE – ACESSOAO PLANO DE EXECUÇÃO
57
IMPLEMENTAÇÃO DE BANCO DE DADOS
TRANSAÇÕES
IMPLEMENTAÇÃO DE BANCO DE DADOS
Próxima Aula
• Revisão para AV2