DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Implement Table Partitioning sysdate wise

  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Implement Table Partitioning sysdate wise

    Hi,

    I Have a non partitioned table in which BLOB is used to store image data. Transaction date column is present. I have to convert it into partitioned table as per scenario which is mentioned below.

    how do i implement partitioning in which sysdate and subsequently day wise transactions are stored in partitions
    i have to keep only 4 days data. (For example Monday (Partition) p0, Tuesday p1,Wednesday p2,Thursday p3.Here i have to store Friday's transaction in Partition p0 by dropping partition p0 and recreating it(delete Monday's data). Also drop partition p1 (delete Tuesday's data), recreate it and store Saturday's data in partition p1 and this cycle continues..... except for the Sunday(Holiday).

    Shall i implement sysdate wise or day wise partition, bit confused? Your suggestions will be great help.

    Thanks
    Nyle.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Ideally you want to do Interval partitioning by day. Here is an example from the Oracle Documentation.

    http://docs.oracle.com/cd/E18283_01/...1.htm#BAJHFFBE

    Code:
    Example 4-18 Creating a composite interval-range partitioned table
    
    CREATE TABLE sales
      ( prod_id       NUMBER(6)
      , cust_id       NUMBER
      , time_id       DATE
      , channel_id    CHAR(1)
      , promo_id      NUMBER(6)
      , quantity_sold NUMBER(3)
      , amount_sold   NUMBER(10,2)
      )
     PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
     SUBPARTITION BY LIST (channel_id)
       SUBPARTITION TEMPLATE
       ( SUBPARTITION p_catalog VALUES ('C')
       , SUBPARTITION p_internet VALUES ('I')
       , SUBPARTITION p_partners VALUES ('P')
       , SUBPARTITION p_direct_sales VALUES ('S')
       , SUBPARTITION p_tele_sales VALUES ('T')
       )
     ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))
    PARALLEL;
    You can subpartition by something else, but you don't need to have a subpartition.

    Code:
    Example 4-18 Creating a composite interval-range partitioned table
    
    CREATE TABLE sales
      ( prod_id       NUMBER(6)
      , cust_id       NUMBER
      , time_id       DATE
      , channel_id    CHAR(1)
      , promo_id      NUMBER(6)
      , quantity_sold NUMBER(3)
      , amount_sold   NUMBER(10,2)
      )
     PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
     SUBPARTITION BY LIST (channel_id)
     ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))) PARALLEL;
    Then you just need a job to drop the oldest partition every day.

    Code:
    ALTER TABLE sales DROP PARTITION FOR(SYSDATE-3);
    However, if you have 10g or before, you need to manually create and drop partitions every day.
    If you have the space it might be easier to list partition by day of the week. Thus you would have
    partitions day_0 through day_6.

    Code:
    ALTER TABLE sales TRUNCATE PARTITION sales_day_0  UPDATE INDEXES;
    11g is definitely easier...

  3. #3
    Join Date
    Jan 2015
    Posts
    2
    Hi gandolf989

    Thankyou somuch for your help. I first test on UAT and if i found anything else good i will post so that others can also be benefited for similar scenarios.

    Thanks once again.

    Quote Originally Posted by Nyle View Post
    Hi,

    I Have a non partitioned table in which BLOB is used to store image data. Transaction date column is present. I have to convert it into partitioned table as per scenario which is mentioned below.

    how do i implement partitioning in which sysdate and subsequently day wise transactions are stored in partitions
    i have to keep only 4 days data. (For example Monday (Partition) p0, Tuesday p1,Wednesday p2,Thursday p3.Here i have to store Friday's transaction in Partition p0 by dropping partition p0 and recreating it(delete Monday's data). Also drop partition p1 (delete Tuesday's data), recreate it and store Saturday's data in partition p1 and this cycle continues..... except for the Sunday(Holiday).

    Shall i implement sysdate wise or day wise partition, bit confused? Your suggestions will be great help.

    Thanks
    Nyle.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width