A ordem das colunas num índice composto faz diferença?

Em um artigo anterior já mostramos a importância do índice composto para melhorar o desempenho de uma instrução SQL, agora vamos estudar um pouco como a ordem das colunas no índice composto pode impactar o desempenho de uma instrução SQL.

A ordem das colunas no índice composto é importante, no entanto para definir qual a melhor ordem a ser adotada precisamos entender como a aplicação utilizará esse índice nas suas instruções SQL mais críticas, entender como os dados serão acessados e a partir daí determinar qual a melhor ordem para as colunas.

Quando o filtro da cláusula WHERE contem somente uma das colunas de um índice composto, a ordem dessa coluna no índice vai afetar o desempenho da instrução SQL. A partir da versão 9i a Oracle introduziu a operação INDEX SKIP SCAN que permitiu que um índice composto seja utilizado mesmo que a coluna presente no filtro da cláusula WHERE não seja a primeira no índice composto.

Descrição da simulação

Para facilitar o entendimento desses conceitos vamos fazer algumas simulações utilizando o “SAMPLE SCHEMA SH”, para isso vamos criar dois índices compostos com duas colunas da tabela “SALES” em ordem alternada e tornar invisível os dois índices unitários das colunas que foram utilizadas para criar os índices compostos. Nas duas primeiras simulações vamos utilizar as duas colunas no filtro da cláusula WHERE e para cada uma das simulações vamos forçar a utilização de um dos índices compostos deixando o outro índice composto invisível.
Nas duas ultimas simulações vamos utilizar somente uma coluna no filtro da cláusula WHERE e para cada uma das simulações vamos forçar novamente a utilização alternada dos índices compostos utilizando o mesmo procedimento.

Caso tenha alguma dúvida na leitura do plano de execução gerado pelo pacote DBMS_XPLAN consulte a série de artigos que vai ajuda-lo a interpretar essas informações:

1. Como verificar a ordem que as operações são realizadas
2. Como interpretar os valores estatísticos estimados
3. Como interpretar os valores estatísticos coletados durante a execução
4. Como interpretar as seções não estatísticas do plano de execução

Criando os índices compostos

O primeiro passo em nossa simulação é a criação de dois índices compostos utilizando duas colunas da tabela “SALES” com a ordem das colunas alternadas e tornar invisível os índices unitários dessas duas colunas.

Consulta com dois filtros na cláusula WHERE

Agora vamos executar duas vezes uma consulta com dois filtros na cláusula WHERE, um para cada uma das colunas presentes no índice composto, sendo que em cada uma das execuções vamos forçar a utilização de um índice composto diferente e vamos observar qual será o efeito desses índices no desempenho da consulta.

Verificando os planos de execução acima, observamos que o Otimizador utilizou os dois índices compostos com a ordem das colunas invertidas e a quantidade de Buffers lidos foi a mesma, podemos afirmar que o desempenho das duas consulta foi semelhante.

Consulta com um filtro na cláusula WHERE

Na sequência vamos executar duas vezes uma consulta com um filtro na cláusula WHERE alternando novamente a utilização dos índices compostos e verificar o desempenho dessas execuções.

Analisando os planos de execução acima notamos que na primeira execução a operação utilizada para acessar o índice foi “INDEX SKIP SCAN”, pois a coluna “CUST_ID” utilizada como filtro na cláusula WHERE é a segunda coluna no índice composto “SALES_PROD_CUST_BIX” e a operação visitou 84 Buffers para encontrar os registros que correspondem ao filtro especificado. Já na segunda execução a operação utilizada foi “INDEX RANGE SCAN” pois a coluna “CUST_ID” é a primeira coluna no índice composto “SALES_CUST_PROD_BIX” e a quantidade de Buffers visitados foram 3.

Comparando o desempenho das duas execuções podemos afirmar que a segunda execução que utilizou o índice “SALES_CUST_PROD_BIX” foi melhor que a primeira.

Conclusão

Quando utilizamos índices compostos temos que avaliar as instruções SQL da nossa aplicação e verificar se nossos índices vão proporcionar um bom desempenho para as instruções SQL mais críticas, precisamos observar se essas instruções possuem equivalência do numero de filtros na cláusula WHERE com o numero de colunas dos índices compostos, pois se não houver essa equivalência essas instruções podem ter um desempenho abaixo do esperado devido à ordem das colunas no índice.

Referências

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671539468597

https://docs.oracle.com/cd/E11882_01/server.112/e41573/data_acc.htm#PFGRF94776