Enable novalidate constraint [message #661078] |
Mon, 06 March 2017 21:32 |
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 #661081 is a reply to message #661079] |
Mon, 06 March 2017 22:50 |
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 |
cookiemonster
Messages: 13943 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 #661092 is a reply to message #661091] |
Tue, 07 March 2017 03:27 |
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
|
|
|