DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: partition by week day

  1. #1
    Join Date
    May 2000
    Location
    Alex, Egypt
    Posts
    41

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    Alex, Egypt
    Posts
    41
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width