Oracle Scratchpad

July 11, 2021

Quiz Night

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 11:41 pm BST Jul 11,2021

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.

9 Comments »

  1. 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 | Reply

  2. Yes I agree with Maxim. A primary key without explicit not null constraint will lead to this result, e.g.

    create table t1 (
       n1      number primary key,
       n2      number not null,
       v1      varchar2(10),
       padding varchar2(100)
    );
    
    

    To solve the problem you can add a not null constraint, e.g.

    SQL> create table t1 (
      2     n1      number not null primary key,
      3     n2      number not null,
      4     v1      varchar2(10),
      5     padding varchar2(100)
      6  );
    
    Table T1 created.
    
    SQL> 
    SQL> create table t2 as select * from t1;
    
    Table T2 created.
    
    SQL> 
    SQL> desc t1
    Name    Null?    Type          
    ------- -------- ------------- 
    N1      NOT NULL NUMBER        
    N2      NOT NULL NUMBER        
    V1               VARCHAR2(10)  
    PADDING          VARCHAR2(100) 
    SQL> 
    SQL> desc t2
    Name    Null?    Type          
    ------- -------- ------------- 
    N1      NOT NULL NUMBER        
    N2      NOT NULL NUMBER        
    V1               VARCHAR2(10)  
    PADDING          VARCHAR2(100)
    

    Comment by Philipp Salvisberg — July 12, 2021 @ 6:02 am BST Jul 12,2021 | Reply

    • 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 | Reply

  3. 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 | Reply

    • 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 | Reply

  4. 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:

    SQL> create table t(n1 number, n2 number, n3 number);
    
    Table created.
    
    SQL>
    SQL> insert into t values (1,2,3);
    
    1 row created.
    
    SQL> insert into t values ( 1, 4, 9);
    
    1 row created.
    
    SQL> insert into t values ( 1, 8, 27);
    
    1 row created.
    
    SQL>
    SQL> select * from t;
    
            N1         N2         N3
    ---------- ---------- ----------
             1          2          3
             1          4          9
             1          8         27
    
    SQL>
    SQL> alter table t modify  n2 invisible;
    
    Table altered.
    
    SQL>
    SQL> alter table t modify  n2 visible;
    
    Table altered.
    
    SQL>
    SQL> create table t_exch
      2  partition by range (n1)
      3  (partition p_max values less than (maxvalue))
      4  as select * from t
      5  where 1=2;
    
    Table created.
    
    SQL>
    SQL> alter table t_exch exchange partition p_max with table t;
    
    Table altered.
    
    SQL>
    SQL> select * from t_exch;
    
            N1         N3         N2
    ---------- ---------- ----------
             1          2          3
             1          4          9
             1          8         27
    
    

    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 | Reply

    • 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 | Reply

  5. 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 | Reply


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.

Website Powered by WordPress.com.

%d bloggers like this: