Home » SQL & PL/SQL » SQL & PL/SQL » Partioning a Table, either List or Range without Hard coding the values
Partioning a Table, either List or Range without Hard coding the values [message #247538] Tue, 26 June 2007 05:13 Go to next message
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 #247558 is a reply to message #247538] Tue, 26 June 2007 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59811
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
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 #247596 is a reply to message #247578] Tue, 26 June 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59811
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at the following thread in DBA-Village: Best way for moving data to History Table.

Regards
Michel
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 Go to previous messageGo to next message
rleishman
Messages: 3701
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 Go to previous message
Michel Cadot
Messages: 59811
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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. Cool

Regards
Michel
Previous Topic: CLEARING TEMPORARY TABLE SPACES
Next Topic: Oracle query for date of the week
Goto Forum:
  


Current Time: Thu Nov 27 11:38:15 CST 2014

Total time taken to generate the page: 0.09856 seconds