Performance de consultas em Tablespaces separados para Dados e Índices

 A verdade é que somente separar os dados e índices de uma tabela em tablespaces diferentes não é suficiente para otimizar o desempenho das consultas e atualizações. Ao ler as documentações oficiais da Oracle  OracleDatabase Performance Tuning Guide 10GR2 e OracleDatabase Performance Tuning Guide 11G Release 2, percebemos que em alguns trechos desses documentos, há informações incompletas que informam que separar os dados e índices em tablespaces diferentes pode otimizar o acesso aos dados. Um benefício pode existir se você configurar o tablespace de índices para não gerar log, porém essa configuração também pode não ter efeito se o BD foi configurado para forçar log (ALTER DATABASE FORCE LOGGING), configuração recomendada para ambientes com Data Guard.

Indo mais a fundo nas documentações citadas no parágrafo anterior, a Oracle informa que a leitura dos dados e índices de uma tabela é feita de forma sequencial, onde o índice é lido primeiro e a tabela é lida em seguida (quando necessário). Esta é uma informação que torna evidente o fato de que apenas separar dados e índices em tablespaces diferentes não pode melhorar a performance das consultas, pois o otimizador de queries do Oracle nunca irá ler as tabelas e índices ao mesmo tempo (ao contrário do que muitos pensam)!

Para melhorar a performance das consultas ao planejar o armazenamento de dados e índices, um dos melhores métodos é não apenas separar dados e índices em tablespaces diferentes, mas além disso, configurar o tablespace de índices para não gerar logs (avaliar muito bem o ambiente pois em alguns casos isso pode não ser uma boa opção) e criar o tablespace de índices com tamanho de blocos maior que o tamanho de blocos padrão do BD (8 kb). Em testes que eu demonstro nos meus treinamentos de SQL Tuning, blocos de 16 kb ou 32 kb para índices são ótimas opções para otimizar o acesso aos dados. Mais detalhes sobre o porquê do tamanho de bloco maior melhorar performance e como configurá-los, eu deixo para explicar nos meus treinamentos de SQL Tuning e Performance Tuning em Bancos de Dados.