3 Replies Latest reply: Feb 4, 2013 10:30 PM by jeneesh RSS

    Suggestions for table partition for existing tables.

    Manjusha Muraleedas
      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...