Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partition by month (NOT INCLUDING YEAR)

Re: Partition by month (NOT INCLUDING YEAR)

From: Binley Lim <Binley.Lim_at_ird.govt.nz>
Date: Wed, 11 Apr 2001 17:20:07 -0700
Message-ID: <F001.002E79ED.20010411162040@fatcity.com>

First of all, always post the code *and* the error message.

In range partitioning, the partitioning column must be of the same datatype as the partitioning value -- ie date. If you want to partition by month (number) then you need an additional column holding the month number only, and refer to that number in the values less than clause.

But this is probably a better candidate for hash partitioning.

>>> fred_fred_1_at_hotmail.com 04/12/01 04:40AM >>>
List,
  I am attempting to partition a table based on the month, not the month and the year. I want all January records to go into the January partition (even if the records are from January 1998,1999,2000, or 2001). Any January record from any year all goes into one partition, any February record from any year goes into the February partition, etc...)

I tried the following code, but no luck. Ideas?? Thanks! -Fred S.

CREATE TABLE MEETING_ROOM_USAGE (
SEQ_MEETING_ROOM_USAGE NUMBER(18) NOT NULL, POLL_ID NUMBER(10) NOT NULL,
PROPERTY_ID NUMBER(8) NOT NULL,
IP_ADDRESS VARCHAR2(15) NULL,
MAC_ADDRESS VARCHAR2(17) NULL,
ACCESS_CODE VARCHAR2(20) NULL,
CHARGE NUMBER(7,2) NULL,
DURATION NUMBER(9) NULL,
USAGE_DATE DATE NULL,
ROOM_NUMBER VARCHAR2(20) NULL,
COLLECTION_DATE DATE NULL,
CUSTOMER VARCHAR2(50) NULL,
USER_NAME VARCHAR2(50) NULL,
CONSTRAINT PK_MEETING_ROOM_USAGE
PRIMARY KEY (SEQ_MEETING_ROOM_USAGE))
PARTITION BY RANGE (USAGE_DATE)
(PARTITION JANUARY values less than (TO_CHAR('02','MM')) TABLESPACE MONTH1_USAGE_TS,
PARTITION FEBRUARY values less than (TO_CHAR('03','MM')) TABLESPACE MONTH2_USAGE_TS,
PARTITION MARCH values less than (TO_CHAR('04','MM')) TABLESPACE MONTH3_USAGE_TS,
PARTITION APRIL values less than (TO_CHAR('05','MM')) TABLESPACE MONTH4_USAGE_TS,
PARTITION MAY values less than (TO_CHAR('06','MM')) TABLESPACE MONTH5_USAGE_TS,
PARTITION JUNE values less than (TO_CHAR('07','MM')) TABLESPACE MONTH6_USAGE_TS,
PARTITION JULY values less than (TO_CHAR('08','MM')) TABLESPACE MONTH7_USAGE_TS,
PARTITION AUGUST values less than (TO_CHAR('09','MM')) TABLESPACE MONTH8_USAGE_TS,
PARTITION SEPTEMBER values less than (TO_CHAR('10','MM')) TABLESPACE MONTH9_USAGE_TS,
PARTITION OCTOBER values less than (TO_CHAR('11','MM')) TABLESPACE MONTH10_USAGE_TS,
PARTITION NOVEMBER values less than (TO_CHAR('12','MM')) TABLESPACE MONTH11_USAGE_TS,
PARTITION DECEMBER values less than (maxvalue)) /



Get your FREE download of MSN Explorer at http://explorer.msn.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Fred Smith
  INET: fred_fred_1_at_hotmail.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Binley Lim
  INET: Binley.Lim_at_ird.govt.nz

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Apr 11 2001 - 19:20:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US