Entendendo a utilização de índices – Parte II

Entendendo a utilização de índices

Salve Galera..

Vou continuar a falar sobre a utilização de índices, no primeiro post foi falado sobre metodos de acesso.. e neste post será falado sobre TIPOS DE JOIN… Espero q tenham gostado da primeira parte e que também gostem da segunda.. Desculpa a demora em colocar todo o conteúdo, é que o dia esta sendo corrido e somente monto este post a noite…

Tudo culpa do ASM no 11gR2… “benditos” pacotes adicionais.. Grid Infrastructure e De-Install Utility (muito bom).. Ainda vou colocar um Post falando sobre ASM (RAC e Single) no 11gR2 com Grid Infrastructure e o pacote De-Install Utility.. Mas vamos voltar ao assunto do Post…!

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;

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;

Obs: a etapa 2 não precisou realizar o sort porque o range scan já retornou os dados ordenados.

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;

2.4 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:
1. produto cartesiano entre as dimensões
2. 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.5 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:

1. 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.
2. 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

Bom pessoal.. por enquanto é só.. logo venho com mais uma parte do material sobre utilização de indices.. espero que tenham gostado…!!!!
Qualquer coisa é só postar ai…!!

Abraços…!!!!