Home » SQL & PL/SQL » SQL & PL/SQL » Date Range Partitions
Date Range Partitions [message #241368] Tue, 29 May 2007 06:43 Go to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hi

I could really use some advice on partioning of a table, since I haven't done it before.

I have this table with about 30 million rows, which i want to
split into a date range partition.

There is one column called period which unfortunately is a char(7) which contains '2004-05' , '2004-06' , '2004-07' and so on.

Is it possible to make a date range partition on that particulary column ?
Or would another partition approach suite this type of problem better ?

CREATE TABLE PART_TEST
(
  PERIOD          CHAR(7 BYTE)                  NOT NULL,
  SOMEDATA        VARCHAR2(5 BYTE)
  )
PARTITION BY RANGE (PERIOD) 
(
.....
)


Oracle version is 9.2.0.6

Any advice much appreciated
Cheers
Re: Date Range Partitions [message #241379 is a reply to message #241368] Tue, 29 May 2007 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is possible but the first question you have to ask is: Why partitioning this table?

Have a look at article Partition Decisions from Arup Nanda.

Regards
Michel
Re: Date Range Partitions [message #241415 is a reply to message #241379] Tue, 29 May 2007 09:18 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Thanks Michel for your reply.

This table (which is actually quite small) resides in a datawarehouse, were they never heard of such things like partitioning , materlized views etc.

Been running statspack , analyzing querys and batch jobs etc.
I "think" that partitioning some of the tables will give a nice boost. But im elaborationg first of course Wink

I added a new date column and did the range partioning on that column instead of the char(7) column. But your are saying that you can do a range partitoning on the char column ?
If you have an example to demonstrate, that would be great.

Cheers
Re: Date Range Partitions [message #241426 is a reply to message #241415] Tue, 29 May 2007 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference bewteen date, number or string partitioning.
Just take care of the values you give.

And read really carefully the article I posted, maybe you don't need partitioning, maybe you worsen your performances with it.

Regards
Michel
Re: Date Range Partitions [message #241428 is a reply to message #241426] Tue, 29 May 2007 09:36 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Ok thanks again Michel
Previous Topic: query problem
Next Topic: Dropping a Datafile and Deleting its References
Goto Forum:
  


Current Time: Thu Dec 08 00:08:59 CST 2016

Total time taken to generate the page: 0.23031 seconds