índices no Oracle

  • 9

índices no Oracle

Category : Oracle

2 Flares Twitter 1 Facebook 1 Filament.io 2 Flares ×

Saber usar índices no Oracle é o que diferencia os desenvolvedores Oracle dos outros. A pedido de um colega eu vou escrever um pouco sobre esse assunto.

Para a minha breve demonstração , utilizarei a ferramenta Oracle SQL*Plus que acompanha a instalação do banco de dados. Se desejar, pode usar outra opção gratuita da Oracle de manipulação de banco de dados, que nada mais é que a engine do JDeveloper adequada somente ao desenvolvimento de SQL e PL/SQL: Oracle SQL Developer.

O banco de dados Oracle tem diversos mecanismos para melhorar a performance de suas queries (consultas).


Primeiro vamos entender o seguinte: procure somente otimizar alguma query se realmente houver necessidade, ficar tentando otimizar antes mesmo de precisar é algo inútil e uma completa perda de tempo.

Não se esqueçam do que Donald Knuth disse:“Nós devemos esquecer de pequenas melhorias (praticamente em 97% do tempo): otimização prematura é a raiz de todo o mal”.

Outra coisa: eu não sou DBA e posso ter alguns conceitos que se já não o são, podem se tornar ultrapassados em pouco tempo.

Além disso, para saber se algo é lento ou rápido, é preciso de alguma métrica. Não dá para saber se um carro é mais veloz que o outro só pela nossa “noção”, é preciso de um velocímetro. Em queries não é diferente, para isso usamos um mecanismo que detalha o que o banco de dados vai fazer e joga numa tabela para você consultar. Essa tabela se chama PLAN_TABLE, consulte esse artigo para entendê-la melhor.

Vou primeiro enumerar alguns conceitos importantes antes de detalhar os índices:

  1. índice – é um objeto do banco de dados utilizado para acessar o dado existente numa tabela mais rapidamente
  2. ROWID – é o endereço físico do registro , informando em qual arquivo e setor o dado exatamente está.
  3. hintsão orientações de uso de índice ou algoritmo feitas para a engine do Oracle executar.
  4. analyzemétodo interno do banco usado para armazenar as informações exatas sobre os seus dados dentro do dicionário de dados. Essas informações serão usadas pelo Oracle em todo novo SQL.
  5. FULL TABLE SCAN – processo que o RDBMS não utiliza nenhum índice para ler os dados de uma tabela.

Basicamente toda vez que se executa um SQL no banco de dados, o Oracle tem como opção duas maneiras de trabalho: por regras e por estatísticas .

Antigamente se usava somente via regra e quase todas queries tinham hints, pois o otimizador do Oracle não era muito bom. Hoje quem trabalha só na base de regra é louco, isso existe no banco de dados assim como aquelas malditas colunas LONG RAW que só dão dor de cabeça ao desenvolvedor: compatibilidade!

Para não ter dor de cabeça é só manter as estatísticas de sua base de dados atualizada, que o Oracle quase sempre acha a melhor maneira de montar uma query. Se você tiver um jeito melhor, pode mesmo assim usar hint.

O índice do banco de dados não é aquela coisa do outro mundo, ele segue a mesma filosofia de um índice de um livro: achar a informação que procura mais rapidamente.

Você pega o Effective Java e quer achar aquela dica de checked exceptions, você tem duas opções:

  1. folhear uma por uma as quase 250 páginas até achar o que procura, ou
  2. olhar o índice, descobrir o número da página (que é 174) e abrir o livro na página exata

Ok, parece uma loucura usar a primeira opção, mas isso somente funciona se você sabe o tamanho do livro. Se fosse um simples guia de consulta rápida de apenas 2 páginas, a segunda opção seria a mais irracional, certo?

Pois bem, no banco de dados é exatamente desse jeito que a coisa funciona.

Vamos ver um exemplo no nosso banco de dados:

No caso da tabela OBJ, temos um índice criado automaticamente para a chave primária, que nesse caso é a coluna ID. Internamente, o Oracle armazena numa estrutura otimizada de árvore B, com a informação do valor da chave (ID) e do ROWID.

Observem no exemplo abaixo obtemos o mesmo registro através da chave primária e do ROWID:

Portanto, na realidade o índice é simplesmente uma combinação de identificadores e endereços físicos, assim como listados abaixo:

Se comparamos com um índice de um livro, a coluna ID seria o título e a coluna ROWID seria o número da página. Simples assim.

Vamos voltar para o exemplo e ver algumas informações do dicionário de dados sobre essa tabela recém-criada dentro da view USER_TABLES.

Antes disso, verifiquem que tipos de informações a view contém, mas não precisa olhar no Google não, isso está no próprio banco:

Esse comando listará o nome e descrição de cada uma das 50 colunas.

Agora somente na tabela obj vamos colher as estatísticas com o comando analyze:

Agora listaremos as informações que o banco de dados contém:

Note no resultado acima que da tabela OBJ o Oracle sabe algumas coisas, entre elas que contém 63861 registros, já a OBJ_SEM_ANALYZE o Oracle não sabe nada.

O que acontece agora é que com a tabela OBJ o Oracle sabe se, dependendo da query utilizada, ele vai usar o índice da chave primária ou vai fazer um FULL TABLE SCAN.

Observem o seguinte exemplo: é exatamente a mesma query para as duas tabelas, porém a com as estatísticas roda com um custo muito menor (648 contra 2130) . Custo menor para query significa que ela executará em menos tempo.

Exemplo de query sem estatísticas no Oracle SQL Developer:

Exemplo de query com estatísticas no Oracle SQL Developer:

Espero que esse breve artigo tenha esclarecido o funcionamento de índices no Oracle. Portanto chame o seu DBA de canto e pergunte: quando foi que você rodou o analyze da última vez? Você poderá se surpreender com a resposta! =)

Fernando Boaglio, para a comunidade. =)

Referências:
Turbocharge SQL with advanced Oracle indexing


About Author

Fernando Boaglio

???

9 Comments

Roger Leite

8/fevereiro/2008 at 7:41 am

Parabéns pelo artigo Boaglio !
Objetivo, simples e com ótimas referências para complementar.

sucesso!

William Kurosawa

6/junho/2008 at 4:29 pm

Muito bom o artigo, simples e eficiente!
Salvou meu trabalho aqui na empresa, onde o módulo demorava 2h na query agora leva 2min..

Gostei do blog!

Abraços,
William

Sérgio Berlotto

22/julho/2008 at 4:27 pm

Legal o artigo Boaglio,
gostei opis é bastante exclarecedor !
Mas me resta ainda uma duvida quanto aos indices que ainda nao descobri:

Tendo-se a tabela: A com os campos : ID, Nome, Tipo
E se tenho a seguinte query:
select * from A where Nome = ‘Sérgio’ and Tipo = 3;

Quais indices serão mais eficazes nesta consulta:

create index idx1 on A (nome);
create index idx1 on A (tipo);

Ou

create index idx1 on A (nome, tipo);

?

Obrigado.
Vou linkar seu blog ….

Fernando Boaglio

22/julho/2008 at 8:52 pm

Olá Sérgio, a resposta é depende! Dependendo do tamanho da sua tabela, talvez o Oracle nem use índice. Mas se fosse somente pela sua query eu diria que é a melhor pedida é o índice composto (sua segudna opção).

brilhante explicação com um pouco de filosofia sobre as estatísticas das tabelas « Blog Sergio Bonfim – Oracle db's

12/setembro/2010 at 9:59 pm

[…] o Oracle criando estatísticas e utilizando o comando analyze. Veja o link abaixo: http://www.boaglio.com/index.php/2008/02/07/indices-no-oracle/ […]

Ana Jaira

4/abril/2011 at 5:11 pm

Parabens!!! mesmo depois de algum tempo, me foi muito util o teu artigo. Muito obrigada

Claudinha

2/junho/2011 at 10:55 pm

Muito obrigada!!!

Leonardo

23/novembro/2012 at 4:17 pm

ola, sou meio noob e gostaria de saber como listo os atributos que tem dentro de um index. se é que é possivel sem o uso de ferramenta grafica ?

Fernando Boaglio

23/novembro/2012 at 8:56 pm

@Leonardo tem várias maneiras, aqui tem um exemplo http://psoug.org/snippet/List-all-indexed-columns-for-a-given-table_515.htm

Leave a Reply

Quero saber mais sobre…

Inscreva-se para receber as novidades!

Arquivos

2 Flares Twitter 1 Facebook 1 Filament.io 2 Flares ×