Re: dba_constraints.validated

constraint validation is not immediate (novalidate tooks 0.002 seconds and validate took 5 seconds in my test for 9 million of test data rows)

the PK has unique index

Thanks

On Mon, Jan 11, 2016 at 12:22 PM, Franck Pachot <franck_at_pachot.net> wrote:

> Hi,
> As the PK is enabled, you have a index on it. If it's a unique index, then
> validate constraint should be immediate, so maintenance window can be
> short.
> If it's not unique, that may be more difficult in 11g (in 12c you can
> create additional unique and set the constraint to use it).
> Anyway, do you have any reason to want it 'VALIDATED'? a 'novalidate rely'
> constraint should be sufficient for query rewrite, optimizer, etc.
> Regards,
> Franck.
>
> Franck Pachot | Senior Consultant & Oracle Technology Leader | Oracle
> Certified Master and Oracle ACE
> franck.pachot_at_dbi-services.com
>
>
> On Fri, Jan 8, 2016 at 8:40 PM Adric Norris <landstander668_at_gmail.com>
> wrote:
>
>> It's not nearly as bad as it sounds. Constraint validation, assuming that
>> the constraint is already enabled (i.e. enforced for new/updated data), is
>> actually a read-only operation which doesn't block DML. I've done this
>> *lots* of time on some very active 11.2.0.x database tables, when we had
>> to make structural changes in an incremental manner in order to avoid
>> downtime.
>>
>>
>> On Thu, Jan 7, 2016 at 2:12 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
>>
>>> Hi Stephan
>>>
>>> I tried that command but it seems to validate all rows, that means I
>>> need a maintenance window to run the DDL, something I want to avoid. I have
>>> looked everywhere and I guess I will have to leave it in NOT VALIDATED
>>> state...
>>>
>>> Thanks!
>>>
>>>
>>> On Thu, Jan 7, 2016 at 7:45 PM, Stefan Knecht <knecht.stefan_at_gmail.com>
>>> wrote:
>>>
>>>> Alter table can do that:
>>>>
>>>> SQL> create table t (x int, constraint t_pk primary key (x) enable
>>>> novalidate);
>>>>
>>>> Table created.
>>>>
>>>> SQL> select constraint_name, status, validated from user_constraints
>>>> where constraint_name='T_PK';
>>>>
>>>> CONSTRAINT_NAME
>>>>
>>>> --------------------------------------------------------------------------------
>>>> STATUS VALIDATED
>>>> -------- -------------
>>>> T_PK
>>>> ENABLED NOT VALIDATED
>>>>
>>>>
>>>> SQL> alter table t modify constraint t_pk enable validate;
>>>>
>>>> Table altered.
>>>>
>>>> SQL> select constraint_name, status, validated from user_constraints
>>>> where constraint_name='T_PK';
>>>>
>>>> CONSTRAINT_NAME
>>>>
>>>> --------------------------------------------------------------------------------
>>>> STATUS VALIDATED
>>>> -------- -------------
>>>> T_PK
>>>> ENABLED VALIDATED
>>>>
>>>> If there are any rows violating the constraint, the alter table will
>>>> fail.
>>>>
>>>> Stefan
>>>>
>>>>
>>>>
>>>> On Fri, Jan 8, 2016 at 1:37 AM, Ls Cheng <exriscer_at_gmail.com> wrote:
>>>>
>>>>> Hi Rich
>>>>>
>>>>> The table was bulk-reloaded a year ago and the FKs were enabled using
>>>>> novalidate option during a maintenance window to speed up the process but
>>>>> after enable novalidate the state of VALIDATED stayed as "NOT VALIDATED". I
>>>>> simply wonder if there is any DDL command to change it to VALIDATED.
>>>>>
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>> On Thu, Jan 7, 2016 at 4:22 PM, Rich J <
>>>>> rjoralist3_at_society.servebeer.com> wrote:
>>>>>
>>>>>> On 2016/01/07 06:56, Ls Cheng wrote:
>>>>>>
>>>>>> I have some tables whose FK in dba_constraints.validated appears as
>>>>>> "NOT VALIDATED". I guess it's because it was once enabled using novalidate
>>>>>> clause.
>>>>>>
>>>>>> Is it possible to "change" this to VALIDATED without validating the
>>>>>> constraints?
>>>>>>
>>>>>>
>>>>>> I'm struggling as to why anyone would want to do this (outside of
>>>>>> academic curiosity) or why any DB engine would ever allow marking an FK as
>>>>>> validated when it hasn't been.
>>>>>>
>>>>>> It seems that one could trace a validation to generate the SQL to
>>>>>> hack the dictionary into doing this, but I hopefully don't need to explain
>>>>>> why that's a terrible idea.
>>>>>>
>>>>>> Just my $.02,
>>>>>> Rich
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>>
>> --
>> "In the beginning the Universe was created. This has made a lot of people
>> very angry and been widely regarded as a bad move." -Douglas Adams
>>
>

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