Ask the Expert

Creating a partition table based on day of the week

I need to create a partition table based on the day of the week. Do I use list partition for this? Where do I get the full syntax diagram for the partition clause? Do I have to use one of the columns in the table as a partitioning column? Instead can I use, say, "sysdate"?

    Requires Free Membership to View

If you are only holding a week's worth of data in the table, then you can certainly partition by the day of the week, one day in one of the seven partitions. List partitioning can definitely be used here, if you want. Let's assume that I have a table similar to the following:
ID   VAL   DATE_ENTERED
---  ----  ------------
1    100   01/01/05
2    110   01/02/05
3    110   01/03/05
4    111   01/05/05
If I wanted to do list partitioning on the day of the week, I'd add another column to the table as follows:
ID   VAL   DATE_ENTERED WEEKDAY
---  ----  ------------ -------
1    100   01/01/05      SAT
2    110   01/02/05      SUN
3    110   01/03/05      MON
4    111   01/03/05      MON
The WEEKDAY column can be populated with a trigger, using the contents of the DATE_ENTERED column to determine the WEEKDAY column's value.

To create this table as a list partitioned table, you can use a command similar to the following:

CREATE TABLE my_table (
   id NUMBER,
   val NUMBER,
   date_entered DATE,
   weekday CHAR(3))
PARTITION BY LIST (weekday) (
   PARTITION my_table_sun VALUES ('SUN'),
   PARTITION my_table_mon VALUES ('MON'),
   PARTITION my_table_tue VALUES ('TUE'),
   PARTITION my_table_wed VALUES ('WED'),
   PARTITION my_table_thu VALUES ('THU'),
   PARTITION my_table_fri VALUES ('FRI'),
   PARTITION my_table_sat VALUES ('SAT'));

This was first published in October 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: