Home » SQL & PL/SQL » SQL & PL/SQL » Partitioning & Truncating a Table based on date in Oracle 10 g
Partitioning & Truncating a Table based on date in Oracle 10 g [message #324239] Sat, 31 May 2008 19:24 Go to next message
raghu1040
Messages: 4
Registered: May 2008
Location: PHILY
Junior Member
Hi There,
I have a requiremnet like: To Partition a table and truncate a partition if it is more than 45 days old, based on dates like 1,2,3...30,31 in a month.
This is because, i have more than 25 million records per day. So if i delete them, by identifying on date which is 45 days before using sysdate-45,
its will take lot of time. So i want to partition based on individual day and truncate that partition.
I have data since 2004, for years like 2004, 2005, 2006, 2007, 2008. So what ever the logic i implemenmt should be useful for coming years also like
2009, 2010 and so on.

So Pls suggest me solution on this.

Thanks in Advance,
Raghu.
Re: Partitioning & Truncating a Table based on date in Oracle 10 g [message #324240 is a reply to message #324239] Sat, 31 May 2008 20:16 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>So i want to partition based on individual day and truncate that partition.
Nobody here is preventing you from implementing as stated above.
Please proceed to do so.
Re: Partitioning & Truncating a Table based on date in Oracle 10 g [message #324241 is a reply to message #324240] Sat, 31 May 2008 21:03 Go to previous messageGo to next message
raghu1040
Messages: 4
Registered: May 2008
Location: PHILY
Junior Member
Hi,
Thanks for your response...

For example: 45 th day from todays date is Apr-16-2008. I am not getting what are the partitions to be done. I guess i should have 46 partitions in a table.when 46 th days data comes to my table, 45 th days previous data to be truncated. So pls help me out like.... what partion type to be implemented and how many number of partitions to be done.... i am not getting the logic to implement...
Thanks,
raghu.
Re: Partitioning & Truncating a Table based on date in Oracle 10 g [message #324242 is a reply to message #324239] Sat, 31 May 2008 21:21 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>i am not getting the logic to implement...
Then perhaps partitions should not be used by you.
Deleting 25 million rows a day, it not so large a task.


http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above.

The SQL Reference Manual can be found at http://tahiti.oracle.com

many fine coding examples can be found at http://asktom.oracle.com

We (TINW) don't do people's work for them.
If you need somebody to complete this task for you, then hire a consultant.

Post what you've tried & explain why the results were not correct.

[Updated on: Sat, 31 May 2008 21:23] by Moderator

Report message to a moderator

Re: Partitioning & Truncating a Table based on date in Oracle 10 g [message #324248 is a reply to message #324239] Sun, 01 June 2008 00:43 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at the following link:
http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=24757

Regards
Michel
Previous Topic: In Oracle Report I need to Get the Date
Next Topic: create XML file from an Oracle Table
Goto Forum:
  


Current Time: Wed Dec 07 20:40:19 CST 2016

Total time taken to generate the page: 0.16797 seconds