Re: Impact on changing an enabled validated constraint to enable novalidate ?

<zejeanmi_at_gmail.com> wrote in message
news:1175526888.604740.149610_at_q75g2000hsh.googlegroups.com...
> Hello,
>
> We would like to change constraints that are enabled and validated to
> "ENABLE NOVALIDATE" state.
>
> I think that there is no problem doing that : any new line or update
> will be checked thanks to the "ENABLE" state.
> I feel that once validated there is no point in keeping the constraint
> in "VALIDATE" state. "ENABLE NOVALIDATE" is sufficient.
> VALIDATE seems only useful when we create a constraint on existing
> data.
>
> Am i right ?
> Is there a (hidden) danger changing the constraint to NOVALIDATE ?
>

Hi Jean-Michel

Yes, there can be some hidden dangers. By making a constraint novalidate, you're basically saying to the CBO there *might* be data which currently violates the constraint as existing data is not checked when enabled. An example where things behave differently is as follows.

Firstly, lets just create a table with index .

SQL> create table david_bowie as select * from dba_objects;

Table created.

SQL> create index david_bowie_status_idx on david_bowie(status);

Index created.

Next lets put a not null constraint on a column and analyze the table.

SQL> alter table david_bowie modify status not null;

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE', tabname=> 'DAVID_BOWIE', cascade=>true);

PL/SQL procedure successfully completed.

Now when we run a simple searching for values that are null within the column ...

SQL> set autotrace on
SQL> select * from david_bowie where status is null;

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=93)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DAVID_BOWIE' (Cost=1 Car

          d=1 Bytes=93)

   2    1     INDEX (RANGE SCAN) OF 'DAVID_BOWIE_STATUS_IDX' (NON-UNIQ
          UE) (Cost=1 Card=1)

Statistics

          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        918  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

we notice that Oracle has been clever enough to "know" there can be no such null value within the data because of the validated not null constraint and can make use of the index to avoid an unnecessary full table scan.

However, if we convert the constraint to novalidate ...

SQL> alter table david_bowie modify status null;

Table altered.

SQL> alter table david_bowie modify status not null novalidate;

Table altered.

SQL> select * from david_bowie where status is null;

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1 Bytes=93)    1 0 TABLE ACCESS (FULL) OF 'DAVID_BOWIE' (Cost=40 Card=1 Bytes

          =93)

Statistics

        185  recursive calls
          0  db block gets
        418  consistent gets
          0  physical reads
          0  redo size
        918  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0  rows processed

The CBO now must search through all the data and perform an expensive full table scan because there's the possibility that a null *might* exist within the data because the constraint is novalidated.

Yes, there are hidden dangers in suddenly making all constraints novalidate ...

Cheers

Richard Received on Tue Apr 03 2007 - 07:41:15 CDT