table partitions in oracle 10g

[mdvreddy]

I have a table in which i have a date column. I need to do a partition due to many (billions) rows in it. I need to do year (including future years) wise first and then months wise in each year. Could any please guide me how to do this with commands and materials..
(least date is 01-01-1998 in the table).
thanks
/MR

[PAVB]

Have you even bothered to look at the syntax?

[mdvreddy]

yes, i checked but could not to do so..

SQL> CREATE TABLE EMP1
2 (EMPNO NUMBER,
3 HIREDATE DATA)
4 TABLESPACE USERS
5 PARTITION BY list(HIREDATE)
6 (PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (1998,1999,2000) TABLESPACE TS01,
7 PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (2001,2002,2003) TABLESPACE TS02,
8 PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (2004,2005,2006) TABLESPACE TS03,
9 PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (2007,2008,2009) TABLESPACE TS04,
10 PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (2010,2011,2012) TABLESPACE TS05
11 )
12 ENABLE ROW MOVEMENT;
(PARTITION TO_CHAR(HIREDATE,'YYYY') VALUES (1998,1999,2000) TABLESPACE TS01,
*
ERROR at line 6:
ORA-00926: missing VALUES keyword


actually i need to do subpartition level too accordingly monthly wise, it means each year will have 12 more subpartitions, do we have any method to avoid hardcode values in partition level to meet future needs??
please advice. thanks so much...

[PAVB]

1- Your syntax is wrong, that's why you are getting the error.

2- On 10g we cannot do it, you have to go to 11g for list/list or list/range partition/subpartitioning either way what you are looking for is to have 12 partitions per year so... what you don't create them in a "range" partitioning strategy instead of this non-sense list you are pursuing?

3- Yes, Oracle will create new partitions for you -read about "interval" partitioning.

[mdvreddy]

thanks for reply. my oracle is Enterprise Edition Release 10.1.0.4.0
I can choose any type of partitioning whichever is applicable for best performance as i am the one to do so.. or suggest me.

[PAVB]

You partition a table to help during either one or both of the items below...

a) Improve query performance.
b) Facilitate purge strategy

First question is, are you planning to partition that table for one -or better the two - of the above reasons?

If your answer is Yes, even not knowing your specific needs I would be inclined to do range partitioning by date(*), perhaps cutting one partition per month -- How may rows do you expect to store in each partition in such an scenario?

On the top of my head I do not remember if Ora10g 10.1 includes interval partitioning, either way we escaped from 10.1 as soon as we where able to do it.

(*) In my experience most of the time there is a "date" column that certainly helps on purge strategy and at least doesn't hurt query performance. If this is your case you might consider that column as your partition key.

[mdvreddy]

yes, exactly for the above 2 you mentioned.
Per months almost 10 million+ records (max at moment) and i want to do on timestamp(date?) column data type and will be partition key. i am little unclear about this concept as i am doing first time. thanks a lot for you clear description...

[mdvreddy]

Can anyone give me the SQL statement to achieve the one i explained??

[PAVB]

What do you have so far?

Here are two hints if you are planning to go for RANGE partitioning in a DATE datatype column:

1- Each partition has to be defined in a line like...

(PARTITION JUL2008 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE YOUR_TABLESPACE)

2- Since you are not in 11g you cannot rely on interval partitioning then do not forget to define a MAX VALUE partition.

[Telco_DBA]

thanks for reply. my oracle is Enterprise Edition Release 10.1.0.4.0
I can choose any type of partitioning whichever is applicable for best performance as i am the one to do so.. or suggest me.

Some time ago I summarized arguments for the partitioning-intervall

Daily, Weekly or Monthly Partitions?
http://www.mercury-consulting-ltd.co...artitions.html