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

Very impressive.

Thanks a lot for this accurate answer.

Jean-Michel

On Apr 3, 2:41 pm, "Richard Foote" <richard.fo..._at_bigpond.nospam.com> wrote:
> <zejea..._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 - 08:04:46 CDT