Index in Unusable state

vpatel · Post by **vpatel** » Thu May 03, 2007 10:08 am

Hi,
when I use Oracle Stage with option LOAD/APPEND to load 2 lac records in a table its indexes become in unusable state.
Moreover I made oracle stage "Sequential".
Also, I tried with option "Index Rebuild" in Oracle Stage. But it also didn't help.

One solution whcih i see is just drop the indexes & recreate them after data being loaded. But this solution doesn't seem feasible to me as I have to load no. of tables & those tables have so many indexes.

Is there any other solution? can something be done in Job itself so this problem doesn't come.

Thanks in advance!!

Regards,
Vinod

chulett · Post by **chulett** » Thu May 03, 2007 11:10 am

Indexes are typically left in an UNUSABLE state post load because a unique index is found to have duplicates. You need to ensure nothing like that is going on. There are other reasons, of course, but that's the biggie.

nick.bond · Post by **nick.bond** » Thu May 03, 2007 4:13 pm

With index rebuild the indexes should be ok after the job completes successfully.

As Craig said this won't be the case if the index rebuild failed. Is the job completing successfully?