Partition By [message #446624] |
Tue, 09 March 2010 11:27  |
ramesh55.sse
Messages: 262 Registered: December 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
How can we partition the existed table.
The table is like this
CREATE TABLE my_table (
id NUMBER,
description VARCHAR2(50)
);
INSERT INTO my_table (id, description) VALUES (1, 'One');
INSERT INTO my_table (id, description) VALUES (2, 'Two');
INSERT INTO my_table (id, description) VALUES (3, 'Three');
INSERT INTO my_table (id, description) VALUES (4, 'Four');
INSERT INTO my_table (id, description) VALUES (4, 'Four');
INSERT INTO my_table (id, description) VALUES (5, 'Five');
INSERT INTO my_table (id, description) VALUES (5, 'Five');
INSERT INTO my_table (id, description) VALUES (6, 'Six');
INSERT INTO my_table (id, description) VALUES (6, 'Six');
COMMIT;
I want to partition this table into 3 partitions.
The first partition should contain the values less than 3.
The Second partition should contain the values less than 5.
The third partition should contain the values less than 6.
PLease help me
Thanks in advance.
|
|
|
|
Re: Partition By [message #446661 is a reply to message #446628] |
Tue, 09 March 2010 21:23   |
ramesh55.sse
Messages: 262 Registered: December 2008 Location: Hyderabad
|
Senior Member |
|
|
We can create one more partition using MAXVALUE.
My intention is to know how can we partition the existed table.
I think this is clear for your question.
|
|
|
|
|
Re: Partition By [message #446775 is a reply to message #446662] |
Wed, 10 March 2010 06:52   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
rahulvb wrote on Wed, 10 March 2010 14:41if numeric column is involved then HASH partition is preferred.
Huh? Did you just make that up?
You choose the partitioning strategy that suits your requirements, not your data type.
How would a HASH partition help if many of the queries had range predicates (<, >, BETWEEN) over the numeric column?
Ross Leishman
|
|
|
Re: Partition By [message #446789 is a reply to message #446775] |
Wed, 10 March 2010 08:04   |
 |
rahulvb
Messages: 924 Registered: October 2009 Location: Somewhere Near Equator.
|
Senior Member |
|
|
rleishman wrote on Wed, 10 March 2010 06:52
Huh? Did you just make that up?
Ross Have you done partition before ? can you please explain what is HASH partition , how HASH partition stores the value.
do you know how Range Scan works?
I think its time for you to go back to basics.
[Updated on: Wed, 10 March 2010 08:09] Report message to a moderator
|
|
|
Re: Partition By [message #446790 is a reply to message #446789] |
Wed, 10 March 2010 08:12   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Maybe YOU should read the basics:
Database Data Warehousing Guide
Chapter 5 Partitioning in Data Warehouses
Especially When to Use Hash Partitioning
Quote:The way Oracle Database distributes data in hash partitions does not correspond to a business or a logical view of the data, as it does in range partitioning. Consequently, hash partitioning is not an effective way to manage historical data. However, hash partitions share some performance characteristics with range partitions. For example, partition pruning is limited to equality predicates.
Regards
Michel
[Updated on: Wed, 10 March 2010 08:14] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: Partition By [message #446979 is a reply to message #446810] |
Thu, 11 March 2010 04:03  |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 10 March 2010 15:48ramesh55.sse wrote on Wed, 10 March 2010 16:08It's creating new table,but i want to partition the existed table.
Use dbms_redefinition.
Regards
Michel
I stand corrected (I've been away from the Oracle world for faaaaar too long). No excuses tho'
|
|
|