Has anyone created table as select (CTAS) which is also reference partitioned.
Here's example I tried to code
create table parent_emp
partition by list(job)
( partition p_job_dba values ('DBA'),
partition p_job_mgr values ('MGR'),
partition p_job_vp values ('VP')
)
as select
empno ,
job ,
sal ,
deptno
from emp;
alter table parent_emp add
constraint emp_pk primary key (empno);
create table reference_emp
(
constraint fk_empno foreign key(empno)
references parent_emp(empno)
)
partition by reference (fk_empno)
as
select
ename ,
empno emp_id ,
empno
from emp;
ORA-00904: : invalid identifier
I am able to create the table if I remove the constraint which is required for reference partitioning and the partitioning clause of course.
i.e. this works ...
create table reference_emp
select
ename ,
empno emp_id ,
empno
from emp;
Also I am able to create the table with out CTAS
i.e. this works
create table reference_emp
(
ename varchar2(10),
emp_id number primary key,
empno not null,
constraint fk_empno foreign key(empno)
references parent_emp(empno)
)
partition by reference (fk_empno)
/
But the I am left with slow insert into select from the source instead of CTAS.
I think it is the constraint that is causing the issue in the syntax which is mandatory for reference partition.