DicasSQL
25 pág.

DicasSQL


DisciplinaBancos de Dados para Redes90 materiais673 seguidores
Pré-visualização4 páginas
.
 1 HASH JOIN .
 2 TABLE ACCESS FULL TESTE.TAB_LOC_PEQ
 3 TABLE ACCESS FULL TESTE.TAB_LOC
 
\ufffd
2.5 Star Join
Join realizado entre uma tabela fato e algumas dimensões (tabelas lookup com informações dos atributos da tabela fato)
Geralmente é usado quando o schema tem tabelas fato grandes e dimensões bem pequenas
Cada tabela lookup possui um join com a tabela fato (PK_Lookup = FK_Fato_Lookup), mas as tabelas lookups não têm join entre si.
Os joins são realizados nas seguintes etapas: 
produto cartesiano entre as dimensões 
join com a tabela fato usando um índice concatenado por nested loops
Exemplo de um star join
 
 
Não confunfir o start join com o star transformation
 \ufffd
2.6 Star transformation
Transformação de uma consulta realizada entre uma tabela fato e algumas dimensões 
É realizado apenas se houver redução no custo
Não há necessidade de criar índices concatenados para atender a combinação de tabelas referenciadas, pois combina índices bitmap da tabela fato
Pré-requisitos
- Deve existir um índice bitmap para cada FK
- STAR_TRANSFORMATION_ENABLED = TRUE
- CBO deve estar sendo usado
A consulta é realizada da em 2 fases:
O Oracle transforma a consulta e utiliza os índices bitmap das colunas FKs da tabela fato para obter o result set (conjunto exato dos registros necessários para avaliar a consulta). Em seguida os registros da tabela fato são obtidos.
O join entre a tabela fato e as dimensões é realizado. Geralmente o hash join é o mais eficiente.
Exemplo:
Comando original
Tabela fato: sales
Dimensões : store, time e product
SELECT store.sales_district,
 time.fiscal_period,
 SUM(sales.dollar_sales) revenue,
 SUM(dollar_sales) - SUM(dollar_cost) income
FROM sales, store, time, product
WHERE sales.store_key = store.store_key 
 AND sales.time_key = time.time_key 
 AND sales.product_key = product.product_key 
 AND time.fiscal_period IN ('3Q95', '4Q95', '1Q96') 
 AND product.department = 'Grocery' 
 AND store.sales_district IN ('San Francisco', 'Los Angeles')
GROUP BY store.sales_district, time.fiscal_period;
Transformação 
1ª parte: obtenção do result set
SELECT ... 
FROM sales
WHERE store_key IN (SELECT store_key FROM store 
 WHERE sales_district IN ('WEST','SOUTHWEST')) 
 AND time_key IN (SELECT time_key FROM time 
 WHERE quarter IN ('3Q96', '4Q96', '1Q97')) 
 AND product_key IN (SELECT product_key FROM product 
 WHERE department = 'GROCERY');
2ª parte: join entre o result set e as dimensões
\ufffd
Plano de execução
SELECT STATEMENT
 HASH JOIN
 HASH JOIN
 HASH JOIN
 TABLE ACCESS SALES BY INDEX ROWID
 BITMAP CONVERSION TO ROWIDS
 BITMAP AND
 BITMAP MERGE
 BITMAP KEY ITERATION
 TABLE ACCESS STORE FULL
 BITMAP INDEX SALES_STORE_KEY RANGE SCAN
 BITMAP MERGE
 BITMAP KEY ITERATION
 TABLE ACCESS TIME FULL
 BITMAP INDEX SALES_TIME_KEY RANGE SCAN
 BITMAP MERGE
 BITMAP KEY ITERATION
 TABLE ACCESS PRODUCTS FULL
 BITMAP INDEX SALES_PRODUCT_KEY RANGE SCAN
 TABLE ACCESS TIME FULL
 TABLE ACCESS PRODUCTS FULL
 TABLE ACCESS STORE FULL
\ufffd
3. Hints
Hint é um comentário padronizado que se inclui no meio do comando para modificar o plano de execução. Geralmente ele é utilizado nos casos em que o Oracle não consegue definir uma boa estratégia.
Sintaxe: 
 select /*+ Hint... */ 
 colunas ...
 from tabelas ...
Abaixo segue a listagem dos hints para consultas:
3.1 Tipo de Otimizador
	
All_rows \u2013 força o cost-based optimizer(CBO) a otimizar o comando para retornar todos registros com o menor tempo possível (maior throughput)
First_rows \u2013 força o CBO a otimizar o comando para obter o melhor tempo de resposta
Rule \u2013 força a utilização do rule-based optimizer(RBO) 
Choose \u2013 deixa para o Oracle optar por CBO ou RBO. Se pelo menos uma das tabelas possuir estatísticas coletadas então CBO é optado, senão é considerado o RBO.
3.2 Subquery / View
	
Push_Subq \u2013 executa as subqueries antes. Normalmente são executadas por último.
	Exemplo
 select /*+ push_subq */ 
 count(*)
 from tabela_teste
 where codigo in (select codigo 
 from tabela_teste2
 where tipo = \u2018T\u2019);
 
Merge(v) \u2013 força o merge da view com a query principal
	Exemplo
 select /*+ merge(v) */
 count(*)
 from view_teste v,
 tabela_teste t
 where v.codigo = t.codigo;
\ufffd
No_Merge(v) \u2013 previne o merge da view com a query principal
	Exemplo
 select /*+ no_merge(v) */ 
 count(*)
 from view_teste v,
 tabela_teste t
 where v.codigo = t.codigo;
 
Merge_Aj \u2013 transforma o NOT IN subquery em sort-merge anti-join
 
Hash_Aj \u2013 transforma o NOT IN subquery em hash anti-join
Merge_Sj - transforma o EXISTS em sort-merge semi-join mantendo a semântica. O default do EXISTS é usar o algoritmo nested loops. 
 Obs: nos casos em que a tabela que está sendo verificada (tabela dentro do EXISTS) não possuir índice nas colunas do join, haverá um ganho de performance se o sort-merge semi-join for usado.
Hash_Sj \u2013 transforma o EXISTS em hash semi-join mantendo a semântica. O default do EXISTS é usar o algoritmo nested loops.
 Obs: nos casos em que a tabela que está sendo verificada (tabela dentro do EXISTS) não possuir índice nas colunas do join, haverá um ganho de performance se o hash semi-join for usado.
Push_Join_Pred(v) \u2013 força a view usar o join predicate da query principal
3.3 Acesso
Full(tab) \u2013 força o full table scan para tabela \u2018tab\u2019
Cache(tab) \u2013 força que blocos retornados da consulta sejam colocados na lista LRU do buffer cache quando o full table scan for executado, se o tamanho da tabela for menor ou igual ao valor do parâmetro CACHE_SIZE_THRESHOLD.
Nocache(tab) \u2013 força que os blocos retornados da consulta não sejam colocados na cache, mesmo que a tabela tenha o parâmetro cache habilitado.
Rowid(tab) \u2013 força o acesso a tabela \u2018tab\u2019 pelo rowid
Index(tab index) \u2013 força o acesso a tabela \u2018tab\u2019 pelo índice \u2018index\u2019
Index_Asc(tab index) \u2013 induz o Oracle a acessar o índice \u2018index\u2019 da tabela \u2018tab\u2019 com um range scan
Index_Desc(tab index) \u2013 induz o Oracle a acessar o índice \u2018index\u2019 da tabela \u2018tab\u2019 na ordem decrescente
Ndex_Ffs(tab index) \u2013 opta pelo fast full scan ao invés do full table scan
Index_Combine(tab i1.. i5) \u2013 realiza a combinação booleana de índices bitmap com melhor custo.
Index_join(tab i1.. i5) \u2013 induz a utilização index join
And_Equal(tab i1.. i5) \u2013 realiza o merge de 2 a 5 índices com apenas 1 coluna.
Use_Concat \u2013 transforma a combinação de ORs/IN na cláusula WHERE em uma query composta com operador de conjunto UNION ALL. 
No_Expand \u2013 não permite a concatenação 
Driving_Site(tab) \u2013 a query é executada no site que a tabela \u2018tab\u2019 se encontra
	
3.4 Join
Use_Nl(tab) \u2013 induz o Oracle a optar a tabela \u2018tab\u2019 como sendo a inner table.
Use_Merge(tab) \u2013 induz o Oracle a realizar o join da tabela \u2018tab\u2019 com o método sort-merge.
Use_Hash(tab) \u2013 induz o Oracle a realizar o join da tabela \u2018tab\u2019 com o método hash join.
Star \u2013 induz o Oracle a realizar o start query
Star_Transformation \u2013 induz o Oracle a optar pelo melhor plano com Star Transformation. A transformação só é realizada se for benéfico.
Ordered \u2013 induz o Oracle a executar o join na ordem em que as tabelas se encontram na cláusula FROM (da esquerda para direita e