Re: Creating List-Partitioned Tables

From: Matteo D'Amato <mat_damato_at_hotmail.com>
Date: 14 Jan 2005 20:48:21 -0600
Message-ID: <Xns95DEDE34BEB7Dmatdamatohotmailcom@216.40.28.70>

Hi,

            I'm trying to created a table with list partitioning. The idea is to have a 9 week rotation, hence w1..w9. So I thought I would convert billingdate to week of year. Oracle 10g complains about "PARTITION BY LIST ( to_char(billingdate,'WW') )". Do I need to use range instead? Can I do partition where values equal a week of year number? Thanks

--

Matteo D'Amato
Intelco Communications

CREATE TABLE test
(

   billinggroup                 NUMBER(4),
   billingtime                  VARCHAR2(10),
   billingdate                  DATE,
   ...
   ocn_in                       NUMBER(4),
   ocn_out                      NUMBER(4),

)
--
--

ORGANIZATION INDEX
INCLUDING billingdate
--
--

PARTITION BY LIST ( to_char(billingdate,'WW') ) (

   PARTITION w1 VALUES (1, 10, 19, 28, 37, 46) TABLESPACE rtbrs_main01,
   PARTITION w2 VALUES (2, 11, 20, 29, 38, 47) TABLESPACE rtbrs_main02,
   PARTITION w3 VALUES (3, 12, 21, 30, 39, 48) TABLESPACE rtbrs_main03,
   PARTITION w4 VALUES (4, 13, 22, 31, 40, 49) TABLESPACE rtbrs_main04,
   PARTITION w5 VALUES (5, 14, 23, 32, 41, 50) TABLESPACE rtbrs_main05,
   PARTITION w6 VALUES (6, 15, 24, 33, 42, 51) TABLESPACE rtbrs_main06,
   PARTITION w7 VALUES (7, 16, 25, 34, 43, 52) TABLESPACE rtbrs_main07,
   PARTITION w8 VALUES (8, 17, 26, 35, 44, 53) TABLESPACE rtbrs_main08,
   PARTITION w9 VALUES (9, 18, 27, 36, 45) TABLESPACE rtbrs_main09

); Received on Fri Jan 14 2005 - 20:48:21 CST