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:

índice – é um objeto do banco de dados utilizado para acessar o dado existente numa tabela mais rapidamente
ROWID – é o endereço físico do registro , informando em qual arquivo e setor o dado exatamente está.
hint – são orientações de uso de índice ou algoritmo feitas para a engine do Oracle executar.
analyze – mé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.
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:

folhear uma por uma as quase 250 páginas até achar o que procura, ou
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:

SQL> create table obj (

2 id number primary key,

3 nome varchar2(30) );

Table created.

SQL> insert into obj

2 select rownum, object_name

3 from all_objects;

63861 rows created.

SQL> commit;

Commit complete.

SQL> create table obj_sem_analyze as select * from obj;

Table created.

SQL> alter table obj_sem_analyze add primary key (id);

Table altered.

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:

SQL> select * from obj where ID=1234;

ID NOME

-- ---------

1234 V$_LOCK

SQL> select * from obj where ROWID=’AAAQCeAAEAAAAQMAAf’;

ID NOME

-- ---------

1234 V$_LOCK

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

SQL> select id,rowid from obj where nome=’V$_LOCK’;

ID ROWID

-- ----------

1234 AAAQCeAAEAAAAQMAAf

SQL>

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:

select *

from dict_columns

where table_name=’USER_TABLES’

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:

SQL> analyze table obj compute statistics;

Table analyzed.

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

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables;

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

---------- -------- ------ ------------

OBJ 63861 370 14

OBJ_SEM_ANALYZE

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! =)

Fonte http://www.boaglio.com/index.php/2008/02/07/indices-no-oracle/ (Fernando Boaglio, para a comunidade)

Referências:
Turbocharge SQL with advanced Oracle indexing

Páginas

quarta-feira, 19 de janeiro de 2011

Índices no Oracle

SQL> create table obj (

2 id number primary key,

3 nome varchar2(30) );

Table created.

SQL> insert into obj

2 select rownum, object_name

3 from all_objects;

63861 rows created.

SQL> commit;

Commit complete.

SQL> create table obj_sem_analyze as select * from obj;

Table created.

SQL> alter table obj_sem_analyze add primary key (id);

Table altered.

SQL> select * from obj where ID=1234;

ID NOME

-- ---------

1234 V$_LOCK

SQL> select * from obj where ROWID=’AAAQCeAAEAAAAQMAAf’;

ID NOME

-- ---------

1234 V$_LOCK

SQL> select id,rowid from obj where nome=’V$_LOCK’;

ID ROWID

-- ----------

1234 AAAQCeAAEAAAAQMAAf

SQL>

select *

from dict_columns

where table_name=’USER_TABLES’

SQL> analyze table obj compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables;

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

---------- -------- ------ ------------

OBJ 63861 370 14

OBJ_SEM_ANALYZE