Re: dba_constraints.validated

  • From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
  • To: "mvelikikh@xxxxxxxxx" <mvelikikh@xxxxxxxxx>, "franck@xxxxxxxxxx" <franck@xxxxxxxxxx>
  • Date: Tue, 12 Jan 2016 11:01:55 +0000

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@xxxxxxxxx> 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@xxxxxxxxxx>:

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


On Fri, Jan 8, 2016 at 8:40 PM Adric Norris <landstander668@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxx> 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.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2016