Is there a way to avoid corrupted indexes after running SQL Loader? — oracle-tech

    Forum Stats

  • 3,715,603 Users
  • 2,242,809 Discussions
  • 7,845,458 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Is there a way to avoid corrupted indexes after running SQL Loader?

simon87
simon87 Member Posts: 55 Blue Ribbon
edited January 21 in SQL & PL/SQL

I have this Oracle versión:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

After running a load with repeated data on the single-indexed column, the index becomes corrupted. How could you validate the index in the SQL Loader? to prevent it from corrupting the index.


Thank you :)

Tagged:

Answers

  • L. Fernigrini
    L. Fernigrini BI / ETL Consultant Member Posts: 3,479 Bronze Crown
    edited January 21

    You must be doing direct path load


    Even though UNIQUE constraints remain enabled during direct path loads, any rows that violate those constraints are loaded anyway (this is different than in conventional path in which such rows would be rejected). When indexes are rebuilt at the end of the direct path load, UNIQUE constraints are verified and if a violation is detected, then the index will be left in an Index Unusable state. See Indexes Left in an Unusable State.


    Either use conventional path, or clean up duplicates after the direct path load and rebuild the index.

Sign In or Register to comment.