Another question on Index!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Another question on Index!

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    83

    Question

    I am trying to create an index on 1 primary key on a partitioned table. As the table contains over billion rows - as you expect - it takes ages to create an index.

    At the moment, I am creating a global index rather than that of local for partitions.

    Can anyone tell me what is the best way to create an index for such large table?

    Thanx in advance
    Fiona
    x
    Share on Google+

  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    A few suggestions:

    a) if the data is already sorted (e.g. it's a key field which has been generated from an ascending sequence), you could use the nosort option

    b) you could create the index with nologging set

    c) for less selective columns, you could consider using bitmap rather than b*tree indexes because they take a fraction of the time to build.

    d) if your system is a multi-processor, make sure you are building the index in parallel.
    Share on Google+

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    All very good suggestions!

    I'd simply like to emphasize the bitmap index option. With a billion rows, I can only assume that this is at least a reporting database if not an actual warehouse. If it is a warehouse, it should absolutely use bitmaps. If it is a reporting database, it should probably use bitmaps.

    Just my .02,

    - Chris
    Share on Google+

  4. #4
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    I'm glad you recommend bitmaps because I've gone that way on my database.

    The only problem I've found is that Oracle seems a bit reluctant to use them if you only query on a single column. Sometimes, you have a column with badly distributed data e.g. one value might have a million records, another might have only 1. It would be nice if the optimizer was smart enough to work out that the bitmap index should be used for the row with only 1 value.

    It will do this with B*tree indexes but with bitmaps it never does. It doesn't help if you do "alter table estimate/compute statistics for indexed columns". This is despite the fact that it is often clearly more efficient to use the bitmap than do a full table scan. I've done tests where I've used a hint to tell the optimizer to use the bitmap and it is definitely much quicker.
    Share on Google+

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Have you tried creating a histogram on this column?

    Check this thread for histogram usage:
    http://www.dbasupport.com/forums/sho...?threadid=9787

    Remember that bitmaps and b*trees are used for different ends of the cardinality spectrum. Assumptions about one cannot be applied to the other.

    - Chris
    Share on Google+

  6. #6
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    I've done an "analyze compute/estimate statistics for column_Name", if that's what you mean.
    Share on Google+

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

    Add SIZE 75; to the end of that statement and you will get a 75-bucket histogram. I would suggest you look into histograms before using them, however. You can start with the thread I linked for you.

    - Chris
    Share on Google+

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