Home » SQL & PL/SQL » SQL & PL/SQL » Partition By (Oracle 9i)
Partition By [message #446624] Tue, 09 March 2010 11:27 Go to next message
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 #446628 is a reply to message #446624] Tue, 09 March 2010 12:45 Go to previous messageGo to next message
John Watson
Messages: 6575
Registered: January 2010
Location: Global Village
Senior Member
Where do you intend to place rows with values equal to 6?
Re: Partition By [message #446661 is a reply to message #446628] Tue, 09 March 2010 21:23 Go to previous messageGo to next message
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 #446662 is a reply to message #446624] Tue, 09 March 2010 21:41 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
if numeric column is involved then HASH partition is preferred.


CREATE TABLE my_table (
        id NUMBER,
        description VARCHAR2(50)
                       )
   PARTITION BY HASH (id)
   PARTITIONS 4
   STORE IN (users, users, users, users);


Check folloing Link Partition

Re: Partition By [message #446680 is a reply to message #446624] Tue, 09 March 2010 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2093664

Regards
Michel
Re: Partition By [message #446775 is a reply to message #446662] Wed, 10 March 2010 06:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rahulvb wrote on Wed, 10 March 2010 14:41
if 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 Go to previous messageGo to next message
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.


./fa/456/0/

[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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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 #446797 is a reply to message #446789] Wed, 10 March 2010 08:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rahulvb wrote on Wed, 10 March 2010 15:04
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.


./fa/456/0/

ROFL

woooooooshhhh
Re: Partition By [message #446799 is a reply to message #446662] Wed, 10 March 2010 09:08 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
It's creating new table,but i want to partition the existed table.
Re: Partition By [message #446800 is a reply to message #446789] Wed, 10 March 2010 09:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rahulvb wrote on Wed, 10 March 2010 14:04

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.
./fa/456/0/


Anybody for the Dunning-Kruger effect?
Re: Partition By [message #446801 is a reply to message #446799] Wed, 10 March 2010 09:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You can't
Re: Partition By [message #446810 is a reply to message #446799] Wed, 10 March 2010 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramesh55.sse wrote on Wed, 10 March 2010 16:08
It's creating new table,but i want to partition the existed table.

Use dbms_redefinition.

Regards
Michel
Re: Partition By [message #446873 is a reply to message #446800] Wed, 10 March 2010 19:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
pablolee wrote on Thu, 11 March 2010 02:09

Anybody for the Dunning-Kruger effect?


Thanks a frickin heap. Now there's a nasal spray of coffee all over my laptop.
Re: Partition By [message #446959 is a reply to message #446873] Thu, 11 March 2010 01:30 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
rleishman wrote on Wed, 10 March 2010 19:36
pablolee wrote on Thu, 11 March 2010 02:09

Anybody for the Dunning-Kruger effect?


Thanks a frickin heap. Now there's a nasal spray of coffee all over my laptop.


Smile
Re: Partition By [message #446978 is a reply to message #446873] Thu, 11 March 2010 04:02 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Smile Gimme the address and I'll send you a new one (coffee that is)
Re: Partition By [message #446979 is a reply to message #446810] Thu, 11 March 2010 04:03 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Wed, 10 March 2010 15:48
ramesh55.sse wrote on Wed, 10 March 2010 16:08
It'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'
Previous Topic: RATIO_TO_REPORT
Next Topic: index
Goto Forum:
  


Current Time: Wed Dec 07 14:46:19 CST 2016

Total time taken to generate the page: 0.14572 seconds