Thanks for the question, Amit.

Asked: May 19, 2016 - 10:58 am UTC

Last updated: June 28, 2017 - 12:56 am UTC

Version: 11.2.0.4.0 - 64bit

Viewed 1000+ times

You Asked

We want to delete the records from “child tables (for e.g. ‘child1', ‘child2’, etc…)” before start deleting the records from parent table in a specific partition we are interested in for e.g. 'P_COMPLETED_20160519'. Child tables are partitioned using "PARTITION BY REFERENCE".

How can we find the list of partition name for child table for the given partition ‘P_COMPLETED_20160519’ (with high value 20160519) in parent table. We know the partition name of parent table.

ALTER TABLE child1 truncate partition partition_name; -- how to get partition_name ?
ALTER TABLE child2 truncate partition partition_name; -- how to get partition_name ?
ALTER TABLE child2 truncate partition P_COMPLETED_20160519;

regards, Amit J.

and Chris said...

By default child reference partitions have the same name as the parent. Someone could rename them though, so it's always worth double checking.

The query joining to user_objects below will find you this information:

create table par (
  par_id    int constraint pk primary key,
  ins_date  date not null)
partition by range (ins_date) (
  partition p1 values less than (to_date('01-mar-2016', 'dd-mon-yyyy')),
  partition p2 values less than (to_date('01-apr-2016', 'dd-mon-yyyy')),
  partition p3 values less than (to_date('01-may-2016', 'dd-mon-yyyy')));

create table chd (
  par_id int not null,
  x      int not null,
  constraint fk foreign key (par_id) references par
) partition by reference (fk);

insert into par
  select rownum, add_months(date'2016-01-01', rownum) from dual
  connect by level <= 3;
 
insert into chd
  select rownum, rownum from dual
  connect by level <= 3;
 
select c.*, u.subobject_name partition_name
from   chd c
join   par partition (p3) p
on     c.par_id = p.par_id
join   user_objects u
on     dbms_rowid.rowid_object(c.rowid) = u.data_object_id;

    PAR_ID          X PARTITION_NAME               
---------- ---------- ------------------------------
         3          3 P3 

alter table chd rename partition p3 to pnew;

select c.*, u.subobject_name partition_name
from   chd c
join   par partition (p3) p
on     c.par_id = p.par_id
join   user_objects u
on     dbms_rowid.rowid_object(c.rowid) = u.data_object_id;

    PAR_ID          X PARTITION_NAME               
---------- ---------- ------------------------------
         3          3 PNEW

Rating

(4 ratings)

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

Comments

Truncate records from Child Partitioned Tables

Amit Jain, May 19, 2016 - 4:30 pm UTC

Thanks for replying so quickly. Given solution works for us.

Its solved the immediate problem we have. Is there any way we can improve this query because mother table will be having at least 11 million records. Will join make it performance issue?

May 20, 2016 - 2:50 am UTC

Can't you just join on PARTITION_POSITION ?

Because if you

a) drop a parent partition, the child goes as well
b) you cannot drop a child partition in isolation


SQL>
SQL>
SQL> create table par (
  2    par_id    int constraint par_pk primary key,
  3    ins_date  date not null)
  4  partition by range (ins_date) (
  5    partition p1 values less than (to_date('01-mar-2016', 'dd-mon-yyyy')),
  6    partition p2 values less than (to_date('01-apr-2016', 'dd-mon-yyyy')),
  7    partition p3 values less than (to_date('01-may-2016', 'dd-mon-yyyy')));

Table created.

SQL>
SQL> create table chd (
  2    par_id int not null,
  3    x      int not null,
  4    constraint chd_fk foreign key (par_id) references par
  5  ) partition by reference (chd_fk);

Table created.

SQL>
SQL> insert into par
  2    select rownum, add_months(date'2016-01-01', rownum) from dual
  3    connect by level <= 3;

3 rows created.

SQL>
SQL> insert into chd
  2    select rownum, rownum from dual
  3    connect by level <= 3;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select partition_position, partition_name
  2  from user_tab_partitions
  3  where table_name = 'PAR';

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
                 1 P1
                 2 P2
                 3 P3

3 rows selected.

SQL>
SQL> select partition_position, partition_name
  2  from user_tab_partitions
  3  where table_name = 'CHD';

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
                 1 P1
                 2 P2
                 3 P3

3 rows selected.

SQL>
SQL> alter table CHD drop partition p2;
alter table CHD drop partition p2
                               *
ERROR at line 1:
ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method


SQL> alter table PAR drop partition p2;

Table altered.

SQL>
SQL> select partition_position, partition_name
  2  from user_tab_partitions
  3  where table_name = 'PAR';

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
                 1 P1
                 2 P3

2 rows selected.

SQL>
SQL> select partition_position, partition_name
  2  from user_tab_partitions
  3  where table_name = 'CHD';

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
                 1 P1
                 2 P3

2 rows selected.

SQL>
SQL>
SQL>

Cheers,
Connor

Truncate records from Child Partitioned Tables

Amit Jain, May 20, 2016 - 6:48 am UTC

We can't "use alter table PAR drop partition p1;" because our parent table have a self referential key:
CREATE TABLE PAR
(PAR_ID VARCHAR2(255 CHAR) NOT NULL, PARENT_ID VARCHAR2(255 CHAR), CLOSED_REQ_STAMP NUMBER(8,0) DEFAULT 0
)...
;
ALTER TABLE PAR ADD CONSTRAINT PAR_PID_FK FOREIGN KEY (PARENT_ID) REFERENCES PAR (PAR_ID);

May 20, 2016 - 7:26 am UTC

I wasn't suggesting you do so, I was simply saying that even when dropped, it would appear the partition_position values for parent and child will align, so if you know the parent partition name, then look up the partition_position for that name, and use that to query the child partition.

Cheers,
Connor

Truncate records from Child Partitioned Tables

Amit Jain, May 20, 2016 - 8:50 am UTC

Thank you very much for quick reply...
In parent table we are using composite partitions:range + hash (16), so data in the child table are distributed by the hash algorithm. For one high_value in parent we are having 16 partitions in child table.
select partition_position from user_tab_partitions where table_name = 'PAR' and partition_name = 'P_COMPLETED_20160512';
I am getting only one record but in child there must be 16 partition for that partition in parent. Can't relate them.

Thanks again for your help !!

May 20, 2016 - 9:24 am UTC

In the child table you have one partition per subpartition in the parent. So to find the names in the child, you need the names of the parent subpartitions.

Assuming no-one's renamed them, you can find them from user_tab_subpartitions:

create table t (
  id int primary key,
  dt date,
  x  int
) partition by range ( dt )
  interval ( numtodsinterval(1, 'day') )
  subpartition by hash (
    x
  ) subpartitions 4 (
    partition p_first_part values less than (
      to_date ( '16-DEC-2015','DD-MON-YYYY' )
    )
  ) ;

create table chd (
  par_id int not null,
  x      int not null,
  constraint fk foreign key (par_id) references t (id)
) partition by reference (fk);

insert into t
  select rownum, sysdate, rownum from dual
  connect by level <= 10;

insert into chd
  select id, rownum from t;

commit;

select c.*, cu.subobject_name chd_partition_name, pu.subobject_name par_partition_name
from   chd c
join   t p
on     c.par_id = p.id
join   user_objects cu
on     dbms_rowid.rowid_object(c.rowid) = cu.data_object_id
join   user_objects pu
on     dbms_rowid.rowid_object(p.rowid) = pu.data_object_id;

    PAR_ID          X CHD_PARTITION_NAME   PAR_PARTITION_NAME 
---------- ---------- -------------------- --------------------
         7          7 SYS_SUBP15327        SYS_SUBP15327       
         4          4 SYS_SUBP15327        SYS_SUBP15327       
         3          3 SYS_SUBP15327        SYS_SUBP15327       
         1          1 SYS_SUBP15327        SYS_SUBP15327       
         8          8 SYS_SUBP15326        SYS_SUBP15326       
         5          5 SYS_SUBP15326        SYS_SUBP15326       
         2          2 SYS_SUBP15326        SYS_SUBP15326       
        10         10 SYS_SUBP15325        SYS_SUBP15325       
         9          9 SYS_SUBP15325        SYS_SUBP15325       
         6          6 SYS_SUBP15324        SYS_SUBP15324

select subpartition_name from user_tab_subpartitions
where  table_name = 'T';

SUBPARTITION_NAME                                                                                                              
----------------------------
SYS_SUBP15327                                                                                                                   
SYS_SUBP15326                                                                                                                   
SYS_SUBP15325                                                                                                                   
SYS_SUBP15324                                                                                                                   
SYS_SUBP15323                                                                                                                   
SYS_SUBP15322                                                                                                                   
SYS_SUBP15321                                                                                                                   
SYS_SUBP15320

Time taken for PMO_DEFERRED_GIDX_MAINT_JOB

heather zhang, June 27, 2017 - 8:40 pm UTC

Chris,
I have noticed PMO_DEFERRED_GIDX_MAINT_JOB took no time to complete after partitions w millions of rows got dropped. can you explain the rationale behind it? I imagine it would take quite sometime, o/w online global index maintenance would be sufficient.

thanks
Heather

June 28, 2017 - 12:56 am UTC

If you only had local indexes, then there would no work to do.

With no other details provided, we can't really comment much further.

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 Chris said...

Rating

Comments

Truncate records from Child Partitioned Tables

Truncate records from Child Partitioned Tables

Truncate records from Child Partitioned Tables

Time taken for PMO_DEFERRED_GIDX_MAINT_JOB

More to Explore

VLDB

VLDB

Find name of reference partitioned child tables

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Truncate records from Child Partitioned Tables

Truncate records from Child Partitioned Tables

Truncate records from Child Partitioned Tables

Time taken for PMO_DEFERRED_GIDX_MAINT_JOB

More to Explore

VLDB

VLDB