Skip to Main Content
  • Questions

  • Things checked with a constraint converted from "enable novalidate" to "enable validate"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 14, 2024 - 5:00 am UTC

Last updated: June 18, 2024 - 4:26 am UTC

Version: 19c Enterprise Edition Release 19.0.0.0

Viewed 1000+ times

You Asked

I have a huge partitioned table(about 3TB) that has a constraint(PK) in "enable novalidate" state.
I have to alter the state to "enable validate" state.

alter table TEST enable validate constraint PK_TEST;


The table belongs to the database(exa) in production mode.
I'm afraid the alter statement would take a long time and could affect the production line.

I tested the alter statement with a table(about 500GB) in development mode.
It took about 1~2 seconds, which is much faster than my expectation.

I'd like to know what is checked after executing the alter statement.
Also I'm wondering if the huge partitioned table(about 3TB) could be altered within a few seconds.

Thank in advance.


and Chris said...

The database still needs to check the data - with a novalidate constraint, there may be junk in there! If there are duplicate values, you'll get a constraint violation error.

If you're validating a primary key constraint, the database only needs to scan the primary key index. Not the table itself. This index is very likely much smaller than the table it's on.

Also note validating a novalidate constraint is a fully online operation. Regardless of how much data it scans or how long it takes, your apps can still read and write to the table while this command runs.

From the docs:

If you change the state of any single constraint from ENABLE NOVALIDATE to ENABLE VALIDATE, then the operation can be performed in parallel, and does not block reads, writes, or other DDL operations.

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE__I1010237

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Checking for null?

mathguy, June 17, 2024 - 5:07 pm UTC

If you're validating a primary key constraint, the database only needs to scan the primary key index.

That doesn't sound right - validation should also check for NULL in the pk column, and that can't be done without accessing the table. Right?
Connor McDonald
June 18, 2024 - 4:26 am UTC

That goes in the "that depends" bucket.

If the column is nullable, we are going to run effectively this as a check first

select A.rowid from MYTAB A where( "COL" is null)


which will read the table.

If the column is already not null, then we'll do something like

select A.rowid 
from MYTAB A, 
   (select "COL" from MYTAB A 
     where( "COL" is not null) 
    group by  "COL" having count(1) > 1) B 
where( "A"."COL" = "B"."COL")


which can be done with an index FFS

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.