Rebuilding index takes 5-6 hrs. Can it be reduced?

Question

We are using Informatica for ETL and the database is Oracle 11g.

We have a fact table (2 billion records) which is loaded daily. There are around 44 indexes for this fact table and the fact table is partitioned.

We are following the below process for the incremental load.

Identify the partitions to be loaded from staging table. Usually there will be around 750k - 900k records for 3 to 4 partitions
Create a temporary table and do a partition exchange from the fact table for the partitions
Populate the incremental data from the staging table to the temporary table
Do a partition exchange again to the fact table from the temporary table
Rebuild the indexes

The total load time is around 8– 10 hours and rebuilding index is taking majority of the time (around 5-7 hours). The indexes are rebuild only for the partitions affected and all indexes are local indexes.

Can anyone suggest a faster way to rebuild the indexes?

Its a big table and well partitioned by range and an index on each partition. All counts to 44. — XING, Dec 28 '16 at 17:33
"...and an index on each partition" - are you saying you're creating a separate index on each partition, each with the same fields? — Bob Jarvis - Слава Україні, Dec 28 '16 at 18:01
Do the larger indexes cover just the partition or the entire table? — Ross Bush, Dec 28 '16 at 18:03
Can you please provide table DDL, it is not clear for me how it looks like. — Wernfried Domscheit, Dec 28 '16 at 19:04

score 4 · Accepted Answer · edited Dec 28 '16 at 18:03

4

You could rebuild parallel, then set back to no parallel once completed. Something like (simplified example):

Alter index SOMEOWNER.SOMEIDX rebuild
Parallel 8;

Alter index SOMEOWNER.SOMEIDX noparallel;

edited Dec 28 '16 at 18:03

Jon Heller

34,999
6
74
132

answered Dec 28 '16 at 17:58

tbone

15,107
3
33
40

We have many other jobs running as well so do you think making parallel 8 will not affect cpu utilization ans other jobs. – XING Dec 28 '16 at 18:24
If you're really concerned start with parallel of 4 maybe and see. I don't know what your environment is so I can't say for sure, but I think you'll be fine, especially since this should be done off hours. – tbone Dec 28 '16 at 18:27
Start with half the number of cores you have and work your way up/down as needed. You may have to pick some compromise position that balances your index job getting done in time vs impact to other jobs. Also, remember to check your storage for bottlenecks. Oracle can drive it really hard with this big reindex job. – SQLmojoe Dec 28 '16 at 21:05
1

Thanks ..this worked. Was able to reduce time to Approx 2-3hrs. – XING Dec 29 '16 at 07:05

Rebuilding index takes 5-6 hrs. Can it be reduced?

1 Answers1