Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Unanswered: Heavily indexed databases

    Hi all,
    i have a very general question about databases. What is the advantage and disadvantage of using a heavily indexed database?

    The advantage i could think is that search operations will be fast. The disadvantage (according to me who is a newbie) is that the size of the database will increase.

    My teacher however is not very happy with this answer and wants me to research more. Any help will be greatly appreciated.


  2. #2
    Join Date
    Feb 2004
    San Antonio, TX
    indexes aren't the lawn darts of the database world...

    you dont just thow them around and hope that they fit because more than likely they wont. indexes are additional architectural elements added to tables (or views) to decrease the amount of time that querying takes. there are known techniques that you can use to evaluate the "usefulness" of an index. the sql query analyzer contains a graphical execution plan tool that will indicate the use or the lack of use as well as the type of index operation being performed.

    remember that not only are there index operations being performed based on query SARGS (hey trotsky!!!) but they also affect join operations as well.
    consider the basic two table join between a unique PK column and a Dense FK column. by placing a nonclustered index on the fk column you create a sorted version of the Fk column thereby decreasing the time it takes to perform the join. (this is the typical result and not the 100% result)

    indexes have their bad sides as well. consider the lopsided levels of an index based on a unique column ( in oracle they call this right side heavy). in addition index performance is based on statistics and those statistics have to be updated and this is a cost added to your queries. also, dont forget that you will have to rebuild your indexed from time to time based on fragmentation of the leaf level.

    indexes are so misunderstood by developers and dbas alike.. i suggest you find a series of articles on indexes by kalen delaney at or look for index articles by Kimberly Tripp.

    BOL wouldnt hurt.

Posting Permissions

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