| Partioning a Table, either List or Range without Hard coding the values [message #247538] |
Tue, 26 June 2007 05:13  |
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
|
Junior Member |
|
|
Hi,
We have a Staging table for which we have a three month Archival policy, meaning we have to store data for the current month, current-1 month and current-2 month.
In the fourth month, current -2 data is deleted, current - 1 becomes current -2, current becomes current -1 and the fourth month becomes the current month.
Now I want to partition the table by month based on a Date column (TXN_DT) in that table.
Assume that the data in the table is for the month of Feb, March and April. I added another column PRTTN_BY_MTH to the table to store the months as number ( 2 for FEB, 3 for March, 4 for April)
The way I tried to partition the table is as below:
PARTITION BY RANGE (PRTTN_BY_MTH)
(PARTITION PRTTN_FIRST_MTH VALUES LESS THAN
(TRUNC(TO_CHAR(TRANSACTION_DT, 'mm'))),
PARTITION PRTTN_SECOND_MTH VALUES LESS THAN
(TRUNC(TO_CHAR(TRANSACTION_DT, 'mm'))+1),
PARTITION PRTTN_THIRD_MTH VALUES LESS THAN
(TRUNC(TO_CHAR(TRANSACTION_DT, 'mm'))+2),
PARTITION PRTTN_MTH VALUES LESS THAN (MAXVALUE));
I get an error on this ORA-14019. I know why I am getting this error but I see no way around this other than to Hard code the month values for the partitioning. If I do this what happens when the current -2 data has to be deleted and the next month data has to be inserted. Which partition will that go into.
Is there any way to partition this table on month, either LIST Partition or Range Partition without hard coding the month values.
Waiting eagerly for anyone to shed light on this.
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: Partioning a Table, either List or Range without Hard coding the values [message #247698 is a reply to message #247596] |
Tue, 26 June 2007 22:22  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You can only (list or range) partition on a constant value or a constant expression:
- 1 (constant)
- 1 + 1 (constant expression)
- to_date('30-sep-2005','DD-MON-YYYY') (constant expression)
You cannot partition on a column expression:
- EXTRACT(MONTH FROM col_name)
The closest you will be able to get is to denormalise the column expression as a real column in the table and then partition on that column.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
