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 one to a table Oracle flags the column as NOT NULL in the data dictionary (col$.null$ > 1) but does not create a “not null” check constraint.
When you drop the primary key constraint the implicit NOT NULL declaration is dropped; but when you copy the table with a CTAS the implicit NOT NULL declaration 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.
I think, one of possible explanations – the table t1 has a primary key constraint on n1 column (e.g. alter table t1 add constraint t1_pk primary key(n1) – at least, tested on 11.2.0.4 )
Regards
Maxim
Comment by Maxim Demenko — July 12, 2021 @ 12:14 am BST Jul 12,2021 |
Maxim,
That didn’t take very long at all.
Thanks for the comment – that’s exactly what I did.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — July 12, 2021 @ 9:20 am BST Jul 12,2021 |
Yes I agree with Maxim. A primary key without explicit not null constraint will lead to this result, e.g.
To solve the problem you can add a not null constraint, e.g.
Comment by Philipp Salvisberg — July 12, 2021 @ 6:02 am BST Jul 12,2021 |
Philipp,
Thanks for the comment – which shows a variation on the PK declaration mechanism that also results in the disappearance of the NOT NULL in t2.
And thanks for demonstrating a possible strategy for avoiding the “accident”.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — July 12, 2021 @ 9:25 am BST Jul 12,2021 |
Hi,
In addition to what has been said (not quite related), with CTAS, we also lose the DEFAULT value of a column.
Comment by Abdellah DANY — July 12, 2021 @ 10:14 am BST Jul 12,2021 |
Abdellah,
Thanks for the comment, that’s a most timely warning.
I’ve added this to the bullet list of “other things to think about”.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — July 12, 2021 @ 10:30 am BST Jul 12,2021 |
Another one issue with CTAS may arise, if original table has some column(s) made invisible and then visible again (which reorders in some sense the columns in the data dictionary, however not in the data blocks) and then used with partition exchange:
As shown, the data is effectively swapped between n2 and n3 columns (wrong data issue), or – if the columns are not of same data type, this would lead to partition exchange failure with ORA-14097
As for bulk deletion of rows – if the deletion filter doesn’t depend on another tables (like in semijoin, antijoin etc.) , then in my opinion is suggested by Sayan (on oracle-l) “move table with row filtering”
Regards
Maxim
Comment by Maxim Demenko — July 12, 2021 @ 11:00 am BST Jul 12,2021 |
Maxim,
Thanks for highlighting another threat.
The problem with Sayan’s suggestion is that the OP is on 11.2.0.4, and “move including rows …” didn’t appear until 12.2.
However, my advice to the OP was: don’t do it until you’ve upgraded to at least 12.2 and then use this feature if you want to stay online during the process.
Regards
Jonathan Lewis
UPDATE: I’ve just realised that since the OP is using 11.2.0.4 the option for making the column invisible is available; it didn’t appear until 12c.
Comment by Jonathan Lewis — July 12, 2021 @ 11:32 am BST Jul 12,2021 |
create table t1 (n1 number primary key,
n2 number not null,
v1 varchar2(10),
padding varchar2(100));
desc t1
create table t2 as select * from t1;
desc t2
select * from user_constraints;
— Primary key constraints are not copied with CTAS
Comment by Octavio Mayor-González — July 12, 2021 @ 9:38 pm BST Jul 12,2021 |
[…] Quiz night 35 (July 2021) – the disappearing NOT NULL declaration – a critical detail about primary key constraints, with links to notes about adding and dropping primary (or unique) keys. […]
Pingback by Quiz Catalogue | Oracle Scratchpad — February 1, 2022 @ 12:20 pm GMT Feb 1,2022 |
[…] Disappearing NOT NULL constraints (July 2021): the effects of the link between primary keys and not null declarations […]
Pingback by Troubleshooting catalogue | Oracle Scratchpad — February 1, 2022 @ 12:23 pm GMT Feb 1,2022 |