Re: estimate progress of constraint creation?

From: Adric Norris <landstander668_at_gmail.com>
Date: Tue, 24 Jan 2012 13:22:55 -0600
Message-ID: <CAJueESrkbA=oDDwFvs7BQkqp13MEpXBRNLtGU6SQjERQWffZCw_at_mail.gmail.com>

On Tue, Jan 24, 2012 at 11:35, Tim Gorman <tim_at_evdbt.com> wrote:
> How about ENABLE NOVALIDATE and then create queries to validate them
> yourself?
>

The data is already known to be good, so we're OK from that perspective. Unfortunately ENABLE NOVALIDATE doesn't behave, or at least *look*, the same. For example:

TEST_at_dwprod1> create table t (

  2     c1 number(4) not null,
  3     c2 number(4)

  4 );

Table created.

TEST_at_dwprod1> desc t

 Name                 Null?    Type
 -------------------- -------- --------------
 C1                   NOT NULL NUMBER(4)
 C2                            NUMBER(4)

Now we'll add a NOT NULL constraint to the second column in ENABLE NOVALIDATE mode.

TEST_at_dwprod1> alter table t
  2 modify (c2 not null enable novalidate);

Table altered.

TEST_at_dwprod1> desc t

 Name                 Null?    Type
 -------------------- -------- --------------
 C1                   NOT NULL NUMBER(4)
 C2                            NUMBER(4)

TEST_at_dwprod1> insert into t values (1, NULL); insert into t values (1, NULL)

                         *

ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."T"."C2")

The newly-added constraint is indeed enforced for new data... so far, so good. But wait:

TEST_at_dwprod1> desc t

 Name                 Null?    Type
 -------------------- -------- --------------
 C1                   NOT NULL NUMBER(4)
 C2                            NUMBER(4)

Hmmm, still showing as nullable... that's bound to cause confusion later on. Perhaps adding RELY into the mix will allow it to be handled normally?

TEST_at_dwprod1> select constraint_name from user_constraints

  2     where table_name = 'T'
  3       and validated = 'NOT VALIDATED';

CONSTRAINT_NAME

---

SYS_C00601485 TEST_at_dwprod1> alter table t
  2 modify constraint SYS_C00601485 rely; alter table t
*
ERROR at line 1:
ORA-25127: RELY not allowed in NOT NULL constraint

Unfortunately, this puts us back to simply needing to wait for the (very lengthy) normal constraint creation process to complete.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 24 2012 - 13:22:55 CST