Missing a child row
Brian Blades, April 15, 2016 - 4:26 pm UTC
I tried what you suggested with my own example, but for some reason I'm missing a child row.
create table parent
(
parent# number primary key,
data varchar2(3)
)
enable row movement
partition by range(parent#)
(partition p100 values less than (100),
partition p200 values less than (200),
partition p300 values less than (300),
partition p400 values less than (400),
partition pmax values less than (maxvalue)
)
/
insert into parent values(50,'ABC');
insert into parent values(150,'DEF');
insert into parent values(250,'GHI');
insert into parent values(350,'JKL');
insert into parent values(450,'JKL');
create table child
( parent# number not null,
child# number,
data varchar2(3),
constraint c1_pk
primary key(parent#,child#),
constraint c1_fk_p
foreign key(parent#)
references parent
on delete cascade
)
enable row movement
partition by reference(c1_fk_p)
/
insert into child values( 50,1,'A');
insert into child values(150,1,'A');
insert into child values(350,1,'A');
insert into child values(450,1,'A');
exec dbms_stats.gather_schema_stats('BBLADES');
col table_name format a10
col partition_name format a14
col partition_position format 9999 head "Partition|Position"
select table_name, partition_name, partition_position,
num_rows
from dba_tab_partitions
where table_owner='BBLADES'
and table_name in ('PARENT','CHILD')
order by table_name desc, partition_name;
Partition
TABLE_NAME PARTITION_NAME Position NUM_ROWS
---------- -------------- --------- ----------
PARENT P100 1 1
PARENT P200 2 1
PARENT P300 3 1
PARENT P400 4 1
PARENT PMAX 5 1
CHILD P100 1 1
CHILD P200 2 1
CHILD P300 3 0
CHILD P400 4 1
CHILD PMAX 5 1
select * from parent
order by parent#;
PARENT# DAT
---------- ---
50 ABC
150 DEF
250 GHI
350 JKL
450 JKL
select * from child
order by parent#, child#;
PARENT# CHILD# DAT
---------- ---------- ---
50 1 A
150 1 A
350 1 A
450 1 A
select c.*, u.subobject_name
from child c
join parent p
on c.parent# = p.parent#
join user_objects u
on dbms_rowid.rowid_object(p.rowid) = u.object_id
order by c.parent#;
PARENT# CHILD# DAT SUBOBJECT_NAME
---------- ---------- --- --------------
150 1 A P200
350 1 A P400
450 1 A PMAX
Where is parent# 50 ?
April 18, 2016 - 9:25 am UTC
As you identified, it should be the data_object_id.
Sorry for the confusion.
Chris
Missing row
Brian Blades, April 15, 2016 - 5:50 pm UTC
I think I see why the row is missing in my example.
The documentation for ROWID_OBJECT Function reads as follows:
This function returns the data object number for an extended ROWID.
The function returns zero if the input ROWID is a restricted ROWID.
In your example, you joined the value returned from rowid_object, which according to the documentation is the data object number (I assume they mean the DATA_OBJECT_ID), with the u.object_id.
select c.*, u.subobject_name
from t_chd c
join t_par p
on c.par_id = p.par_id
join user_objects u
on dbms_rowid.rowid_object(p.rowid) = u.object_id;
shouldn't it have been the u.data_object_id ?
April 16, 2016 - 8:02 am UTC
Just curious - *by default* the partition names align
SQL> create table t_par (
2 par_id int constraint 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')),
8 partition p4 values less than (to_date('01-jun-2016', 'dd-mon-yyyy'))
9 );
Table created.
SQL>
SQL> create table t_chd (
2 par_id int not null,
3 x int not null,
4 constraint fk foreign key (par_id) references t_par)
5 partition by reference (fk);
Table created.
SQL>
SQL> select table_name, partition_name
2 from user_tab_partitions
3 where table_name in ('T_PAR','T_CHD')
4 order by 1,2;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_CHD P1
T_CHD P2
T_CHD P3
T_CHD P4
T_PAR P1
T_PAR P2
T_PAR P3
T_PAR P4
Do you have things in your codebase that rename partitions to break that default ?
Cheers,
Connor
Partition Names the same
Brian Blades, April 18, 2016 - 1:04 pm UTC
Connor, you are correct. In may case, the "parent" table RANGE partitioned and the 2 "child" tables are REFERENCE partitioned to it. When I create new "future" partitions by doing a partition SPLIT of the parent, the child partitions get the same name. In the general case, that might not be true, and someone can always rename the partitions and break the correspondence. I'd prefer to find the corresponding partitions using a connection that cannot be broken as was proposed in the prior suggestion.
The only thing that I don't think was correct in the original solution was the value returned from the dbms_rowid.rowid_object function. The documentation (PL/SQL packages 11g) for this function says it returns the "data object number". I assume this refers to either the OBJECT_ID or DATA_OBJECT_ID column in the ALL_OBJECTS / USER_OBJECTS view. When I tried the original solution using the OBJECT_ID in my example, a row was missing. When I modified the code and assumed the DATA_OBJECT_ID was returned by the function, it worked correctly. This was the SQL I used.
ANSI SQL
select c.*, u.subobject_name
from child c
join parent p
on c.parent# = p.parent#
join user_objects u
on dbms_rowid.rowid_object(p.rowid) = u.data_object_id
order by c.parent#;
Oracle SQL
select up.object_name, up.subobject_name,
uc.object_name, uc.subobject_name
from child c,
user_objects uc,
parent p,
user_objects up
where rownum < 2
and dbms_rowid.rowid_object(c.rowid) = uc.data_object_id
and dbms_rowid.rowid_object(p.rowid) = up.data_object_id
and c.parent#=p.parent#
and up.subobject_name = 'P200';
To finish this subject, I would like to get a confirmation on which value is returned by the rowid_object function, the OBJECT_ID or the DATA_OBJECT_ID.
April 18, 2016 - 4:08 pm UTC
Ref partitioning
A reader, August 06, 2018 - 5:33 am UTC
I wonder if reference partitioning works within a rely novalidate fk ?
I have no computer right now to test it but just want to know if could works .
reference partition on composite partition table
Rajeshwaran, Jeyabal, August 05, 2022 - 7:14 am UTC
Team,
Is it possible to bring a composite partition technique from parent to child table using reference partition concepts?
in the below demo, T1 was composite partition got subpartitions there, but when T2 got reference partition implemented - all subpartitions from T1 got implemented as partitions in T2.
is it possible to get the same composite partition technique from T1 to T2 using reference partition concepts ?
demo@ATP19C> create table t1(x number primary key, y date )
2 partition by range(y)
3 subpartition by hash(x)
4 subpartition template(
5 subpartition sp1, subpartition sp2 )
6 ( partition p2020 values less than
7 ( to_date('01-jan-2020','dd-mon-yyyy')) ) ;
Table created.
demo@ATP19C> create table t2 (x number not null,
2 constraint t2_fk foreign key(x)
3 references t1 on delete cascade )
4 partition by reference(t2_fk);
Table created.
demo@ATP19C> col table_name for a10
demo@ATP19C> col partition_name for a10
demo@ATP19C> col subpartition_name for a10
demo@ATP19C> select table_name,composite,partition_name
2 from user_tab_partitions
3 where table_name ='T1';
TABLE_NAME COM PARTITION_
---------- --- ----------
T1 YES P2020
demo@ATP19C> select table_name,partition_name, subpartition_name
2 from user_tab_subpartitions
3 where table_name ='T1';
TABLE_NAME PARTITION_ SUBPARTITI
---------- ---------- ----------
T1 P2020 P2020_SP1
T1 P2020 P2020_SP2
demo@ATP19C> select table_name,composite,partition_name
2 from user_tab_partitions
3 where table_name ='T2';
TABLE_NAME COM PARTITION_
---------- --- ----------
T2 NO P2020_SP1
T2 NO P2020_SP2
demo@ATP19C> select table_name,partition_name, subpartition_name
2 from user_tab_subpartitions
3 where table_name ='T2';
no rows selected
demo@ATP19C>
August 10, 2022 - 3:01 am UTC
Replicated your result.
Asking around internally.
=======================
Got some info back from the feature owners.
Reference partitioning does not mean "a reference to the partitioning *strategy* of the parent table, but simply a reference to a keys to the partition that parent sits in. So seeing a reference partitioned child table being flattened is accurate and as-designed.
Having said that, there is some work underway to make the implementation a little clearer to avoid what people see as ambiguity.
reference partition on composite partition table
Rajeshwaran, Jeyabal, August 10, 2022 - 8:01 am UTC
Thanks for getting back with those details. Appreciate that. it helps.
August 11, 2022 - 4:30 am UTC
glad we could help