Oracle Scratchpad

March 2, 2012

Add Constraint

Filed under: Bugs,Infrastructure,Oracle — Jonathan Lewis @ 6:33 pm BST Mar 2,2012

Here’s a quirky little detail that may make you think carefully about how you define and load large tables.
I have a large table which I load with data and then apply the following:

rem
rem     Script:         add_constraints.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2003
rem

alter table t_15400 modify (id not null, small_vc not null);

Would you really expect to find Oracle doing two tablescans on the table to enable these constraints ? This is what I found in a trace file (with a lot of db file scattered read waits and other stuff in between) when I ran the test recently – and it still does it up to 12.1.0.2:

select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "SYS"."T_15400" A where( "ID" is null)
select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "SYS"."T_15400" A where( "SMALL_VC" is null)

It’s just a little difficult to come up with a good reason for this approach, rather than a single statement that validates both constaints at once.

Somewhere I think I’ve published a note that points out that when you add a primary key constraint Oracle first checks that the key column (or column set) is not null [update Jan 2016: couldn’t find it, so finally got around to writing it (again)]– which means that adding a primary key may also result in a tablescan for every column in the index before the index is created – but in that case you can’t see the SQL that checks each column, you have to infer the check from the number of tablescans and number of rows fetched by tablescan. The trace file is rather more helpful if all you’re doing is adding the not null constraints.

Update Aug 2018

I’ve just re-run the test on 18.3 and the same mechanism is used – N check constraints means N separate test which could mean N full tablescans.

Here’s a link to a little quiz I’d given in 2010 about adding not null check constraints quickly – depending on the table definition and its dependent objects you might have cases where the optimizer finds a better path for the check than a tablescan.

 

1 Comment »


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.