Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70

    Unanswered: Help with Indexes

    I have a snapshot table, of which mostly used columns are item_id and link_id.
    My application has several queries including all the 3 combinations like

    Only link_id
    Only item_id
    Both link_id and item_id.

    Now what is the best index(Non-clustered) which suits in this situation.
    This table is a highly populated table with regular inserts and updates.

    Thanks,
    Pavan.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you mean with those three examples that you filter sometimes on link_id, sometimes on item_id and sometimes both?

    What proportion of the table would (typical) examples of the above query return (e.g. 0.1%, 8%, 15% etc)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Yes, they return anywhere between 0.1 to 1% or may be a little more.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If it is less than (IME) 3% or so of the table (I have heard higher quoted but I imagine that depends somewhat on the size of the rows in the data page) then you should get a seek with lookups to the CI.

    I would try NCIs of (link_id, item_id) and (item_id, link_id). You may find you need to get rid of one of these and replace with (item_id) and (link_id) - depends on whether or not you are getting good plans.

    What columns do you return from the table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    We already have an index on that table with NCI(Item_id,link_id), but I found out from the trace & DB tuning adviser recommendation for other index on link_id column.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's better - not sure why I suggested both columns twice.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    A quick question , whats the diff btn create statistics and create index.
    I know that statistics help query optimiser to run the queries faster, and SQL is asking us to create indexes on those col's where the stats are created.!
    Is there any overhead in creating stats like db space, index page usage etc which is for creating multiple indexes

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Index enables faster access to data, while statistics tell the optimizer which index to use and, most importantly how.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, the optimiser generates statistics on columns that may benefit from indexing (as per your second line).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Thank you...Gotcha..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •