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: Richard Foote <>
Date: Tue, 29 Jun 2004 22:26:13 +1000
Message-ID: <010601c45dd4$44d9c970$0100000a@FOOTE>

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
Received on Tue Jun 29 2004 - 07:17:46 CDT

Original text of this message