Re: dba_constraints.validated
Date: Tue, 12 Jan 2016 19:36:37 +0000
Message-ID: <CAK6ito2LyMJ8LTWiw6Frwo035LGucAG1W=37Qvwhp3fqHBk4Ng_at_mail.gmail.com>
Hi Dominic,
There are a few bugs (4615392,9153459,17651484) which can explain the that join elimination doesn't work.
Hi Mikhail,
You example is not Join Elimination. It's the NOT NULL associated with the primary key that makes the access to the table unneeded. I don't think it is a transformation, thus the different behavior. The fact that the above bugs are recognized as bugs mean that rely constraint are expected to be considered by CDO (despites what doc says), but for some transformations only. But you're right, RELY do not bring the same behavior as validated constraints.
Regards,
Franck.
On Tue, Jan 12, 2016 at 12:02 PM Dominic Brooks <dombrooks_at_hotmail.com> wrote:
> Looks like a change in join elimination behaviour from 11.2.0.3 at least.
>
> There is a test case in article below which shows a constraint with state
> RELY DISABLE NOVALIDATE being used for join elimination:
>
> https://orastory.wordpress.com/2014/12/05/rely-trumps-disable/
>
> Just done a quick test on 12.1.0.2 and the join elimination does not
> happen and an eliminate_join hint was not effective.
>
> Dominic
>
>
> On 12 January 2016, at 07:12, Mikhail Velikikh <mvelikikh_at_gmail.com>
> wrote:
>
> 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
>
> -----------------------------------------------------------------
> | Id | Operation | Name | Rows | Cost (%CPU)| Time |
> -----------------------------------------------------------------
> | 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
>
> -------------------------------------------------------------------
> | Id | Operation | Name | Rows | Cost (%CPU)| Time |
> -------------------------------------------------------------------
> | 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-lReceived on Tue Jan 12 2016 - 20:36:37 CET