Index Organised FACT table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Index Organised FACT table

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    A consultant recently suggested changing the FACT table of our data warehouse to be index organised. I couldn't see a lot of advantages of this, except perhaps in space saving. I was wondering if anyone has tried it and got any benefits.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Uh, can't say as that makes any sense to me either.

    Here is a link where we talk about indexing strategies for fact tables. Basically - lots of bitmap indexes.

    It would be very rare for you to actually access facts via the PK, which should generally be your only B*Tree, and, hance, organize-able index. So, if you don't access it via that index, why organize via that index?


    - Chris

  3. #3
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    Thanks for your reply. Is there supposed to be a link in your message ? It doesn't show up.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    Doh!

    http://www.dbasupport.com/forums/sho...threadid=11745

    - Chris
    You are the weakest link, goodbye!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Dang!!

    Voted off again!! :(

    - Chris

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There are many benefits having a fact table defined as IOT. They are:

    1 The space used for PK is not required. Hence, multiple I/Os on Index and data are not required.

    2 Since most of the fact table has date (or time key) as the first column in the table as well as first column in the index, that is always used in the WHERE clause, speed of retrieving the data is maximized.

    3 IOTs can be partitioned

    Disadvantages are:
    1 Physical rowid is NOT maintained on IOT. You are left with only logical ROWID. In case of block corruption, and you want to extract maximum number of rows from the table, you have to use PK only not the min or max of rowid.

    2 PK constraint can not be dropped

    3 Unique constraints are not allowed

    4 Distribution and replication are not allowed

    5 Bit Mapped Index is not allowed on IOT

    Carefully evaluate the requirements, particularly ad hoc queries. If the table requires bit mapped indexes on columns such as Male/Female, Status (Active/Inactive), YES/NO etc, then IOT is NOT a good choice.



  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by chrisrlong
    Doh!

    http://www.dbasupport.com/forums/sho...threadid=11745

    - Chris
    BTW, nealh, it's a link to a thread *you* have started a month ago.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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