Home » SQL & PL/SQL » SQL & PL/SQL » Enable novalidate constraint (Oracle 11g)
Enable novalidate constraint [message #661078] Mon, 06 March 2017 21:32 Go to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
Am having the table which are having composite primary key constraint with 4 columns which is in specified TABLESPACE.
Now i want to include one more column which is date column with some of the values are having NULL values.

I Searched how to implement this without checking the existing records and has to be applicable for new datasets.
i reached to know there is way using ENABLE NOVALIDATE CONSTRAINT.

My clarifications are

    My understanding on this is Quote:

    "ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be
    true for all rows. This allows existing rows to violate the constraint, while ensuring
    that all new or modified rows are valid."

    if so is there been any drop on existing indexes which we have given specifically to be on required TABLESPACE.
    if new dataset comes in with checking of constraint which TABLESAPCE the indexes may reside.

    Shall we redirect to point out to different TABLESPACE or SAME as existing is in use.?
    As this is partitioned table would this affect anyway?.
    if any index drop happened any possible SLOW processing on SELECT query on other batch for this TABLE?.

[Updated on: Tue, 07 March 2017 01:07] by Moderator

Report message to a moderator

Re: Enable novalidate constraint [message #661079 is a reply to message #661078] Mon, 06 March 2017 21:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
are you unwilling or incapable to simply TEST for yourself to see what works?
Re: Enable novalidate constraint [message #661081 is a reply to message #661079] Mon, 06 March 2017 22:50 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member


Its not unwilling or uncapable ... Environment has some issues which i cant access this currently.
As we are doing new project which this environment is also quite new and unstable on connectivity.

But i need to give some prompt information for this issue in our call urgently .

if am not uncapable i would have not raised it in this forum.

Please give some convenience answer by considering the situation of questioner would be in.

If i need to put all my situation before i ask any question please let me know

[Updated on: Tue, 07 March 2017 01:07] by Moderator

Report message to a moderator

Re: Enable novalidate constraint [message #661088 is a reply to message #661081] Tue, 07 March 2017 03:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you should have a test DB to test things on. If you don't have one, get one. Now.

The tablespace has nothing to do with this.
Partitioning has nothing to do with this.

If you drop an index that queries use and don't immediately replace it with another index those queries can use then some of those queries will slow down.

And if you use enable novalidate you better be 100% certain that non of the existing data violates the constraints or you will have problems.
Re: Enable novalidate constraint [message #661089 is a reply to message #661088] Tue, 07 March 2017 03:08 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Novalidate also affects the optimiser. It is ill advised.
Re: Enable novalidate constraint [message #661090 is a reply to message #661089] Tue, 07 March 2017 03:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I'm aware it's not the novalidate command that messes up the optimiser, it's the presence of rows that would fail the validation.
Re: Enable novalidate constraint [message #661091 is a reply to message #661090] Tue, 07 March 2017 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The NOVALIDATE implies the possibility to have "bad" rows and this is this (and not the fact these rows actually exist) that affects the optimizer (which does not check if these rows exist or not) unless RELY is also given to force the optimizer to take into account the constraint (although the documentation states RELY is only taken into account for query rewrite, tests have to be made...).

Re: Enable novalidate constraint [message #661092 is a reply to message #661091] Tue, 07 March 2017 03:27 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Yeah, it is table elimination by the optimizer which I know can be affected and have tested in the past to prove the effects.

Like Michel I've not tested extensively, merely enough to make sure things go back on validated but I'd wager something like an unvalidated not null constraint would prevent an index fast full scan or an "index only" type access of a table too.

Basically as far as the optimizer is concerned an unvalidated constraint may as well not be there, otherwise it would risk wrong results were it to assume it to be valid to consider.


Yes, rely exists, but I never tell anyone about it to be honest...trust issues Wink
Previous Topic: How to split col into two columns according to data types
Next Topic: Exchange Partition
Goto Forum:
  


Current Time: Thu Mar 28 07:42:23 CDT 2024