none
how to run query without index?

    Question

  • Hi,

    The DBA has created few indexes in the Datawarehouse, where i have only read access, so cannot disable the indexes.

    Is there a way to disalbe the indexes with a query? If so can you please provide syntax for it?

    LuckY


    • Modifié Luckwhy jeudi 12 septembre 2013 20:52
    • Type modifié Luckwhy jeudi 12 septembre 2013 20:55
    • Type modifié Luckwhy jeudi 12 septembre 2013 20:57
    jeudi 12 septembre 2013 20:51

Réponses

Toutes les réponses

  • here is a way to do that:

    http://stackoverflow.com/questions/11016935/how-can-i-force-a-query-to-not-use-a-index-on-a-given-table

    But the main question is why you don't want to use indexes?!


    Regards,

    Reza

    SQL Server MVP

    Blog:   http://rad.pasfu.com  Twitter:   LinkedIn:

    SQL Server Integration Services 2012 Tutorial Videos:     http://www.radacad.com/CoursePlan.aspx?course=1

    • Marqué comme réponse Luckwhy jeudi 12 septembre 2013 21:31
    • Non marqué comme réponse Luckwhy jeudi 12 septembre 2013 21:34
    • Marqué comme réponse Luckwhy jeudi 12 septembre 2013 22:00
    jeudi 12 septembre 2013 21:02
    Modérateur
  • Try with (index(0)) hint
    • Marqué comme réponse Luckwhy jeudi 12 septembre 2013 22:00
    jeudi 12 septembre 2013 21:04
  • Thanks for the quick reply.

    We would like to compare the runtimes stats of the queries with/without indexes. We asked the DBA help to analysis and feedback on the proposed indexes, he went ahead and created them already, so now i dont have stats on before the indexes. And DBAs time is really precious in our company.

    Below is the query i am running, addding the WITH(INDEX(0), where i added would work?

     

    SELECT

    dbo.T_DIM_ACCOUNT.CUSTOMER_SIZE_CD,

    dbo.T_DIM_ACCOUNT.PARENT_NAMED_ACCOUNT_GROUP_NM,

    dbo.T_DIM_ACCOUNT.NAMED_ACCOUNT_GROUP_NM,

    SUM(dbo.T_FACT_ASSET.SEAT_QTY)

    FROM

    dbo.T_DIM_ACCOUNT WITH (INDEX(0))

    INNER JOIN dbo.T_FACT_ASSET WITH (INDEX(0)) ON

    (dbo.T_DIM_ACCOUNT.ACCOUNT_KEY=

    (CASE

    WHEN

    dbo.T_FACT_ASSET.CUSTOMER_ACCOUNT_KEY = 0

    THEN dbo.T_FACT_ASSET.SOLD_TO_ACCOUNT_KEY

    ELSE

    dbo.T_FACT_ASSET.CUSTOMER_ACCOUNT_KEY END) AND dbo.T_DIM_ACCOUNT.SRC_DELETE_FLG!='Y')

    GROUP BY

    dbo.T_DIM_ACCOUNT.CUSTOMER_SIZE_CD,

    dbo.T_DIM_ACCOUNT.PARENT_NAMED_ACCOUNT_GROUP_NM,

    dbo.T_DIM_ACCOUNT.NAMED_ACCOUNT_GROUP_NM

    jeudi 12 septembre 2013 21:23
  • As Reza noted, if you use with index hint, your query will execute with that index you want. But for time; you can execute two queries, one with index hint and another without hint. To know the duration you can use these options in the start of your code:

    SET STATISTICS IO ON;
    SET STATISTICS TIME ON; 
    GO
    
    -- query with index hint
    
    -- query without index hint


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page

    • Marqué comme réponse Luckwhy jeudi 12 septembre 2013 22:00
    jeudi 12 septembre 2013 21:38