1 Reply Latest reply: Mar 8, 2012 8:18 AM by Andreas S. RSS

    CTAS and reference partitioning

    Andreas S.
      Hi folks,

      I have to partition two big tables (parent_unpart and child_unpart).

      The first table parent is partitioned by range and subpartitioned by hash.
      Here it is no problem to create a new partitioned table using "create table as select" (CTAS) for the parent-table.

      So i can create and import in one step.


      My child table should use reference partitioning by the foreign key. I would try to use CTAS again, but oracle gives me
      ORA-00942.

      The foreign key constraint shows

      Here is my statement:

      CREATE TABLE child_partitioned
      (
      child_parent_id
      , child_data
      -- Constraint für Foreign Key
      , CONSTRAINT C_FK_CHILD_PARENT_ID FOREIGN KEY
      (
      child_parent_id
      )
      REFERENCES parent
      (
      parent_id
      )
      ON DELETE CASCADE ENABLE
      )
      PARTITION BY REFERENCE (C_FK_CHILD_PARENT_ID)
      as select * from child;

      The foreign-key-constraints points at the primary key of the parent_table.
      Parent key and foreign key are not null.

      Doing it in 2 steps (1. create table and 2. insert as select)
      Do you have any ideas?

      I would like to test if CTAS is using less undo space than inserting the data on 2nd step.
      Thanks,
      Andreas
        • 1. Re: CTAS and reference partitioning
          Andreas S.
          Ah, I found it.

          by eleminating the column type i also dropped the not null constraint which does not come by the as select.

          here's the correct statement:

          CREATE TABLE child_partitioned
          (
          child_parent_id NOT NULL
          , child_data
          -- Constraint für Foreign Key
          , CONSTRAINT C_FK_CHILD_PARENT_ID FOREIGN KEY
          (
          child_parent_id
          )
          REFERENCES parent
          (
          parent_id
          )
          ON DELETE CASCADE ENABLE
          )
          PARTITION BY REFERENCE (C_FK_CHILD_PARENT_ID)
          as select * from child;


          Thanks,
          Andreas