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. SQL>
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. SQL>
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 SQL>
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