Re: How to insert data using index unusable/rebuild

I knew this thread has the potential of degenerating into a heated and meaningless philosophical debate. It started with a few simple, albeit confusing to some, options that have real practical uses. Before you know it, people are debating what can or can not be done, what should or should not be done.

This will be my last response to this thread.

Did you ever reach the bottom of my previous post? What's wrong with the embedded comment/conclusions? There are important practical purposes for setting various combination of "disable/enable" and "validate/novalidate" on a constraint - notably in a data warehouse environment. Why do you think ORACLE allows you all these options? Read up on the "integrity constraints" section of the "Oracle9i Data Warehousing Guide".

SQL> -- Enable PK, as long as there is bad data, it can not be validated.
SQL> alter table test3_a modify constraint test3_a_pk enable novalidate;

Table altered.

SQL> select * from test3_a;

C1 C2
-- ----------

a           1
a           1
f           1

SQL> -- What you have here is a functioning PK that has duplicates. SQL> insert into test3_a values('f',1);
insert into test3_a values('f',1)
*
ERROR at line 1:
ORA-00001: unique constraint (JYANG.TEST3_A_PK) violated

SQL>

• Jusung Yang