| Partioning a Table, either List or Range without Hard coding the values [message #247538] |
Tue, 26 June 2007 05:13  |
ananth.dikshit
Messages: 20
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 #247558 is a reply to message #247538 ] |
Tue, 26 June 2007 06:47  |
Michel Cadot
Messages: 19107
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Use MOD(ulo) function on your month to split your data in your partitions. Modulo 3 is you have 3 partitions, 4 if you have 4 partitions...
Regards
Michel
|
|
|
| Re: Partioning a Table, either List or Range without Hard coding the values [message #247578 is a reply to message #247558 ] |
Tue, 26 June 2007 07:37 |
ananth.dikshit
Messages: 20
Registered: November 2006
Location: HYD |
Junior Member |
|
|
Hi Michel,
I am sorry but my day is kinda winding down and I am unable to grasp what you mean. Could you care to give an example?
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: 2721
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
|
|
|
| Re: Partioning a Table, either List or Range without Hard coding the values [message #247707 is a reply to message #247698 ] |
Tue, 26 June 2007 23:20 |
Michel Cadot
Messages: 19107
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: | 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.
|
This is exactly what I show in the example I posted. 
Regards
Michel
|
|
|
]
Members
Search
Help
Register
Login
Home
