Should I index?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Should I index?

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227

    Should I index?

    Hi,

    I have a couple of tables with at least 5 million rows each from which ad-hoc reports generated. The reports can be sliced-and-diced using a combination of any of the 15+ columns on these tables. My goal is to improve the poor performance of these reports. Questions:

    1- Do you advice that I create an index on each of the columns used in the where clause (taking into account all the various scenarios that the report can be sliced-and-diced)
    2. What kind of index - bitmap or b-tree. The environment is a dss (data warehouse) and the average # of distinct rows is 3800 out of 5 million rows.
    3. If the tables are partitioned (by range), is it recommended to also partition the indexes (using the same range as the table). Comments are also welcome.


    Thanks,
    L
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Should I index?

    1- Do you advice that I create an index on each of the columns used in the where clause (taking into account all the various scenarios that the report can be sliced-and-diced)
    yes, and analyze columns also.

    2. What kind of index - bitmap or b-tree. The environment is a dss (data warehouse) and the average # of distinct rows is 3800 out of 5 million rows.
    3800 out of 5,000,000 sound ok for BM indexes. For columns at about 10,000 unique values you might like to test sizes of BM vs. btree.

    3. If the tables are partitioned (by range), is it recommended to also partition the indexes (using the same range as the table). Comments are also welcome.
    yes.

    Consider physically ordering the data within each partition by the columns most commonly used as predicates in user reports also, to increase block selectivity of the indexes.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    If this is the fact table for a DSS, the answer is almost always to bitmap all the keys. Why would you not?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Also, ensure that FK's are in place to the dimension tables, even if the are disbable+novalidate+rely , and enable star_transformations.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jul 2000
    Posts
    521
    This query is taking a minute ?!! And, the table has 2000 rows.

    What kind of hardware are you running this database on ?
    svk

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by svk
    This query is taking a minute ?!! And, the table has 2000 rows.
    I am lost here! What are you talking about, svk? Where did you get those informations from? And what relevance do they have on the original question. Enlighten me, please.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    thats a reply to a different thread, i think.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Ah, it makes much more sense now.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jul 2000
    Posts
    521
    I'm sorry !! It really should have been a reply to another question in the same forum.
    svk

Posting Permissions

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



Click Here to Expand Forum to Full Width