Q

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?

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"?
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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close