oracle11g - Oracle 11g Reference Partitioning and Indexes -

i have parent table containing following columns:
- parent_id: uuid
- event_date: timestamp
- data_column1: varchar2(255)
- data_column2: varchar2(255)

the table range partitioned event_date. data retained month , last partition dropped on daily basis.

following understanding, using global index pk result in sub-standard performance when dropping partition. means pk of table has based on both parent_id + event_date in order create local index.

i have second table child of first (via one-to-many relationship). has following columns:
- child_id: uuid
- parent_id: uuid - fk parent table
- data_column3: varchar2(255)
- data_column4: varchar2(255)

to partition child table, decided use reference partitioning. 1 of big advantages: removes need duplicate partition key in child table. however, based on reasoning, way achieve through global indexes. here train of thought:

1. for unique index of parent table local, pk must include partition key, e.g. event_date.
2. a foreign key constrain cannot reference part of pk. child table must include both parent_id , event_date columns.

what's more, read "when using reference partitioning, child table indexes should defined global, unless there compelling reason given index defined local." (http://www.nocoug.org/download/2010-05/zitelli-reference_partitioning_nocoug.pdf).

am missing or there no way use reference partitioning without having global indexes or duplicating data?
explanation on how reference partitioning works local/global indexes appreciated!

you understand correctly. if want create reference partition need define valid fk. in case - both parent_id , event_id needs present in child table.

ref partitions cases when want partition table according column not in pk not in child table. not case - can apply range partition on both tables , gain max pruning.

the event_date in child table not redundant - it's required model - need data_columns 3/4 in child table for each instance of parent_id + event_date.

regarding local indexes on child table in ref partitioned table - logic opposite. if have ref partition i'm aiming @ max pruning means want less partitions possible accessed in each query. in case want local indexes , not global.

you said "using global index pk result in sub-standard performance when dropping partition". when drop partition table global indexes invalidated , have rebuild them. performance impact regarding ddl changes. pk on partitioned table must global don't have choice here way.