-
Hello everybody,
Any help will be really appriciated.
Situation: A table XYZ stores data for each date(tT_DATE DATE) for one month. Each day appx 15000 rows are inserted into the table. This goes on till the end of month for a month. Then On 3rd of Next month, all data rows of previous month is deleted. Our reports and queries are based on each day (tt_date) (format DD-MON-YYYY) which takes a very very long as the data keeps on increasing each day. We want to partition table into 30 partitions based on the date field for each date(1-31). Giving the below commands gives errors due to Oracle requirement to provide the DATE format "DD-MM-YYYY". However if i give full format then i will have to specify month and year in hard-code manner. So next month will have to recreate partitions once again.
Requirement
Please tell me some way of partitioning the table into each date partition so that the same partition can be used next month without making any change in month,year.
create table XYZ
( ttDate DATE ,
IPlanId VARCHAR2(30),
ST VARCHAR2(30),
sch_arTime NUMBER(8),
sch_dpTime NUMBER(8),
act_arrDelay NUMBER(8),
act_depDelay NUMBER(8),
plan_arrTime NUMBER(8),
plan_depTime NUMBER(8),
curr_arrTime NUMBER(8),
curr_depTime NUMBER(8),
CONSTRAINT log_time_pk PRIMARY KEY(ttDate,IPlanId,ST))
partition by range (ttdate)
( PARTITION dt_1 VALUES LESS THAN (to_date('02','DD')),
PARTITION dt_2 VALUES LESS THAN (to_date('03','DD')),
PARTITION dt_3 VALUES LESS THAN (to_date('04','DD'))
.
.
.
.for all 30 days of month
);
/* I don't want to specify month or year in range partition */
/* The selection criteria in query uses DD-MMM-YYYY format */
--
Monika
-
The only solution that I see to your problem is to add a number column to your table which stores the day of the week and use this number column for partitionning. But I dont think that it make sense to do it like this.
I recommand you to try out an index organized table. It may also improve the performance.
Mike
