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

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.

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. hint – são orientações de uso de índice ou algoritmo feitas para a engine do Oracle executar.
4. 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.
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.