none
Dúvida na criação de índice - Chaves do índice RRS feed

  • Pergunta

  • Pessoal,

    estou com uma dúvida em relação á criação de índices e gostaria da ajuda de vocês:

    Vou criar esses dois índices:

    CREATE INDEX [IX_TbAccount_IdCredit] ON [BDEnter].[TbAccount] ([IdCredit])WITH (ONLINE = ON, PAD_INdex = ON, FILLFACTOR = 80)
    CREATE INDEX [IX_TbAccount_IdAccou]  ON [BDEnter].[TbAccount] ([IdAccount])WITH (ONLINE = ON, PAD_INdex = ON, FILLFACTOR = 80)


    Minha dúvida é, vale a pena criar esses dois índices diferentes ou criar apenas um com as duas colunas sendo chave do índice?

    CREATE INDEX [IX_TbAccount_INDICE] ON [BDEnter].[TbAccount] ([IdCredit],[IdAccount])WITH (ONLINE = ON, PAD_INdex = ON, FILLFACTOR = 80)

    Falo isso porque em questão de escrita no índice, teoricamente eu não teria que escrever duas vezes nela (é apenas um índice), mas e pra leitura? A posição (esquerda ou direita) da coluna pode interferir na performance?



    terça-feira, 29 de maio de 2012 18:32

Respostas

  • Boa Noite,

    É um vício comum achar que a criação de um índice composto é um plano bidimensional, mas infelizmente não é o caso (a menos que estejamos falando de índices dimensionais ou estruturas como grid files e hash particionado).

    Vejamos a seguinte lista com as colunas a e b com os seguintes valores:

    a - 2, b - 1
    a - 1, b - 3
    a - 2, b - 7
    a - 1, b - 9
    a - 1, b - 2
    a - 5, b - 8

    Supondo um índice sobre as colunas A e B, teríamos que primeiro ordenar os registros pela coluna A e quando houvesse empate, ordenar pela coluna B. Se fizermos isso teremos a seguinte lista:

    a - 1, b - 2
    a - 1, b - 3
    a - 1, b - 9
    a - 2, b - 1
    a - 2, b - 7
    a - 5, b - 8

    Aqui podemos ver que primeiro vem os registros com A = 1, A = 2 e depois A = 5 (afinal como A é a primeira coluna, ela tem que vir em ordem crescente). Quando A empata, podemos ver que B está ordenado (A = 1 & B = 2, A = 1 & B = 3, A = 1 & B = 9), afinal se B é a segunda coluna, ela só entra na ordem quando a ordem de A foi respeitada. Isso é bem claro, pois, podemos ver que a terceira linha tem o valor de B igual a 9 enquanto que a quarta linha tem o valor de B igual a 1. Embora 1 seja menor que nove, sabemos que o valor de A é priorizado e nesse caso para B = 9 temos que A = 1 e para B = 1 temos que A = 2 o que se encaixa na ordem A, B.

    Agora vamos pensar o seguinte, se você quer fazer uma consulta que informe os valores das colunas A e B, esse índice é perfeito, pois, ele mantém uma lista com essas duas colunas já ordenadas e terá facilidade para localizar os registros. Se você fizer uma consulta que informe o valor da coluna A, o índice ainda pode ser utilizado, pois, embora tenha as colunas A e B, como a coluna A ordena o índice primeiro, ela ainda mantém uma ordem pela coluna A e seria "útil" para efetuar a pesquisa.

    Entretanto, se você fizer uma pesquisa apenas informando a coluna B, podemos ver que a lista é frágil, pois, a coluna B se considerada individualmente não impõe qualquer ordem à lista. Veja que uma consulta do tipo WHERE B <=2 seria frágil, pois, mesmo depois do 3 ainda temos o valor 1 para pesquisar o que nos faria ler o índice inteiro para posterior efetuar a pesquisa e se for pra ler o índice inteiro para depois ir na tabela, é mais fácil ignorar o índice e ler a tabela.

    Existem muitos outros IFs para esse caso e aí a gente poderia discutir uma noite inteira, mas por hora esse é o raciocínio básico. Índice composto costuma ser bom se todas as colunas forem consultadas (ou as primeiras). Se esse não é o caso, índices individuais costumam ser uma opção mais em conta (ainda que possam vir a onerar as operações de escrita).

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quinta-feira, 5 de julho de 2012 02:26

Todas as Respostas

  • Leonardo,

    Vale a pena criar os 2 indices apartados caso voce fará buscas na mesma pelos 2 campos separadamente:

    Se voce criar o terceiro indice, contando as 2 colunas, e realizar uma pesquisa apenas pelo Id_Account ele utilizará este indice, porem, sua efetividade será menor que se utilizasse o segundo indice, o que resultará em um custo da query maior.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    domingo, 24 de junho de 2012 16:27
    Moderador
  • UM índice composto não substitui a criação de um ou mais índices simples, inclusive a criação de um índice composto pode (e em alguns casos, DEVE) ser substituído por um ou mais índices simples.

    Um índice composto como no seu caso, seria muito efetivo em consultas que utilizassem os dois campos (IdCredit e IdAccount) na mesma consulta como chave de JOIN ou cláusula WHERE.

    Se quiser um pouco mais detalhe, http://datareader.wordpress.com/2012/03/26/indice-composto-parte-i-6/ 

    Abs,

    terça-feira, 26 de junho de 2012 13:00
  • Boa Noite,

    É um vício comum achar que a criação de um índice composto é um plano bidimensional, mas infelizmente não é o caso (a menos que estejamos falando de índices dimensionais ou estruturas como grid files e hash particionado).

    Vejamos a seguinte lista com as colunas a e b com os seguintes valores:

    a - 2, b - 1
    a - 1, b - 3
    a - 2, b - 7
    a - 1, b - 9
    a - 1, b - 2
    a - 5, b - 8

    Supondo um índice sobre as colunas A e B, teríamos que primeiro ordenar os registros pela coluna A e quando houvesse empate, ordenar pela coluna B. Se fizermos isso teremos a seguinte lista:

    a - 1, b - 2
    a - 1, b - 3
    a - 1, b - 9
    a - 2, b - 1
    a - 2, b - 7
    a - 5, b - 8

    Aqui podemos ver que primeiro vem os registros com A = 1, A = 2 e depois A = 5 (afinal como A é a primeira coluna, ela tem que vir em ordem crescente). Quando A empata, podemos ver que B está ordenado (A = 1 & B = 2, A = 1 & B = 3, A = 1 & B = 9), afinal se B é a segunda coluna, ela só entra na ordem quando a ordem de A foi respeitada. Isso é bem claro, pois, podemos ver que a terceira linha tem o valor de B igual a 9 enquanto que a quarta linha tem o valor de B igual a 1. Embora 1 seja menor que nove, sabemos que o valor de A é priorizado e nesse caso para B = 9 temos que A = 1 e para B = 1 temos que A = 2 o que se encaixa na ordem A, B.

    Agora vamos pensar o seguinte, se você quer fazer uma consulta que informe os valores das colunas A e B, esse índice é perfeito, pois, ele mantém uma lista com essas duas colunas já ordenadas e terá facilidade para localizar os registros. Se você fizer uma consulta que informe o valor da coluna A, o índice ainda pode ser utilizado, pois, embora tenha as colunas A e B, como a coluna A ordena o índice primeiro, ela ainda mantém uma ordem pela coluna A e seria "útil" para efetuar a pesquisa.

    Entretanto, se você fizer uma pesquisa apenas informando a coluna B, podemos ver que a lista é frágil, pois, a coluna B se considerada individualmente não impõe qualquer ordem à lista. Veja que uma consulta do tipo WHERE B <=2 seria frágil, pois, mesmo depois do 3 ainda temos o valor 1 para pesquisar o que nos faria ler o índice inteiro para posterior efetuar a pesquisa e se for pra ler o índice inteiro para depois ir na tabela, é mais fácil ignorar o índice e ler a tabela.

    Existem muitos outros IFs para esse caso e aí a gente poderia discutir uma noite inteira, mas por hora esse é o raciocínio básico. Índice composto costuma ser bom se todas as colunas forem consultadas (ou as primeiras). Se esse não é o caso, índices individuais costumam ser uma opção mais em conta (ainda que possam vir a onerar as operações de escrita).

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quinta-feira, 5 de julho de 2012 02:26
  • Muito bem Gustavo,

    entendi perfeitamente, vou realizar os testes e analisar os planos de execução.

    Muito obrigado pela resposta.

    quinta-feira, 19 de julho de 2012 16:46