Entendendo a utilização de Indices – Parte III

Entendendo a utilização de Indices

Fala Galera….!!!

Demorou mas enfim.. aqui está a terceira parte do post.. hehehe.. coisas da vida né.. correria faz parte.. no ultimo post falamos sobre TIPOS de JOIN e neste post iremos falar de HINTS.. é HINTS.. existem varios tipos de HINTS para melhorar ou ate mesmo prejudicar uma consulta.. dependendo de como cada hint será utilizado… Como tudo tem seus prós e contras..! Mas vamos a uma melhor explicação de HINT´s e suas utilizações.. espero que aproveitem e que possam utilizar HINT´s corretos em suas instruções SQL.. Nós DBA´s agradecemos.. e muitoooooo!!!

1. 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 de cima para baixo).

Leading(tab) – induz o Oracle iniciar o join com a tabela ‘tab’.

3.5 Parallel Query (PQ)

Parallel (tab,degree[,inst]) – induz o Oracle a paralelizar a consulta a tabela ‘tab’ pelo grau de paralelismo ‘degree’ com ‘inst’ instâncias.

Parallel_Index(table,[index,[degree[,inst]]]) – induz o oracle a paralelizar o acesso de índice particionado.

Pq_Distribute(tab,out,in)- indica como distribuir os registros da tabela ‘tab’ em uma PQ entre produtores e consumidores. Os valors para ‘out’ (outer table) e ‘in’ (inner table) podem ser: hash, none, broadcast ou partition.

Noparallel(tab) – evita a paralelização do acesso a tabela ‘tab’

Noparallel_Index(tab) – evita o parallel index scan

3.7 Variados

Rewrite(v1[,v2]) – permite que uma query compatível com a consulta de uma materialized view (v1,v2,…) seja reescrita de forma que aproveite os dados pré processados da materialized view.

Obs: Isto somente funciona caso o seu banco seja superior à 8.1

Norewrite – não permite que a query seja reescrita

Bom pessoal.. este post é realmente curtinho.. Espero que tenham aproveitado.. e caso queiram entender mais de HINT´s.. podem dar uma olhada neste link.. HINTS 11g.. Muita coisa pode ser melhorada em uma instrução Sql com a inclusão de apenas um HINT.. e algo que eu digo sempre para os analistas.. “P… meu.. q m… q vc fez desta vez?” …. Brincadeira.. Brincadeira heim.. mas agora é serio.. Algo que eu sempre pensava quando programava.. é.. programava.. isto mesmo.. ja fui programador.. em DELPHI+ORACLE.. e quando eu desenvolvia uma instrução Sql, eu sempre procurava ver se a maneira que eu havia escrito a mesma era a mais correta… procurando sempre melhorar e obter a informação que eu queria o mais rapido possível e sem prejudicar o banco…!!!

“Melhor uma query gigante bem escrita no banco do que duas pequenas derrubando o meu banco”

Abraços Galera.. até o proximo post..!!