This discussion is archived
3 Replies Latest reply: Jun 24, 2008 2:56 PM by 629346 RSS

Partitioning by days of week

user629987 Newbie
Currently Being Moderated
Hi,

How would you partition a table with a DATE column (DD-MON-YYYY) by days of the week? Therefore, all data for monday in one partition, data for tuesday in another partition and so on.
Thanks!
  • 1. Re: Partitioning by days of week
    629346 Newbie
    Currently Being Moderated
    In Oracle 11g, there is now support for virtual column based partitioning. So you should be able to add a virtual column to your table and have that use the to_char function to extract the day of the week -

    day_of_week AS TO_CHAR(my_date,'DAY')

    Then, you should be able to just use range or list partitioning on this virtual column to create your day of the week partitions. The ILM Assistant only supports partitioning by DATE columns, so this partitioning scheme would not work with the Assistant.


    Adam
  • 2. Re: Partitioning by days of week
    user629987 Newbie
    Currently Being Moderated
    Hi,
    Thanks for your reply.
    Unfortunately, it's an Oracle database version 10g, not 11g.

    I was wondering if it's possible to have many values when partitioning by range, for example....

    Partition by range (date)
    ( Partition monday Values ('02-JUN-2008', '09-JUN-2008', '16-JUN-2008') Tablespace t1,
    Partition tuesday Values ('03-JUN-2008', '10-JUN-2008', '17-JUN-2008') Tablespace t2,
    .........);

    Is this possible? Thanks.
  • 3. Re: Partitioning by days of week
    629346 Newbie
    Currently Being Moderated
    Hi,

    I am not aware of any such capabilities. You could partition by LIST, but that would require you to specify every valid date in the partitioning clause for each partition. That may be feasible if you have only have a short time period or could write a script to generate the clauses.


    Adam