DicasSQL
25 pág.

DicasSQL


DisciplinaBancos de Dados para Redes90 materiais672 seguidores
Pré-visualização4 páginas
Dicas para otimização de consultas
\ufffd
Índice
3Introdução	\ufffd
41. Método de Acesso	\ufffd
41.1 Full Table Scan	\ufffd
41.2 Table Access by Rowid	\ufffd
51.3 Index Scan	\ufffd
82. Tipos de Join	\ufffd
82.1 Nested Loops	\ufffd
92.2 Sort-Merge	\ufffd
102.3 Hash Join	\ufffd
112.5 Star Join	\ufffd
122.6 Star transformation	\ufffd
143. Hints	\ufffd
143.1 Tipo de Otimizador	\ufffd
143.2 Subquery / View	\ufffd
153.3 Acesso	\ufffd
163.4 Join	\ufffd
173.5 Parallel Query (PQ)	\ufffd
173.7 Variados	\ufffd
184. Dicas	\ufffd
194.1 Evitar função em coluna indexada	\ufffd
204.2 Join com colunas do mesmo tipo	\ufffd
224.3 Coletar as estatísticas com regularidade	\ufffd
244.4 Sql Trace	\ufffd
\ufffd
Introdução
Este documento tem o objetivo de auxiliar os desenvolvedores e dbas na otimização de consultas.
Inicialmente revisaremos alguns pontos importantes: os métodos de acesso, os algoritmos usados para realização do join e os hints usados para modificar o plano de execução das consultas.
Em seguida veremos algumas dicas para se aproveitar o máximo da eficiência deste banco de dados.
 \ufffd
1. Método de Acesso
Representa o tipo de acesso para obter os dados de uma determinada tabela. O Oracle possui os seguintes métodos: 
1.1 Full Table Scan
Realiza a leitura direta da tabela para determinar os registros que satisfazem a cláusula Where
Permite o uso do multiblock I/O (leitura de vários blocos seqüenciais com um simples I/O)
Permite a paralelizar ção
Nem sempre representa um método de acesso ruim
Representação no explain plan:
OPERATION OPTIONS OBJECT_NAME 
------------------------------ -------------------- ---------------
 0 SELECT STATEMENT .
 1 SORT AGGREGATE .
 2 TABLE ACCESS FULL TESTE.TAB_LOC
1.2 Table Access by Rowid
Realiza o acesso a tabela pelo Rowid
É a maneira mais rápida de acessar um registro
Representação no explain plan:
OPERATION OPTIONS OBJECT_NAME 
------------------------------ -------------------- -------------------
 0 SELECT STATEMENT .
 1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA
\ufffd
1.3 Index Scan
Realiza o acesso aos dados através de um índice
A maioria é do tipo b*-tree (padrão)
Estrutura interna da B*-tree
Possui os seguintes tipos:
Unique Scan: é usado quando a tabela possui uma chave única ou primária. Retorna apenas 1 rowid.	
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- -------------------
 0 SELECT STATEMENT .
 1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 2 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK
Range Scan: é usado para obter um determinado intervalo de dados. Retorna 0 ou mais rowids.
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- --------------------
 0 SELECT STATEMENT .
 1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA
Full Scan: é usado quando a query usa pelo menos uma coluna do índice. Também é usado quando a consulta não possui predicado e se deseja evitar a operação de sort.
Fast Full Scan: é um Full Table Scan alternativo usado quando o índice possui todas colunas necessárias para a query. Para ler todos dados do índice, é mais rápido que o Index Scan por possibilitar o Multiblock I/O e a paralelização. 
 Obs: não pode ser usado para evitar a operação de sort (os dados não são retornados de forma ordenada). 
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- -------------------
 0 SELECT STATEMENT .
 1 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA
Index Join: é um hash join com alguns índices da tabela. Juntos obtêm todas colunas referenciadas na query.
OPERATION OPTIONS OBJECT_NAME 
------------------------- ----------------- -----------------------
 0 SELECT STATEMENT .
 1 VIEW TESTE.index$_join$_001
 2 HASH JOIN .
 3 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA
 4 INDEX FAST FULL SCAN TESTE.TAB_LOC_PK
Bitmap: utiliza um bitmap como chave para cada valor distinto da coluna e uma função de mapeamento que converte cada bit para um rowid. Se a coluna indexada tiver baixa cardinalidade, são rápidos e ocupam pouco espaço.
 Obs: uma coluna é considerada de baixa cardinalidade se o número de valores distintos for menor ou igual a 1% do total de registros.
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- --------------------
 0 SELECT STATEMENT .
 1 TABLE ACCESS BY INDEX ROWID TESTE.VENDAS
 2 BITMAP CONVERSION TO ROWIDS .
 3 BITMAP INDEX SINGLE VALUE TESTE.VENDAS_TIPO_BM
\ufffd
Exemplo: tabela Customer com 6 registros
 
 
 
 Índice Bitmap sobre a coluna Region 
 
Consulta de clientes casados e da região central e oeste.
			SELECT COUNT(*) FROM customer
WHERE STATUS = 'married' 
 AND REGION IN ('central','west');
		
	 
\ufffd
2. Tipos de Join
O join é uma operação que permite combinar o resultado de duas ou mais tabelas baseando se nos valores das colunas em comum. O Oracle utiliza os seguintes algoritmos 
2.1 Nested Loops
O otimizador escolhe uma tabela para ser a outer table (driving table) e outra para ser a inner table
Para cada registro da outer table, o Oracle acha todos registros da inner table que satisfazem o join condition
O Oracle combina os dados de cada par de registros que satisfazem o join condition e então retorna os registros resultantes
select *
 from tab_loc l,
 tab_loc_peq p
 where l.codigo = p.codigo;
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- ------------------
 0 SELECT STATEMENT .
 1 NESTED LOOPS .
 2 TABLE ACCESS FULL TESTE.TAB_LOC_PEQ
 3 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 4 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK
 
2.2 Sort-Merge
Os registros de cada tabela são ordenados pelas colunas do join condition
É feito um merge das duas ordenações e os registros que satisfizerem o join condition são retornados
select /*+ use_merge(l p) */ *
 from tab_loc l,
 tab_loc_peq p
 where l.codigo = p.codigo;
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- ----------------------
 0 SELECT STATEMENT .
 1 MERGE JOIN .
 2 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_PEQ
 3 INDEX FULL SCAN TESTE.TAB_LOC_PEQ_PK
 4 SORT JOIN .
 5 TABLE ACCESS FULL TESTE.TAB_LOC
 
 
Obs: a etapa 2 não precisou realizar o sort porque o range scan já retornou os dados ordenados.
\ufffd
2.3 Hash Join
O Oracle escolhe uma tabela menor para construir a tabela hash e o maior para verificar a tabela hash
Geralmente o Hash Join é mais rápido que o Sort-Merge
select /*+ use_hash(l) */ *
 from tab_loc l,
 tab_loc_peq p
 where l.codigo = p.codigo;
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- --------------------
 0 SELECT STATEMENT