Re: Range partitions - 2 levels deep

Raj wrote:
>
> I need a table to be partitioned
> 1. First by range on a number field.
> 2. By date range.
>
> Anyone attempted this before. I was trying to use "List" for
> sub-partition. But it doesn't seem to like use of functions anywhere.
>
> This is what i am trying:
> CREATE TABLE raj1
>
> (salesman_id NUMBER(5),
> sales_date DATE)
> PARTITION BY LIST(to_char(sales_date,'MONYYYY'))
> ( PARTITION sales_jan03 VALUES('JAN2003'),
> PARTITION sales_feb03 VALUES ('FEB2003'),
> PARTITION sales_other VALUES(DEFAULT) );
>
> Has anyone tried this?
>
> Rajesh

Raj.

You if you are using a function you need it in the PARTITION VALUES clause.

PARTITION sales_jan03 VALUES(TO_DATE('JAN2003','MONYYYY')

You can subpartition a range partition by list, but that's not what your initial description 1 & 2 is asking for, that implies sub partitioning a range partition by range, which you can't do. Received on Fri Nov 07 2003 - 11:09:24 CST