none
Duvida Indice RRS feed

  • Pergunta

  • Qual a diferenca de usar ou nao INCLUDE em um indice. No exemplo abaixo, eu poderia adicionar todos os campos sem INCLUDE, mas o plano de execucao sugerio criar esse indice. So queria mesmo saber se tem direferença:

    CREATE NONCLUSTERED INDEX [IX_MAV_Devolucao_Composto_1] ON [dbo].[MAV_Devolucao] 
    (
    	[Bonificacao] ASC,
    	[Status] ASC,
    	[Docto] ASC,
    	[NF_DtEmissao] ASC
    )
    INCLUDE ( [Devolucao],
    [Origem],
    [CadCli],
    [Cliente],
    [NF_DtSaidaEntrada],
    [FretePagCli],
    [NaturezaOperacao],
    [CadTrans],
    [Trans],
    [Veiculo],
    [CompIE],
    [CompEspecie],
    [CompMarca],
    [CompQtde],
    [CompNumero],
    [CompPesoB],
    [CompPesoL],
    [CompObsCorpo]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO
    
    


    sexta-feira, 10 de fevereiro de 2012 16:55

Respostas

  • Davi,

    A utilização do Include ou não é uma questão interessante sobre os indices, mas de forma rapida, posso explicar da seguinte maneira:

    Voce possui em seu indice um campo chave, esse campo chave aparecera nos nós do seu indice, e a estrutura de dados arvore sera dividida por este campo.

    Quando voce possui apenas um indice com o campo chave, tudo que voce possui nos nós folhas é o numero das linhas aonde tenha tal valor.

    Fazendo com que seja necessario ir até a tabela para buscar outros dados alem do chave (No plano de execução voce vera isso como um look up).

    Um exemplo de uma busca apenas pelo campo chave é:

    SELECT Id

    FROM Tabela

    WHERE Id = 5

    Neste caso, sua unica pesquisa sera no indice, trazendo o valor em si. Se voce alterar a query para:

    SELECT Id, Nome

    FROM Tabela

    WHERE Id = 5

    Ao encontrar os ID 5 na estrutura do indice, voce devera fazer uma pesquisa em sua tabela, nos numeros da linha apontados no indice, para buscar a informação do nome, mantendo o seguinte fluxo:

    Inicia -> Indice -> Tabela -> Retorno.

    Agora, se colocarmos o INCLUDE (Nome) em nosso indice, e executarmos o ultimo select, o indice sera:

    Inicia -> Indice -> Retorno.

    Pois apesar da estrutura de arvore do indice estar divida pelo campo chave (Id), o campo nome sera carregado por toda estrutura, não sendo necessario ir buscar este dado na tabela uma vez que voce ja o possui no indice.

    Tome cuidado pois esses indices são chamados de FullQueryIndex, ou seja, preencher 100% uma query completa, e realmente para tal query talvez seja a melhor opção, porem, estamos falando de uma query Ad-Hoc, o que talvez não compense a criação do mesmo, ainda mais por que pode interferir em seu ambiente transacional.

    A sugestão do SQL Server é valida e muitas vezes voce a utilizara, porem tome cuidado, não estou falando para criar ou não o indice, e sim que talvez, se voce for executar essa query apenas uma vez, não acho que valha o esforço, porem, se for uma query recorrente de seu ambiente transacional, talvez seja interessante, mas novamente, é preciso analisar.

    Como dica: Crie o indice, execute a query, veja o plano, veja se houve efetivamente uma melhora ou não.


    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

    sexta-feira, 10 de fevereiro de 2012 17:07
    Moderador


  • DaviSaba,





    No final segue um script bem detalhado com todos os casos
    possiveis que consegui pensar.



    Porem, sim, quando voce colocar o campo no include, um
    filtro por este campo fara a utilização do indice, porem seu custo (Vide plano
    de execução) sera maior do que colocando-o como indice chave (Primeiro caso),
    quando o mesmo for a segunda coluna (No exemplo...), porem, não se for a
    primeira. Segue abaixo.





    OBS: Tudo depende de seus dados e da quantidade, é
    impossivel dizer 100% se ele vai ou não utilizar tal indice, a melhor opção
    continua sendo criar e testar.



    --CRIA TABELA



    CREATE TABLE TesteDavi



    (



          CodigoINT,



          ClienteINT



    )





    --CARREGA DADOS
    NA TABELA



    DECLARE @Cont INT = 0



    DECLARE @Sql VARCHAR(8000)



    SET @Sql = ''



    WHILE(@Cont < 1000000)



    BEGIN



          SET@Sql='INSERT INTO TesteDavi
    VALUES ('
    +CAST(@ContASVARCHAR(100))+','+CAST(@Cont+100 ASVARCHAR(100))+')'



          EXEC
    (@Sql)



          SET@Cont
    =@Cont+1



    END





    -----PRIMEIRO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    --FULLTABLE SCAN





    -----CRIACAO DE INDICE 1



    CREATE INDEX IX_01 ON TesteDavi(Codigo) WITH (SORT_IN_TEMPDB=ON)





    -----SEGUNDO SELECT



    SELECT Codigo



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN





    -----TERCEIRO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN + LookUp (Comentado acima)





    -----QUARTO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5



    --FULLTABLE SCAN





    -----CRIACAO DE INDICE 2



    CREATE INDEX IX_01 ON TesteDavi(Codigo, Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----QUINTO SELECT



    SELECT Codigo



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN





    -----SEXTO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN SEM LOOKUP





    -----SETIMO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--3,11736



    --INDEX SCAN





    -----CRIACAO DE
    INDICE 3



    CREATE INDEX IX_01 ON TesteDavi(Codigo) INCLUDE(Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----OITAVO SELECT



    SELECT Codigo



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN





    -----NONO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN SEM LOOKUP





    -----DECIMO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--3,11736



    --INDEX SCAN





    -----TESTE GERAIS



    CREATE INDEX IX_01 ON TesteDavi(Cliente) INCLUDE(Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----DECIMO
    PRIMEIRO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--0,0032831





    -----CRIACAO DE
    INDICE 4



    CREATE INDEX IX_01 ON TesteDavi(Codigo, Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--3,11736





    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 5--0,0032831





    -----CRIACAO DE INDICE 4



    CREATE INDEX IX_01 ON TesteDavi(Cliente, Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--0,0032831





    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 5--3,11736





    -----CRIACAO DE INDICE 4



    CREATE INDEX IX_01 ON TesteDavi(Cliente, Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 5--3,11736





    CREATE INDEX IX_01 ON TesteDavi(Cliente)INCLUDE(Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 5--3,11736



    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

    sexta-feira, 10 de fevereiro de 2012 18:20
    Moderador

Todas as Respostas

  • Olá DaviSaba,

    Acho que a Thread abaixo pode lhe ser útil.

    http://social.msdn.microsoft.com/Forums/pt-BR/520/thread/5819cef3-db5d-4544-a90c-6b616fe6027b/#f24d6b74-f1db-40c3-86d1-cf42e149d63a


    David Silva | MCITP| MCTS | MCP | ITILF | Blog: http://tilive.wordpress.com

    sexta-feira, 10 de fevereiro de 2012 17:05
  • Davi,

    A utilização do Include ou não é uma questão interessante sobre os indices, mas de forma rapida, posso explicar da seguinte maneira:

    Voce possui em seu indice um campo chave, esse campo chave aparecera nos nós do seu indice, e a estrutura de dados arvore sera dividida por este campo.

    Quando voce possui apenas um indice com o campo chave, tudo que voce possui nos nós folhas é o numero das linhas aonde tenha tal valor.

    Fazendo com que seja necessario ir até a tabela para buscar outros dados alem do chave (No plano de execução voce vera isso como um look up).

    Um exemplo de uma busca apenas pelo campo chave é:

    SELECT Id

    FROM Tabela

    WHERE Id = 5

    Neste caso, sua unica pesquisa sera no indice, trazendo o valor em si. Se voce alterar a query para:

    SELECT Id, Nome

    FROM Tabela

    WHERE Id = 5

    Ao encontrar os ID 5 na estrutura do indice, voce devera fazer uma pesquisa em sua tabela, nos numeros da linha apontados no indice, para buscar a informação do nome, mantendo o seguinte fluxo:

    Inicia -> Indice -> Tabela -> Retorno.

    Agora, se colocarmos o INCLUDE (Nome) em nosso indice, e executarmos o ultimo select, o indice sera:

    Inicia -> Indice -> Retorno.

    Pois apesar da estrutura de arvore do indice estar divida pelo campo chave (Id), o campo nome sera carregado por toda estrutura, não sendo necessario ir buscar este dado na tabela uma vez que voce ja o possui no indice.

    Tome cuidado pois esses indices são chamados de FullQueryIndex, ou seja, preencher 100% uma query completa, e realmente para tal query talvez seja a melhor opção, porem, estamos falando de uma query Ad-Hoc, o que talvez não compense a criação do mesmo, ainda mais por que pode interferir em seu ambiente transacional.

    A sugestão do SQL Server é valida e muitas vezes voce a utilizara, porem tome cuidado, não estou falando para criar ou não o indice, e sim que talvez, se voce for executar essa query apenas uma vez, não acho que valha o esforço, porem, se for uma query recorrente de seu ambiente transacional, talvez seja interessante, mas novamente, é preciso analisar.

    Como dica: Crie o indice, execute a query, veja o plano, veja se houve efetivamente uma melhora ou não.


    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

    sexta-feira, 10 de fevereiro de 2012 17:07
    Moderador
  • Obrigado pelas explicacoes. Entendi perfeitamente a explicacao do SELECT ID, Nome ... com e sem o INCLUDE.

    Agora eu posso cricar o INDICE de duas formas:

    CREATE NONCLUSTERED INDEX [IX_Texte] ON [dbo].[TabelaCliente] ( [Codigo] ASC, [Nome] ASC )

    OU

    CREATE NONCLUSTERED INDEX [IX_Teste] ON [dbo].[TabelaCliente] 
    (
    	[codigo] ASC
    )
    INCLUDE ( [Nome])

    Existe diferenca entre essas duas formas?

    sexta-feira, 10 de fevereiro de 2012 17:32


  • DaviSaba,





    No final segue um script bem detalhado com todos os casos
    possiveis que consegui pensar.



    Porem, sim, quando voce colocar o campo no include, um
    filtro por este campo fara a utilização do indice, porem seu custo (Vide plano
    de execução) sera maior do que colocando-o como indice chave (Primeiro caso),
    quando o mesmo for a segunda coluna (No exemplo...), porem, não se for a
    primeira. Segue abaixo.





    OBS: Tudo depende de seus dados e da quantidade, é
    impossivel dizer 100% se ele vai ou não utilizar tal indice, a melhor opção
    continua sendo criar e testar.



    --CRIA TABELA



    CREATE TABLE TesteDavi



    (



          CodigoINT,



          ClienteINT



    )





    --CARREGA DADOS
    NA TABELA



    DECLARE @Cont INT = 0



    DECLARE @Sql VARCHAR(8000)



    SET @Sql = ''



    WHILE(@Cont < 1000000)



    BEGIN



          SET@Sql='INSERT INTO TesteDavi
    VALUES ('
    +CAST(@ContASVARCHAR(100))+','+CAST(@Cont+100 ASVARCHAR(100))+')'



          EXEC
    (@Sql)



          SET@Cont
    =@Cont+1



    END





    -----PRIMEIRO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    --FULLTABLE SCAN





    -----CRIACAO DE INDICE 1



    CREATE INDEX IX_01 ON TesteDavi(Codigo) WITH (SORT_IN_TEMPDB=ON)





    -----SEGUNDO SELECT



    SELECT Codigo



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN





    -----TERCEIRO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN + LookUp (Comentado acima)





    -----QUARTO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5



    --FULLTABLE SCAN





    -----CRIACAO DE INDICE 2



    CREATE INDEX IX_01 ON TesteDavi(Codigo, Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----QUINTO SELECT



    SELECT Codigo



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN





    -----SEXTO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN SEM LOOKUP





    -----SETIMO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--3,11736



    --INDEX SCAN





    -----CRIACAO DE
    INDICE 3



    CREATE INDEX IX_01 ON TesteDavi(Codigo) INCLUDE(Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----OITAVO SELECT



    SELECT Codigo



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN





    -----NONO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 500000



    --INDEX SCAN SEM LOOKUP





    -----DECIMO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--3,11736



    --INDEX SCAN





    -----TESTE GERAIS



    CREATE INDEX IX_01 ON TesteDavi(Cliente) INCLUDE(Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----DECIMO
    PRIMEIRO SELECT



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--0,0032831





    -----CRIACAO DE
    INDICE 4



    CREATE INDEX IX_01 ON TesteDavi(Codigo, Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--3,11736





    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 5--0,0032831





    -----CRIACAO DE INDICE 4



    CREATE INDEX IX_01 ON TesteDavi(Cliente, Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)





    -----



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Cliente = 5--0,0032831





    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 5--3,11736





    -----CRIACAO DE INDICE 4



    CREATE INDEX IX_01 ON TesteDavi(Cliente, Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 5--3,11736





    CREATE INDEX IX_01 ON TesteDavi(Cliente)INCLUDE(Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)



    SELECT Codigo, Cliente



    FROM TesteDavi



    WHERE Codigo = 5--3,11736



    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

    sexta-feira, 10 de fevereiro de 2012 18:20
    Moderador