Rebuilding index takes 5-6 hrs. Can it be reduced?
<p>We are using Informatica for ETL and the database is Oracle 11g. </p>

<p>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. </p>

<p>We are following the below process for the incremental load.</p>

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

<p>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. </p>

<p>Can anyone suggest a faster way to rebuild the indexes? </p>