how best to specify table constraints with "create table as <query>"

Kevin Wilkinson <w.kevin.wilkinson@xxxxxxxxx> · Fri, 1 Mar 2019 16:39:53 -0800



    i want to recluster an immutable table without locking the table and
    then add the table as a partition of a parent table. my plan was:

    create table tbl_cpy as select * from tbl order by c1
      asc;

      alter table tbl_cpy add constraint c1 not null, c1>=lo
      c1<hi;

      alter table tbl_parent attach partition tbl_cpy for values from
      (lo) to (hi);

    
    i want this to run quickly. so, i populate the table with a parallel
    query (using create table as ...). i do NOT want attach partition to
    rescan the table (which it does to validate the value range) so i
    have to explicitly add constraints to tbl_cpy. but adding these
    constraints itself causes a scan of the table.

    
    so, i want to specify the constraints when the table is created. but
    that seems not possible. if i first create an empty table with
    constraints, then i cannot populate it with a parallel query (since
    insert into is not parallel).

    
    am i missing something? any ideas?

    
    thanks,

    
    kevin