Table partitioning by year

[Former Member]

Can we partition a table by year basis ?

Below is my requirement

1. I have table xyz that contains data of 8 years.

2. I would like to do 8 partitions of that table (xyz) by Year basis. i.e first partition should contains only first year data and second partition should contains second year data ..so on ..

3. I believe the above should be possible(may be range partitioning). But, I am not able to find the syntax for doing the that.

If  it can be possible..can someone tell me the syntax for doing this.

Regards,

Pavan Gunda

[Former Member]

ALTER TABLE <SCHEMA>.<TABLE> PARTITION BY RANGE (YEAR) (PARTITION VALUE = '2016', PARTITION OTHERS);

ALTER TABLE <SCHEMA>.<TABLE> ADD PARTITION VALUES = '2015';

and so on.

[muthuram_shanmugavel2]

Hi Pavan,

Try this, It should work.

Input Table: PARTITION_RANGE_T

Partitioning Query:

Alter Table PARTITION_RANGE_T

PARTITION BY RANGE (YEAR(S_DATE))

(PARTITION VALUE = '2009',

PARTITION VALUE = '2010',

PARTITION VALUE = '2011',

PARTITION VALUE = '2012',

PARTITION VALUE = '2013',

PARTITION VALUE = '2014',

PARTITION VALUE = '2015',

PARTITION VALUE = '2016',

PARTITION OTHERS)

Partitioned Table Output:

[former_member1334]

Hi Muthu,

Can you help me with the query to get final "Partitioned Table Output"??

Regards,

Charles

[Former Member]

Hi Muthuram,

SAP DBTech JDBC: [435]: invalid expression: YEAR(S_DATE): line 2 col 26 (at pos 44) .I am getting syntax error

[muthuram_shanmugavel2]

Hi Pavan,

I executed the same query again. It executed successfully.

Please check the Datatype of Column S_DATE. It should be in DATE Datatype.

Execute this Queries as the same Order and let me know.

Table Creation:

Create column Table PARTITION_RANGE_T

(S_NUMBER Integer,

S_DATE DATE);

Data Loading to Table:

Insert into PARTITION_RANGE_T Values (1, '2009-01-01');

Insert into PARTITION_RANGE_T Values (2, '2010-01-01');

Insert into PARTITION_RANGE_T Values (3, '2011-01-01');

Insert into PARTITION_RANGE_T Values (4, '2012-01-01');

Insert into PARTITION_RANGE_T Values (5, '2013-01-01');

Insert into PARTITION_RANGE_T Values (6, '2014-01-01');

Insert into PARTITION_RANGE_T Values (7, '2015-01-01');

Insert into PARTITION_RANGE_T Values (8, '2016-01-01');

Partitioning the Table based on Year:

Alter Table PARTITION_RANGE_T

PARTITION BY RANGE (YEAR(S_DATE))

(PARTITION VALUE = '2009',

PARTITION VALUE = '2010',

PARTITION VALUE = '2011',

PARTITION VALUE = '2012',

PARTITION VALUE = '2013',

PARTITION VALUE = '2014',

PARTITION VALUE = '2015',

PARTITION VALUE = '2016',

PARTITION OTHERS)

--------------------------------------------------------------------

Best Regards,

Muthu

[muthuram_shanmugavel2]

Hi Charles,

Partitioned Table Output will be as same as before partitioning. The Data won't be modified. By Partitioning only the Data Storing allocation onlu will be changed.

Partitioned Table Output:

[former_member1334]

Hi Muthu,

Thanks for your reply.

I am referring to the output which you had got below is from "M_CS_PARTITIONS" table??

Could you clarify the same.

Partitioned Table Output:

Regards,

Charles

[muthuram_shanmugavel2]

Hi Charles,

OK Ok fine.

Before Partitioning, 8 rows of data ranging from 2009-2016 was there in the Table.

This Table is partitioned by YEAR Column using Range Partitioning.

Addition to this 8 YEAR Range with Other condition, 9 Parts will be created.

The table data will be grouped based on YEAR Value.

You notice "Number of Entries" column, Each row will have 1 row.

Now I insert this following two rows to this Partitioned Table:

Insert into PARTITION_RANGE_T Values (9, '2016-01-02');

Insert into PARTITION_RANGE_T Values (10, '2007-01-01');

We will check the How data is grouped in the Partitioning Table.

I hope it would be clear for you now.

[former_member1334]

Thanks Muthu for the clarification.