Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ENABLE NOVALIDATE behaviour bug

Re: ENABLE NOVALIDATE behaviour bug

From: Mark Richard <>
Date: Wed, 30 Jun 2004 08:15:21 +1000
Message-ID: <>

Thanks to Tanel and Richard for explaining this. I know realise that I misunderstood the meaning of "novalidate" - I was hoping it might mean "trust me Oracle, I know it to be true" but it can only mean "from today on, enforce this - who knows about the past". I should have realised that.

I'll take Tanel's advice of creating the column with a default. This is yet another area I wasn't sure about... I wasn't positive that a default value would be applied to existing rows since adding a default clause to a pre-existing column doesn't do this. So I've asked one question and received two answers - Well done and thankyou.



                      "Richard Foote"                                                                                                  
                      <richard.foote_at_big        To:       <>                                                     
            >                 cc:                                                                                    
                      Sent by:                  Subject:  Re: ENABLE NOVALIDATE behaviour bug                                          
                      29/06/2004 22:26                                                                                                 
                      Please respond to                                                                                                

Hi Mark,

Just to expand a little on a point made by Tanel. It can be a little "dangerous" to enable a constraint with novalidate. By doing so, you're effectively telling Oracle the data is valid, honest, whist the optimizer takes the attitude, "actually, I really don't believe you". This means that possibly useful constraint data can't be used by the optimizer when determining the best plan.

A very simple example. We had a statement that required an "empty" set to be
returned and used a query to list all null values for a not null column. Problem being the constraint was inadvertently enabled with novalidate after
the table was rebuilt meaning that a previous "efficient" plan was replaced by a horrendously expensive and unnecessary FTS. Although it might sound like an odd thing to do (and in this specific example, it was a rather odd thing to do), it's not actually uncommon for queries to sometimes request data that can't possibly exist due to a constraint. Only by having a validated constraint can the optimizer "know" that such a query will return no rows and act accordingly (or "know" that there are no nulls and use an index etc ...).

Sometimes performing one scan to validate a constraint can save many subsequent unnecessary scans !!



Hi List,

I regularly have to change the structure of some fairly large tables (~200m rows). Often we use the opportunity to do a full table rebuild if we want to change other settings (such as index locations) but other times we would prefer to modify the existing table.

I currently have the scenario where I need to add a single CHAR(1) column to a 250m row table and populate it with a constant value (new records may have a different value). The approach I am considering is:

  1. alter table blah add (mycol char(1));
  2. update blah set mycol = 'F'; {perhaps include a parallel hint on this statement}
  3. alter table blah modify mycol not null enable novalidate;

I was hoping to use "enable novalidate" to avoid a verification of all records when I know they will be populated.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: ---------------------------------------------------------------- To unsubscribe send email to: put 'unsubscribe' in the subject line. -- Archives are at FAQ is at -----------------------------------------------------------------
Received on Tue Jun 29 2004 - 17:12:24 CDT

Original text of this message