Hi Tom,

I just had a quick read of this thread but didn't see this point so I just thought I'll mention it as it can be a little trap for the unwary when using novalidate constraints and it doesn't involve query rewrites.

Very simple demo to hightlight:

SQL> show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      FALSE

SQL> create table bowie as select * from dba_objects;

Table created.

SQL> create index bowie_status_idx on bowie(status);

Index created.

SQL> alter table bowie modify status not null;

Table altered.

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

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select * from 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 'BOWIE' (Cost=1 Card=1 Bytes=93)

   2    1     INDEX (RANGE SCAN) OF 'BOWIE_STATUS_IDX' (NON-UNIQUE) (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
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Note that the optimizer is "clever" enough to know that there can't be a null value for the status column due to the not null constraint and so has avoided an unnecessary FTS. A quirky example of where a b*tree index can be useful on a low cardinality column if it's possible for users to select data that can't exist.

However, if one enables the constraint with novalidate ...

SQL> alter table bowie modify status null;

Table altered.

SQL> alter table bowie modify status not null novalidate;

Table altered.

SQL> select * from bowie where status is null;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=1 Bytes=93)
   1    0   TABLE ACCESS (FULL) OF 'BOWIE' (Cost=39 Card=1 Bytes=93)

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

Now the optimizer has no choice but to perform the FTS as it can't know for certain that there are no nulls as the constraint has not been validated. Similar problems can be encountered for other check constraints as well. 

Strangely, attempting to select data that can't exist or the requirement for an empty set is more common than many realize. In many scenarios, a validated constraint can provide the optimizer with useful/vital information.

Cheers

Richard Foote 

I've got a data warehouse and marts where the referential integrity is ETL-enforced.  However, it's a pain to reverse-engineer the data structures in ERwin (or any other data modeling tool) because the RI is not documented in the database.  I'm thinking that establishing DISABLED RELY NOVALIDATE would be appropriate for documentation purposes.  My example set up is:

CREATE TABLE DIM ( DIM# INTEGER NOT NULL );
ALTER TABLE DIM ADD  ( PRIMARY KEY (DIM#) ) ;
CREATE TABLE F ( DIM# INTEGER NULL );
ALTER TABLE F ADD constraint FK_D_TO_F
    FOREIGN KEY (DIM#) REFERENCES DIM
    RELY DISABLE NOVALIDATE;

Which "works", that is:
1. it doesn't impede the ETL because the constraint is DISABLEd
    SQL> insert into f values ( 0 );
    1 row created.
    SQL> insert into dim values ( 1 );
    1 row created.
2. is read by ERwin as a constraint
3. could work with MVs because it's RELY

Would this be your recommended approach, and is there anyplace in Oracle to add free-form text comments on why the constraints are disabled? 

SQL> analyze index OINV_ID validate structure;

Index analyzed 

SQL> analyze table oda.outinvoices validate structure cascade

ORA-01499 table/index cross reference failure - see trace file
 

ALTER TABLE myemp MODIFY CONSTRAINT pk_myemp
DISABLE VALIDATE;

SQL> INSERT INTO myemp (empno) VALUES (21);

ORA-25128: No insert/update/delete on table with constraint (auser.PK_myemp) disabled and validated
========================================================================
Is this a "proper" way to make a table *temporarily* READ-ONLY ? and if not, what is ?
what are the caveats of doing it this way ?

thanks !