12 Replies Latest reply: Feb 8, 2011 9:19 PM by 715920 RSS

    Enable NoValidate Constraint

    715920
      Dear all,
      my understanding about ENABLE NOVALIDATE is when it is set in a constraint, it won't validate the existing data but will do for the next insertion.

      Suppose I do this:
      SQL> create table hr.test (temp_num number);
      
      Table created.
      
      SQL> insert into hr.test values (1);
      
      1 row created.
      
      SQL> insert into hr.test values (1);
      
      1 row created.
      
      SQL> insert into hr.test values (1);
      
      1 row created.
      
      SQL> alter table hr.test add constraint temp_const unique (temp_num) enable novalidate;
      alter table hr.test add constraint temp_const unique (temp_num) enable novalidate
                                         *
      ERROR at line 1:
      ORA-02299: cannot validate (HR.TEMP_CONST) - duplicate keys found
      I created a table without constraint and a column with multiple rows that have same value. I had expected a success constraint creation when I added ENABLE NOVALIDATE clause, but it failed. Why was it validating the existing data?

      Best regards,
      Val
        • 1. Re: Enable NoValidate Constraint
          Karthick_Arp
          This one has the answer for you.

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8806498660292
          • 2. Re: Enable NoValidate Constraint
            Dom Brooks
            Because a unique constraint will by default use a unique index.
            A unique index can't be novalidate.

            You'd have to create a non-unique index to protect your novalidate unique constraint.

            E.g.
            SQL> create table test 
              2  (temp_num number);
            
            Table created.
            
            SQL> 
            SQL> insert into test values (1);
            
            1 row created.
            
            SQL> insert into test values (1);
            
            1 row created.
            
            SQL> insert into test values (1);
            
            1 row created.
            
            SQL> 
            SQL> create index i1 on test (temp_num);
            
            Index created.
            
            SQL> 
            SQL> alter table test add constraint u1 unique (temp_num) using index i1 enable novalidate;
            
            Table altered.
            
            SQL> 
            SQL> select * from test;
            
              TEMP_NUM
            ----------
                     1
                     1
                     1
            
            SQL> insert into test values (1);
            insert into test values (1)
            *
            ERROR at line 1:
            ORA-00001: unique constraint (RIMS.U1) violated
            
            
            SQL> insert into test values (2);
            
            1 row created.
            
            SQL> 
            • 3. Re: Enable NoValidate Constraint
              HiteshNirkhey
              refer this

              http://www.dbasupport.com/forums/archive/index.php/t-7270.html

              Thanks
              • 4. Re: Enable NoValidate Constraint
                Peter Gjelstrup
                my understanding about ENABLE NOVALIDATE is when it is set in a constraint, it won't validate the existing data but will do for the next insertion.
                In my opinion this is misuse of constraints. A unique constraint should not be applied unless ALL the data meets that constraint.

                You should not use as, "In the future, this will be unique".

                The enable novalidate - in my opinion - Is just a hack to minimize actual work done when enabling constraints. When you use it, you "know" that the constraint could be enabled.


                Regards
                Peter
                • 5. Re: Enable NoValidate Constraint
                  Dom Brooks
                  In my opinion this is misuse of constraints
                  But sometimes (often actually) you come across a system where constraints are not in place, and data integrity has been compromised.
                  And the first step is to stop the cr4p coming in (and can help identify where in this holy middle tier it's getting in).

                  ENABLE NOVALIDATE can be a first step with a view to getting to enabled and validated where possible.

                  In that situation I'd rather have ENABLE NOVALIDATE than no constraint.
                  • 6. Re: Enable NoValidate Constraint
                    837524
                    you can do this wiith (novalidate) clause....
                    • 7. Re: Enable NoValidate Constraint
                      Dom Brooks
                      brilliant
                      • 8. Re: Enable NoValidate Constraint
                        Peter Gjelstrup
                        Hi Dom,

                        I know the arguments. So once you have identified from where the crap comes. Stop it, and then what?

                        If you want to apply that constraint, then apply it. But, that requires a data clean up. Better do it now than later (Never)


                        Regards
                        Peter
                        • 9. Re: Enable NoValidate Constraint
                          Dom Brooks
                          They are misused without a doubt.


                          This might be a rubbish analogy...

                          If you've got a leak coming through the ceiling, do you start cleaning it up and then turn off the tap?
                          Or best to turn off the tap first, then clean up.
                          Depends on how long it takes to turn it off I suppose.

                          I know I've used ENABLE NOVALIDATE as a first step before.


                          But I've also worked on systems where ENABLE NOVALIDATE caused problems so it can be misused, definiteiyl

                          While we're on the subject....

                          Here's an example from a system that I worked on recently where as part of the release process all foreign keys were disabled - saved having to do stuff like doing things in the right order!!

                          And then at the end of the release the FKs were re-enabled with ENABLE NOVALIDATE (Fingers crossed that those inserts that that release just did don't violate the data integrity!)

                          Obviously the occasional performance benefits of FKs can be redundant @ ENABLE NOVALIDATE

                          e.g.
                          SQL> drop table t2;
                          
                          Table dropped.
                          
                          SQL> 
                          SQL> drop table t1;
                          
                          Table dropped.
                          
                          SQL> 
                          SQL> create table t1
                            2  (col1 number);
                          
                          Table created.
                          
                          SQL> 
                          SQL> alter table t1 add constraint pk_t1 primary key (col1);
                          
                          Table altered.
                          
                          SQL> 
                          SQL> create table t2
                            2  (col1 number);
                          
                          Table created.
                          
                          SQL> 
                          SQL> alter table t2 add constraint pk_t2 primary key (col1);
                          
                          Table altered.
                          
                          SQL> 
                          SQL> insert into t1 
                            2  select rownum
                            3  from   dual
                            4  connect by rownum <= 10;
                          
                          10 rows created.
                          
                          SQL> 
                          SQL> insert into t2 
                            2  select rownum
                            3  from   dual
                            4  connect by rownum <= 10;
                          
                          10 rows created.
                          
                          SQL> 
                          SQL> alter table t2 add constraint fk_t2_1 foreign key (col1) references t1 (col1) enable novalidate
                          ;
                          
                          Table altered.
                          
                          SQL> 
                          SQL> explain plan for
                            2  select t1.col1
                            3  from   t1
                            4  ,      t2
                            5  where  t1.col1 = t2.col1;
                          
                          Explained.
                          
                          SQL> 
                          SQL> select * from table(dbms_xplan.display);
                          
                          PLAN_TABLE_OUTPUT
                          ----------------------------------------------------------------------------------------------------
                          Plan hash value: 3514408644
                          
                          ----------------------------------------------------------------------------
                          | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                          ----------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT   |       |    10 |   260 |     1   (0)| 00:00:01 |
                          |   1 |  NESTED LOOPS      |       |    10 |   260 |     1   (0)| 00:00:01 |
                          |   2 |   INDEX FULL SCAN  | PK_T1 |    10 |   130 |     1   (0)| 00:00:01 |
                          |*  3 |   INDEX UNIQUE SCAN| PK_T2 |     1 |    13 |     0   (0)| 00:00:01 |
                          ----------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             3 - access("T1"."COL1"="T2"."COL1")
                          
                          Note
                          -----
                             - dynamic sampling used for this statement (level=4)
                          
                          19 rows selected.
                          
                          SQL> 
                          SQL> alter table t2 modify constraint fk_t2_1 enable validate;
                          
                          Table altered.
                          
                          SQL> 
                          SQL> explain plan for
                            2  select t1.col1
                            3  from   t1
                            4  ,      t2
                            5  where  t1.col1 = t2.col1;
                          
                          Explained.
                          
                          SQL> 
                          SQL> select * from table(dbms_xplan.display);
                          
                          PLAN_TABLE_OUTPUT
                          ----------------------------------------------------------------------------------------------------
                          Plan hash value: 3400771338
                          
                          --------------------------------------------------------------------------
                          | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                          --------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT |       |    10 |   130 |     1   (0)| 00:00:01 |
                          |   1 |  INDEX FULL SCAN | PK_T2 |    10 |   130 |     1   (0)| 00:00:01 |
                          --------------------------------------------------------------------------
                          
                          Note
                          -----
                             - dynamic sampling used for this statement (level=4)
                          
                          12 rows selected.
                          Richard Foote has example of performance implications of NOVALIDATE primary keys here:
                          http://richardfoote.wordpress.com/2008/07/30/novalidate-constraints-part-ii-does-it-matter/
                          • 10. Re: Enable NoValidate Constraint
                            Peter Gjelstrup
                            Thanks Dom for putting work into this.

                            We might agree when you say
                            I know I've used ENABLE NOVALIDATE as a first step before.
                            Key word being first, assuming there will be a next ;)

                            Regards
                            Peter
                            • 11. Re: Enable NoValidate Constraint
                              Dom Brooks
                              Unfortunately I needed the same test case to show same thing at work just recently.
                              Just thought I'd chuck it into the thread for extra info for anyone else.
                              • 12. Re: Enable NoValidate Constraint
                                715920
                                Many thanks, you guys are brilliant [thumbs up]!