Quiz Night

How do you explain the apparent inconsistency between the two outputs from this tiny fragment of an SQL*plus script (last tested 19.11.0.0):

describe t1
create table t2 as select * from t1;
describe t2

The results of the two describe commands are as follows (cut-n-paste, with no editing, including the feedback from the CTAS):

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                            NOT NULL NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(10)
 PADDING                                VARCHAR2(100)


Table created.

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                                     NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(10)
 PADDING                                VARCHAR2(100)

Answer, and comments on why it’s worth knowing, some time tomorrow (Monday)

Update (Monday morning)

.Well that didn’t take very long at all (31 minutes according to the timestamps). The effect is due to Oracle’s implementation of declared primary key constraints. A primary key means “unique and not null”, and when you add it Oracle flags the column as NOT NULL in the data dictionary (col$.null$ > 1) but doesn’t create a “not null” check constraint.

When you drop the primary key constraint the implicit “not null” is dropped; when you copy the table with a CTAS the implicit “not null” is not copied.

Sample code:

rem
rem     Script:         pk_overhead_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem             11.2.0.4
rem

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4    -- > comment to avoid WordPress format issue
)
select
        rownum                          n1,
        rownum                          n2,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1
;

alter table t1 add constraint t1_pk primary key(n1);
alter table t1 modify n2 not null;

set echo on

describe t1

create table t2 as select * from t1;
describe t2

alter table t1 drop primary key;
describe t1

set echo off

And the output, cut-n-paste:

SQL> describe t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                            NOT NULL NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

SQL> 
SQL> create table t2 as select * from t1;

Table created.

SQL> describe t2
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                                     NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

SQL> 
SQL> alter table t1 drop primary key;

Table altered.

SQL> describe t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                                     NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

SQL> 
SQL> set echo off

Why does it matter?

The reason why I wrote and posted this note is because of a recent request for comments on the Oracle-l mailing list about deleting half the data from a 222GB table with 900M rows. Since this is running on 11.2.0.4 none of the really nice options that appeared in 12.2 is available (and even then there might be time, space, or performance costs that would make the newer options unnacceptable).

One of the suggestions (probably the best option in the circumstances) was to use CTAS to create a partitioned table with one partition, selecting only the data that was needed from the original table, add the primary key and any indexes needed, then exchange the original table with the one partition.

The beneftis of this approach are:

• no massively expensive delete
• minimal undo and redo on the CTAS
• option for maximum parallel processing on the CTAS and any index creation
• no worries about making mistakes with access privileges on the table (thanks to the exchange “trick”)

And this is where the blog note comes in. The OP started asking questions about the cost of creating primary key. When you’ve got a table with 450M rows (half the original) of 111GB (half the original) you probably care about Oracle doing a full tablescan to add a NOT NULL constraint as step 1 of adding the primary key constraint. So you need to know how the primary key was created on the original table – was the column explicitly declared NOT NULL, or did its NOT NULL appear as a side effect of adding its primary key.

To be on the safe size, of course, you can write the CTAS to list the columns and explicitly include a NOT NULL on the primary key columns. Here’s one last fragment of my demo script:

create table t3(n1 not null, n2, v1, padding) 
as 
select * from t1
;

describe t3

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                            NOT NULL NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

There are several other details that would need to be addressed (and tested) before applying this mechanism e.g (but probably incomplete):

• any indexes you create on the partitioned table have to be local, and if that’s not possible you have to create them on the standalone table after the exchange;
• since it’s 11.2.0.4 there may be problems if the original table has had columns marked as unused but not dropped (and you don’t want to drop a couple of columns from a 900M row table), of if there were virtual columns, or function-based indexes with their hidden columns – you might need to create the partitioned table empty using the exact steps that got the original table to its current state otherwise you could run into the error “ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION”
• At this type of scale you probably want to be certain that all the operations take place at the maximum degree of parallelism that the machine (or your ration of the machine) can handle.
• Other … give me a little time and I’ll probably think of something.
• And here’s another – which appeared at comment 3 while I was typing in the update: CTAS loses any default declarations you’ve made for columns.

ORA-14097: Oracle addressed the problem of getting the right table definition in 12c with the “create table for exchange” option – but we’re taking advantage of an exchange “the wrong way round” so that wouldn’t help in this particular case.

Footnote

There are a couple of other posts on my blog that might also be relevant to the OP, one is about the effect of dropping a primary key constraint the other about the cost of adding a primary key to a table.