-
partition by week day
I want to create table which holds the data for transactions of one week, every day the oldest partition will be truncated, and the data of the new day will be entered in this partition.
the problem is that I couldn't find a way to create a partition on "week day" or on a function of the transaction date.
thanks for your help
Amir Magdy
-
You could either create rolling partitions based directly on the transaction date and drop/create new partitions, or create a new (redundant) column to store to_char(transaction_date,'W'), using that as the partitioning key, and just truncate and reuse partitions.
-
Thanks for the reply
dropping and recreating the partition every day is not a good idea since it will be time consuming, and requires DBA intervention.
I just wanted to truncate the partition, and have the transactions of the new day to be inserted in this partition.
also adding a new column in the table is not possible, since we don't have the source code of the application, so we can't add this column in the insert statement, and we can't add a trigger in the table to populate this column since it has bulk inserts on it, (4-5 millions per day).
Amir Magdy
-
well what Slimdave suggested (second option) does not require drop and recreate partitions everyday
it´s just 7 partitions which stores day 1 to 7, reusing them, for example next monday will use this monday partitions.
if you cannot add a column how are you gonna use partitions...? what column are you going to use?
-
Originally posted by amir_magdy
dropping and recreating the partition every day is not a good idea since it will be time consuming, and requires DBA intervention.
Well, no it doesn't actually. You can have a scheduled job set up through DBMS_JOB to create new partitions and drop old ones automatically - any worthwhile DBA could do that, it ain't rocket science and the operation would not be time-consuming to run either.
You can also hedge your bets a little by creating the partitions a week in advance.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|