Forum Stats

  • 3,757,657 Users
  • 2,251,253 Discussions
  • 7,869,882 Comments

Discussions

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

simon87
simon87 Member Posts: 55 Blue Ribbon
edited Jan 21, 2021 5:14PM 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 Data Engineer Sr Consultant Member Posts: 3,578 Silver Crown
    edited Jan 21, 2021 6:21PM

    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.