on 03-02-2016 11:12 AM
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
ALTER TABLE <SCHEMA>.<TABLE> PARTITION BY RANGE (YEAR) (PARTITION VALUE = '2016', PARTITION OTHERS);
ALTER TABLE <SCHEMA>.<TABLE> ADD PARTITION VALUES = '2015';
and so on.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
User | Count |
---|---|
95 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.