-
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.
-
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...
-
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.
Originally Posted by Nyle
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|