Re: dba_constraints.validated

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Tue, 12 Jan 2016 13:10:57 +0600
Message-ID: <CALe4Hp=ihSDM41u6=5C-+BFnH+dr4pWUxYEU5m9aLigv0kVEWQ_at_mail.gmail.com>

Hi,

a 'novalidate rely' constraint should be sufficient for query rewrite,
> optimizer, etc.
>

RELY clause influences query rewrite, but not the optimizer, with accordance to the documentation:
https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52223

I doubt if the RELY has any effect on the optimizer besides the query rewrite.
Below is a little testcase to prove my points:

SQL> create table t(
2 id int constraint t_pk primary key, 3 pad char(10));

SQL>
SQL> exec dbms_stats.gather_table_stats( '', 't')
SQL>
SQL> alter session set query_rewrite_integrity=trusted

query_rewrite_enabled=true;
SQL>
SQL> select rely, validated, status
2 from user_constraints
3 where table_name='T';

RELY VALIDATED STATUS
------------ -------------- --------

             VALIDATED ENABLED
SQL>
SQL> explain plan for
  2 select /*+ index(t)*/
  3 count(*)
  4 from t;

Observed INDEX FULL SCAN with no TABLE ACCESS SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1477688419

|   0 | SELECT STATEMENT |      |     1 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   INDEX FULL SCAN| T_PK |     1 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------
SQL>
SQL> alter table t modify primary key rely enable novalidate;

SQL>
SQL> select rely, validated, status
2 from user_constraints
3 where table_name='T';

RELY VALIDATED STATUS
------------ -------------- --------
RELY NOT VALIDATED ENABLED
SQL>
SQL> explain plan for
  2 select /*+ index(t)*/
  3 count(*)
  4 from t;

Observed TABLE ACCESS FULL with no INDEX SCAN SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1842905362

|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------

A rely enabled not validated foreign key constraint not used for JOIN ELIMINATION also.

I faced with "NOT VALIDATED" primary key constraints in my environment in the past which were results of DBMS_REDEFINITION: Bug 13526773 - Constraint becomes NOT VALIDATED after table is redefined by DBMS_REDEFINITION (Doc ID 13526773.8) (
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=13526773.8 )
Please excuse me for the long post.

Best regards,
Mikhail.

2016-01-11 17:22 GMT+06:00 Franck Pachot <franck_at_pachot.net>:

> 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
>>
>

-- 
Best regards,
Mikhail Velikikh.

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

Received on Tue Jan 12 2016 - 08:10:57 CET

This message: [ Message body ]
Next message: Leroy Kemnitz: "Oracle App Express prod architecture"
Previous message: Thomas Roach: "Re: oracle on EC2"
Maybe in reply to: Ls Cheng: "dba_constraints.validated"
Next in thread: Franck Pachot: "Re: dba_constraints.validated"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message