Howdy,

Just wondering what the limitations of having a partitioned table and a foreign key are in terms of the locking required to protect said foreign key.

If we have a table that's hash partitioned by primary key (with a local index for that primary key) are we looking to run into any locking issues when we set up an index on a foreign key that's GLOBALLY hash partitioned by the FK value? I assume not (but asking in case I assume wrong); but I'm more curious about the same question in the presence of a local index to protect the foreign key.

So for example (globally hash partitioned)...

create table parent (parent_pk_column number(38) primary key, junk_col varchar2(100));

create table child 
(
    child_pk_column   number(38) not null 
  , parent_pk_column  number(38) not null
  , junk_col          varchar2(100)
  , constraint child_pk primary key (child_pk_column) using index
  , constraint child_fk_001 foreign key (parent_pk_column) references parent(parent_pk_column) 
);

create index child_fk_idx_001 on child (parent_pk_column);

ALTER TABLE child   MODIFY
PARTITION BY hash (child_pk_column    )
(
  partition p01,partition p02,partition p03,partition p04,partition p05,partition p06,partition p07,partition p08,partition p09,partition p10,partition p11,partition p12,
  partition p13,partition p14,partition p15,partition p16,partition p17,partition p18,partition p19,partition p20,partition p21,partition p22,partition p23,partition p24,
  partition p25,partition p26,partition p27,partition p28,partition p29,partition p30,partition p31,partition p32,partition p33,partition p34,partition p35,partition p36,
  partition p37,partition p38,partition p39,partition p40,partition p41,partition p42,partition p43,partition p44,partition p45,partition p46,partition p47,partition p48,
  partition p49,partition p50,partition p51,partition p52,partition p53,partition p54,partition p55,partition p56,partition p57,partition p58,partition p59,partition p60,
  partition p61,partition p62,partition p63,partition p64
)
online
update indexes
(
  child_pk          local, 
  child_fk_idx_001  global partition by hash (parent_pk_column)  
                                  (
                                    partition p01,partition p02,partition p03,partition p04,partition p05,partition p06,partition p07,partition p08,partition p09,partition p10,partition p11,partition p12,
                                    partition p13,partition p14,partition p15,partition p16,partition p17,partition p18,partition p19,partition p20,partition p21,partition p22,partition p23,partition p24,
                                    partition p25,partition p26,partition p27,partition p28,partition p29,partition p30,partition p31,partition p32,partition p33,partition p34,partition p35,partition p36,
                                    partition p37,partition p38,partition p39,partition p40,partition p41,partition p42,partition p43,partition p44,partition p45,partition p46,partition p47,partition p48,
                                    partition p49,partition p50,partition p51,partition p52,partition p53,partition p54,partition p55,partition p56,partition p57,partition p58,partition p59,partition p60,
                                    partition p61,partition p62,partition p63,partition p64
                                  ) 
);

And with the local index protecting the foreign key.

drop table child purge;
drop table parent purge;

create table parent (parent_pk_column number(38) primary key, junk_col varchar2(100));

create table child 
(
    child_pk_column   number(38) not null 
  , parent_pk_column  number(38) not null
  , junk_col          varchar2(100)
  , constraint child_pk primary key (child_pk_column) using index
  , constraint child_fk_001 foreign key (parent_pk_column) references parent(parent_pk_column) 
);

create index child_fk_idx_001 on child (parent_pk_column);

ALTER TABLE child   MODIFY
PARTITION BY hash (child_pk_column    )
(
  partition p01,partition p02,partition p03,partition p04,partition p05,partition p06,partition p07,partition p08,partition p09,partition p10,partition p11,partition p12,
  partition p13,partition p14,partition p15,partition p16,partition p17,partition p18,partition p19,partition p20,partition p21,partition p22,partition p23,partition p24,
  partition p25,partition p26,partition p27,partition p28,partition p29,partition p30,partition p31,partition p32,partition p33,partition p34,partition p35,partition p36,
  partition p37,partition p38,partition p39,partition p40,partition p41,partition p42,partition p43,partition p44,partition p45,partition p46,partition p47,partition p48,
  partition p49,partition p50,partition p51,partition p52,partition p53,partition p54,partition p55,partition p56,partition p57,partition p58,partition p59,partition p60,
  partition p61,partition p62,partition p63,partition p64
)
online
update indexes
(
  child_pk          local, 
  child_fk_idx_001  local
);

Thanks a bunch!

SQL> create table parent (parent_pk_column number(38) primary key, junk_col varchar2(100));

Table created.

SQL>
SQL> create table child
  2  (
  3      child_pk_column   number(38) not null
  4    , parent_pk_column  number(38) not null
  5    , junk_col          varchar2(100)
  6    , constraint child_pk primary key (child_pk_column) using index
  7    , constraint child_fk_001 foreign key (parent_pk_column) references parent(parent_pk_column)
  8  ) partition by hash ( child_pk_column )
  9  (
 10    partition p01,partition p02,partition p03,partition p04
 11  )
 12  ;

Table created.

SQL>
SQL> --create index child_fk_idx_001 on child (parent_pk_column) local;
SQL>
SQL> insert into parent values (1,'x');

1 row created.

SQL> insert into parent values (2,'x');

1 row created.

SQL>
SQL> insert into child values (1,1,'x');

1 row created.

SQL> insert into child values (2,2,'x');

1 row created.

SQL>
SQL> commit;

Commit complete.