Add primary key.

I thought I had written this note a few years ago, on OTN or Oracle-L if not on my blog, but I can’t find any sign of it so I’ve decided it’s time to write it (again) – starting as a question about the following code:

rem
rem     Script: pk_overhead.sql
rem     Author: J.P.Lewis
rem     Dated:  Feb 2012
rem

create table t1
as
with generator as (
        select  rownum  id
        from    dual
        connect by
                rownum <= 1000 --> comment to avoid WordPress format issue
)
select
        rownum                      id,
        trunc((rownum-1)/50)        clustered,
        mod(rownum,20000)           scattered,
        lpad(rownum,10)             vc_small,
        rpad('x',100,'x')           vc_padding
from
        generator       g1,
        generator       g2
;

execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')

alter system flush buffer_cache;

alter table t1 add constraint t1_pk primary key(id, scattered);

I’ve generated a table with 1,000,000 rows, including a column that’s going to hold unique values; then I’ve added a (two-column) primary key constraint to that table.

Because of the guaranteed unique column the call to add constraint will succeed. Because Oracle will automatically create a unique index to support that constraint it will have to do a tablescan of the table. So here’s the question:

HOW MANY TIMES will Oracle tablescan that table (and how many rows will it scan) ?

…

…

…

Space for thought …

…

…

…

The answer is three tablescans, 3 million rows.

Oracle will scan the table to check the validity of adding a NOT NULL definition and constraint for the id column, repeat the scan to do the same for the scattered column, then one final scan to accumulate the key data and rowids to sort and create the index.

Knowing this, you may be able to find ways to modify bulk data loading operations to minimise overheads.

Update – May 2016

The extra tablescans occur even if you have pre-existing check constraints (not declarations) on the columns to ensure that they are not null (i.e. things like: “alter table t1 add constraint t1_nn_id check (id is not null)”).

Update – Jan 2021

Test cases are wonderful: I’ve just re-run this one, and the effect is still the same in 19.3.0.0.  Adding the primary key required three tablescans of the table.  Then I repeated the test that tried to short-circuit some of the work a little by modifying the two columns to “not null” before adding the primary key; it still took two tablescans to modify the two columns.

Update (April 2024)

With a small upgrade (up to 19.11) the session statistics on the tablescans show an interesting change in how they are reported. There are three tablescans visible (in the row counts) during creation of the two-column primary key, but two of them are treated differently from (presumably) the first one:

table scan rows gotten                3,000,543
table scan disk non-IMC rows gotten   1,000,543

Although “IMC” hints at the use of the InMemory cache (rather than the boring old buffer cache) for 2M rows, the numbers reported for “session logical reads” and “consistent gets” still looked as if the table had been read 3 times from blocks in the buffer cache.

Nothing further has changed in 23.3