how to choose composite index

  • Hi All,

    🙂 I'm working in an MNC.presently i'm dealing with performance issues related to queries.i'm having a table called Lookup table.i am using two fields lkup_type and lkup_value together in most of my queries in the where clause as where lkup_type='PHONE' and lkup_value='1234' .can i use these two together as a composite index.will it improve my performance

    Kiran

  • If the rest of the query is correct for it, yes.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi,

    Yes you can have One Compiste index. But as i understand by the definion of LookUp table, Look Up tables are generelly very small. If it is small table then no need to have index.

    How many records you have it in Lookup Table?

    Thanks -- Vj

    http://dotnetvj.blogspot.com

  • I disagree... a lookup or definition table better have a Primary Key...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thanks jeff,

    one more thing.I am using Toad to know the explain plan of my queries.The query optimizer is taking nested loop join as best amoung hash join and merge join.but if i force my query to use hash join(using hints) the cost is getting reduced.Is it a gud practise to force my queries to use hints?

    Kiran

  • Not normally... of course, there are always exceptions.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    :blink: Can u give me more details regarding when can i use hints in my query.

    Kiran.

    Kiran

  • Best thing to do is to look them up in Books Online. Bottom line is, if they work, then it's probably a good thing... if they don't work (as shown be execution plan and testing), then don't use them. Most of the time, you won't need to use them.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the info.:) i will check it.

    Kiran.

    Kiran

  • Note that the query engine, when creating a plan, takes into account things such as table statistics, etc. If you are checking your query in a small development environment where the number of rows, and often their statistical distribution, is different from a production environment, then you may be hurting yourself by forcing a join type with a hint.

    Also, it sounds like you have a fairly generic lookup table so I'm assuming the number of rows could get fairly large.

    Your wording "composite index" is often referred to in these circles as a "covering index". If you look up that term on this site you'll get a lot of information 🙂

  • yes Ian, my lookup table has more than 1000 rows.

    By composite index i mean that i can put the two conditions in the where clause lkup_type and lkup_value together as an index which i call it composite index.covering index means what i have indexed in my lookup table i'm trying read (if and only if i use those keys for joining) using select clause.

    Or should i need to index lkup_type and lkup_value as two separate non-clustered indexes?

    Kiran

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply