Oracle Database Discussions

Hi All,

I am on v 11.2.0.3.
I have a pair of typical parent-child tables. Child table is growing like hell, hence we want to partition it, which will be used for deleting/dropping old data later on.
There is no partitioning key in the child table which I can use for relating the data to the time when data was created. So, I thought I can use the timestamp from parent table for partitioning the parent table and reference partition the child table.
I am more concerned about the child table (or the queries running on the child table) in terms of performance. ITEM_LIST_ID from the child table is extensively used in queries to access data from child table.
How will partition pruning work when the child table is queried on the foreign key? will it every time go to the parent table, find out the partition and then resolve the partition for child table?
The setup is given in the scripts below, will it cause lot of locking (to resolve partitions)? or am I worrying for nothing?

Here are the scripts

CREATE TABLE ITEM_LISTS /* Parent table, tens of thousands of records */
(
  ITEM_LIST_ID    NUMBER(10)     NOT NULL PRIMARY KEY, /* Global index on partitioned table !!! */
  LIST_NAME       VARCHAR2(500)  NOT NULL,
  FIRST_INSERTED  TIMESTAMP(6)   NOT NULL
)
PARTITION BY RANGE ( FIRST_INSERTED )
(
  partition p0 values less than ( to_date('20130101','YYYYMMDD') ),
  partition p201301 values less than ( to_date('20130201','YYYYMMDD') ),
  partition p201302 values less than ( to_date('20130301','YYYYMMDD') ),
  partition p201303 values less than ( to_date('20130401','YYYYMMDD') ),
  partition p201304 values less than ( to_date('20130501','YYYYMMDD') ),
  partition p201305 values less than ( to_date('20130601','YYYYMMDD') )
);

CREATE INDEX ITEM_LISTS_IDX1 ON ITEM_LISTS ( LIST_NAME ) LOCAL ;

CREATE TABLE ITEM_LIST_DETAILS /* Child table, millions of records */
(
  ITEM_ID        NUMBER(10)     NOT NULL,
  ITEM_LIST_ID   NUMBER(10)     NOT NULL, /* Always used in WHERE clause by lots of big queries */
  CODE           VARCHAR2(30)   NOT NULL,
  ALT_CODE       VARCHAR2(30)   NOT NULL,
  CONSTRAINT   ITEM_LIST_DETAILS_FK
  FOREIGN KEY  ( ITEM_LIST_ID ) REFERENCES ITEM_LISTS
)
PARTITION BY REFERENCE ( ITEM_LIST_DETAILS_FK )
;

CREATE INDEX ITEM_LIST_DETAILS_IDX1 ON ITEM_LIST_DETAILS (ITEM_ID) LOCAL;
CREATE INDEX ITEM_LIST_DETAILS_IDX2 ON ITEM_LIST_DETAILS (ITEM_LIST_ID, CODE) LOCAL;

Any thoughts / opinions / corrections ?

Thanks in advance