Re: Constraints on views - disable novalidate

"Peter" <peter_at_nomorenewspamin.ca> wrote in message news:smvtqvcc1k59bi7udlces98ba7urqha71a_at_4ax.com...
> When creating a constraint on views, why is it necessary to include
> the "disable nonvalidate" keyword?

Because a view is merely a stored select statement, and thus contains no actual data of its own. Therefore, there's nothing to actually validate. The idea of constraints on views is merely to 'push' constraints that exist on tables (and which would be enabled and validated, thus doing the real check of the actual data) to a level where the optimiser can see that they exist, and thus take them into account. But they are intended merely to duplicate, or replicate, the real table constraints. That is, they are merely declarative in intent: they state "this constraint exists", but they have no data of their own against which to actually validate anything.

> What is the difference between
> "enable novalidate" and "disable validate"?

Enabled novalidate means that new data arriving at the table is checked for conformance with the constraint, but the existing contents of the table are in an unknown state of conformance. There could be duplicating records all over the place, and we'd never know about it. Why run in such a state? Because to enable validate the constraint, you have to check all the existing records in the table for conformance... and on a big table, that will take a long time, during the entirety of which, the table is locked from further DML. So if you disable a constraint to do a bit of maintenance, it may not be practical to completely re-enable it when you're done. But you don't want your users to pollute the table with bad data, so you check the new arrivals and leave the final check of table data to an off-peak time.

Disabled validate means the constraint is fully off ("disabled"), but we make sure that no violating records can get into the table nevertheless by the simple expedient of locking the table from all DML. It's a bit of an unusual one to use, but consider this: you can't drop an index if it's used to enforce a constraint. But an invalidated index can't just be rebuilt. It has to be dropped and then re-created from scratch. So if you really, really need to rebuild the index, you would disable the constraint, and thus be able to drop the index prior to rebuilding. But as soon as you disable the constraint, the table is wide open for violating records to get in (and this is the situation we were in before 8i). But with disabled validate, the constraint is disabled... so you can drop your index... but you know the table will remain "clean" throughout, because the 'validate' bit means that Users can't actually do any DML on the table at all. You use this odd 'off but not quite' state for constaints a lot when you do partitioning, because partition DDL has a habit of invalidating large swathes of associated indexes, which therefore need to be dropped.

> With the "enable novalidate" constraint, Oracle will not validate
> existing data but will validate future data. How about changing data
> of existing rows?

The data is not checked.

>
> When you create a table with the "disable validate" constraint, does
> that mean you cannot add any new rows to the table?
> I cannot see why as Oracle will still validate the existing data, but
> no new data will be validated.

See above. Remember that before 8i, this state didn't exist... but that wasn't necessarily a problem, because if you dropped an index prior to it being rebuilt, the rebuilding itself would lock the table from all DML. But in 8i, they introduced the "online rebuild", and therefore they had to find a constraint enforcement mechanism to plug the gap of opportunity they'd opened for bad data to get into the table.

>
> Does Oracle validate existing data in the table on every DML
> statement?

You've lost me. Does it validate existing data in the table on every DML statement when it is enabled validate? No. Only the rows being affected are checked. Otherwise there'd be a full table scan every time you tried updating something... it wouldn't be pretty.

Regards
HJR
>
> Thanks
>
Received on Mon Nov 10 2003 - 05:42:14 CST