This discussion is archived
3 Replies Latest reply: Feb 4, 2013 8:30 PM by jeneesh RSS

Suggestions for table partition for existing tables.

Manjusha Muraleedas Newbie
Currently Being Moderated
I have a table as below. This table contains huge data. This table has so many child tables .I am planning to do a 'Reference Partitioning' for the same.


 create table PROMOTION_DTL
(
  PROMO_ID              NUMBER(10) not null,
  EVENT                 VARCHAR2(6),
  PROMO_START_DATE      TIMESTAMP(6),
  PROMO_END_DATE        TIMESTAMP(6),
  PROMO_COST_START_DATE TIMESTAMP(6),
  EVENT_CUT_OFF_DATE    TIMESTAMP(6),
  REMARKS               VARCHAR2(75),
  CREATE_BY             VARCHAR2(50),
  CREATE_DATE           TIMESTAMP(6),
  UPDATE_BY             VARCHAR2(50),
  UPDATE_DATE           TIMESTAMP(6)
)

alter table PROMOTION_DTL
  add constraint PROMOTION_DTL_PK primary key (PROMO_ID);
alter table PROMOTION_DTL
  add constraint PROMO_EVENT_FK foreign key (EVENT)
  references SP_PROMO_EVENT_MST (EVENT);
-- Create/Recreate indexes 
create index PROMOTION_IDX1 on PROMOTION_DTL (PROMO_ID, EVENT)
 ;
create unique index PROMOTION_PK on PROMOTION_DTL (PROMO_ID)
;
-- Grant/Revoke object privileges 
grant select, insert, update, delete on PROMOTION_DTL to SCHEMA_RW_ROLE;
I would like to partition this table .Most of the queries contains the following conditions.
promo_end_date >=   SYSDATE
and 
(event = :input_event OR
(:input_Start_Date <= promo_end_date            
AND promo_start_date <= :input_End_Date))
Any time the promotion can be closed by updating the PROMO_END_DATE.

Interval partioning on PROMO_END_DATE is not possible as PROMO_END_DATE is a nullable and updatable field.

I am now to table partition.

Any suggestions are welcome...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points