Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    13

    Unanswered: How much performance hit if I create index on all columns

    How much performance hit if I create index on all columns.

    Let's say I have a table with 20 columns and index on each column.

    How much peformance/overhead with and without indexes?

    Thanks,
    Veera

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It depends... If you do mostly queries like in a data warehouse, it is beneficial to create all possible indexes. If you have more of an OLTP workload, you only want to create the indexes that you really need due to the added index maintenance overhead.

    Furthermore, I wouldn't simply create indexes on all columns. It may be better to have an index on a combination of columns and you may not need indexes on some columns. Therefore, a good start would be to run your SQL statements as workload through the Design Advisor and see what indexes are suggested by that tool. The next step is to understand how indexes work (e.g. what is index-only access, prefetching, include columns, just to name very few) and then apply this knowledge to your workload.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by mowry_889
    How much performance hit if I create index on all columns.

    Let's say I have a table with 20 columns and index on each column.

    How much peformance/overhead with and without indexes?

    Thanks,
    Veera
    Big hit, and you probably want some composite indexes and not just indexes with one column. Probably less than 5 of the indexes will ever be used by DB2, but DB2 will have to maintain them on inserts, updates, and deletes.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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