This discussion is archived
1 Reply Latest reply: Mar 8, 2012 6:18 AM by AndreasS. RSS

CTAS and reference partitioning

AndreasS. Newbie
Currently Being Moderated
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
    AndreasS. Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points