Thanks for the question, Khudbudeen.

Asked: April 17, 2009 - 7:25 am UTC

Last updated: July 24, 2017 - 1:39 am UTC

Version: Oracle 10g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Hope you are doing great. My question is regarding truncating a Parent Child partitioned table.

1. I have a partition table say X which is a parent table.
2. I have a partition table say Y which is a child table whose parent is table X
3. I need to truncate both the parent and child partitioned tables through an SQL procedure using execute immediate statement.
4. Is it possible to truncate a partition table which has parent child relationship, if yes please advise me.

Thanks
Khutub

and Tom said...

In 11g - yes, you can with reference partitioning:

ops$tkyte%ORA11GR1> create table p
  2  (
  3    order#      number primary key,
  4    order_date  date,
  5    data       varchar2(30)
  6  )
  7  enable row movement
  8  PARTITION BY RANGE (order_date)
  9  (
 10    PARTITION part_2007 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 11    PARTITION part_2008 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR1> create table c1
  2  ( order#   number not null,   -- the NOT NULL is necessary, even though part of the
  3    line#    number,            -- key
  4    data     varchar2(30),
  5    constraint c1_pk primary key(order#,line#),
  6    constraint c1_fk_p foreign key(order#) references p
  7  )
  8  enable row movement
  9  partition by reference(c1_fk_p)
 10  /

Table created.

ops$tkyte%ORA11GR1> insert into p (order#, order_date, data)
  2  values ( 1, to_date('31-dec-2007'), 'order data' );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into c1 (order#, line#, data)
  2  values ( 1, 1, 'line data 1' );

1 row created.

ops$tkyte%ORA11GR1> insert into c1 (order#, line#, data)
  2  values ( 1, 2, 'line data 2' );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into p (order#, order_date, data)
  2  values ( 2, to_date('31-dec-2008'), 'order data' );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into c1 (order#, line#, data)
  2  values ( 2, 1, 'line data 1' );

1 row created.

ops$tkyte%ORA11GR1> insert into c1 (order#, line#, data)
  2  values ( 2, 2, 'line data 2' );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> alter table p truncate partition part_2007;
alter table p truncate partition part_2007
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


ops$tkyte%ORA11GR1> alter table c1 truncate partition part_2007;

Table truncated.

ops$tkyte%ORA11GR1> alter table p truncate partition part_2007;

Table truncated.

In 11g, we understand there is a relationship between the parent partition and the child partition

In 10g and before, there is no way to "express" that - we don't know that fact, if you did the above in 10g you would have to denormalize ORDER_DATE into the Child table - but we don't know that order date in the child is the same as order date in the parent.

In 10g, you'd have to disable the constraint, truncate, and then re-enable it.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Disable constraint on partition by reference

Prashant, November 10, 2011 - 5:06 am UTC

Hi Tom,

Is it possible to disable the foreign key constraint for partition by reference table?

I have a feeling that you are going to say "No" since the data is collocated for parent and child tables and the integrity must be needed for the same.

However for our data warehouse we haven't maintained any constraints and occasionally when we have decided to create constraints (for star transformations) we keep them disabled.

Please let me know if there is a way to achieve this for partition by reference tables?

November 10, 2011 - 1:31 pm UTC

We need that constraint for reference partitioning, it is mandatory.

You should have as many constraints as possible in your warehouse for performance. For good performance. You already know of one reason you want constraints - so we can do a star transformation. Well, believe it or not we use constraints for all sorts of query rewrites - including table elimination (we can remove entire tables from consideration IF the proper constraints are in place), partition view elimination via check constraints, use an index when we couldn't before with NOT null constraints and so on. You can put them in "disable rely novalidate" mode - but make sure your data actually conforms to the rules you say exist!

Here is a quick and dirty example of why constraints in the warehouse are sort of mandatory:

http://asktom.oracle.com/Misc/stuck-in-rut.html

But, if you want reference partitioning - we sort of need that constraint there. It is the only thing that makes it even possible to consider doing!

Thank you

Prashant, November 11, 2011 - 2:13 am UTC

Thank you Tom

I have tried it but it doesn't work

Mauricio García, July 20, 2017 - 6:20 pm UTC

Hello, I have the case but with many child tables referencing the parent table. All of them has 0 rows, but still I can't delete the parent table.
Parent table = T_INSTRUCCION
To see all the FK over my parent table I do the following.

select table_name, constraint_name, status
from all_constraints
where constraint_type = 'R'
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in ('P', 'U')
and table_name = 'T_INSTRUCCION'
)
order by table_name, constraint_name;

TABLE_NAME CONSTRAINT_NAME STATUS
----------------------------------------------------------
T_CANALES_PERMITIDOS T_CANALES_PERMITIDOS_FK1 ENABLED
T_INSTR_POR_ENVIAR T_INSTR_POR_ENVIAR_FK1 ENABLED
T_INSTRUCCION R_45 DISABLED
T_INSTRUCCION_DETALLE FKT_INSTRUCCION_DETALLE ENABLED
T_INSTRUCCION_TRACE T_INSTRUCCION_TRACE_FK1 ENABLED
T_INSTR_YA_ENVIADAS FKT_INSTR_YA_ENVIADAS ENABLED
T_NOMINA_IN_DETALLE_ANALIZADO FK1T_NOMINA_IN_DETALLE_AN ENABLED
T_NOMINA_OUT_DETALLE FK1T_NOMINA_OUT_DETALLE DISABLED
T_NOTIF_INDIV FK1T_NOTIF_INDIV ENABLED

All the enabled constraints are because they are partitioned by reference with my parent table (T_INSTRUCCION), so I can't disable the constraints.

I have tried the following.

alter table T_CANALES_PERMITIDOS truncate partition P_MIREP_201603;
alter table T_INSTR_POR_ENVIAR truncate partition P_MIREP_201603;
alter table T_INSTRUCCION_DETALLE truncate partition P_MIREP_201603;
alter table T_INSTRUCCION_TRACE truncate partition P_MIREP_201603;
alter table T_INSTR_YA_ENVIADAS truncate partition P_MIREP_201603;
alter table T_NOMINA_IN_DETALLE_ANALIZADO truncate partition P_MIREP_201603;
alter table T_NOTIF_INDIV truncate partition P_MIREP_201603;
alter table T_INSTRUCCION truncate partition P_MIREP_201603;

And it fails just in the last sentence wit the error:

alter table MIREP.T_INSTRUCCION truncate partition P_MIREP_201603
Informe de error -
ORA-02266: claves únicas/primarias en la tabla referidas por claves ajenas activadas
02266. 00000 - "unique/primary keys in table referenced by enabled foreign keys"
*Cause: An attempt was made to truncate a table with unique or
primary keys referenced by foreign keys enabled in another table.
Other operations not allowed are dropping/truncating a partition of a
partitioned table or an ALTER TABLE EXCHANGE PARTITION.
*Action: Before performing the above operations the table, disable the
foreign key constraints in other tables. You can see what
constraints are referencing a table by issuing the following
command:
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

What else can I do?

July 24, 2017 - 1:39 am UTC

They need to be defined as *reference* partitions. Not just foreign key references.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Disable constraint on partition by reference

Thank you

I have tried it but it doesn't work

More to Explore

VLDB

VLDB

Truncate - Parent Child Partitioned Tables

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Disable constraint on partition by reference

Thank you

I have tried it but it doesn't work

More to Explore

VLDB

VLDB