Re: Creating List-Partitioned Tables

In article <Xns95E2875C4507Ematdamatohotmailcom_at_216.40.28.74>, Matteo D'Amato says...
>
>Hi Morgan,
> I tried it but I get
>
>SQL> @test2.sql
>PARTITION BY RANGE (TO_CHAR(billingdate,'ww'))
> *
>ERROR at line 122:
>ORA-00907: missing right parenthesis
>
>
>Any other ideas?

partition by range, hash, list all take COLUMNS, not function(COLUMN), just column.

you would need to add a column to this table -- "billingdate_week" and maintain this value either by inserting to_char(:billingdate,'ww') as you load the data or by maintain via a trigger.

You can then partition on this "real" column.

but since your users will probably never query on to_char(billingdate,'ww'), you won't be getting any partition elimination or anything out of this -- so you might just consider rangeing on BILLING date and setting up a procedure that adds "next weeks" partition and removes the "oldest partition"

That is, don't partition by f(date), just partition by DATE and slide the partitions along.

>
>--Matteo
>
>
>
>DA Morgan <damorgan_at_x.washington.edu> wrote in
>news:41e8924e$1_2_at_127.0.0.1:
>
>> Matteo D'Amato wrote:
>>
>>> 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
>>
>> Use RANGE.
>>
>

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation