Printable View
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
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.
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
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.
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
I've done an "analyze compute/estimate statistics for column_Name", if that's what you mean.
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