DicasSQL
25 pág.

DicasSQL

Disciplina:Bancos de Dados para Redes81 materiais667 seguidores
Pré-visualização4 páginas
.

 1 HASH JOIN .

 2 TABLE ACCESS FULL TESTE.TAB_LOC_PEQ

 3 TABLE ACCESS FULL TESTE.TAB_LOC

�

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

 �

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

�

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

�

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 – 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 – força o CBO a otimizar o comando para obter o melhor tempo de resposta

Rule – força a utilização do rule-based optimizer(RBO)

Choose – 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 – 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 = ‘T’);

Merge(v) – 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;

�

No_Merge(v) – 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 – transforma o NOT IN subquery em sort-merge anti-join

Hash_Aj – 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 – 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) – força a view usar o join predicate da query principal

3.3 Acesso

Full(tab) – força o full table scan para tabela ‘tab’

Cache(tab) – 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) – 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) – força o acesso a tabela ‘tab’ pelo rowid

Index(tab index) – força o acesso a tabela ‘tab’ pelo índice ‘index’

Index_Asc(tab index) – induz o Oracle a acessar o índice ‘index’ da tabela ‘tab’ com um range scan

Index_Desc(tab index) – induz o Oracle a acessar o índice ‘index’ da tabela ‘tab’ na ordem decrescente

Ndex_Ffs(tab index) – opta pelo fast full scan ao invés do full table scan

Index_Combine(tab i1.. i5) – realiza a combinação booleana de índices bitmap com melhor custo.

Index_join(tab i1.. i5) – induz a utilização index join

And_Equal(tab i1.. i5) – realiza o merge de 2 a 5 índices com apenas 1 coluna.

Use_Concat – transforma a combinação de ORs/IN na cláusula WHERE em uma query composta com operador de conjunto UNION ALL.

No_Expand – não permite a concatenação

Driving_Site(tab) – a query é executada no site que a tabela ‘tab’ se encontra

	

3.4 Join

Use_Nl(tab) – induz o Oracle a optar a tabela ‘tab’ como sendo a inner table.

Use_Merge(tab) – induz o Oracle a realizar o join da tabela ‘tab’ com o método sort-merge.

Use_Hash(tab) – induz o Oracle a realizar o join da tabela ‘tab’ com o método hash join.

Star – induz o Oracle a realizar o start query

Star_Transformation – induz o Oracle a optar pelo melhor plano com Star Transformation. A transformação só é realizada se for benéfico.

Ordered – induz o Oracle a executar o join na ordem em que as tabelas se encontram na cláusula FROM (da esquerda para direita e