Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question re range partioning tables

RE: Question re range partioning tables

From: John Dunn <jdunn_at_sefas.com>
Date: Wed, 19 Jan 2005 14:27:56 -0000
Message-ID: <004a01c4fe33$129924b0$3e04050a@johnman>


My table has a date value. For each date there will be approx 250000 rows. I was planning to have a partition per day, since data will be deleted for a specfic day.
3 months worth of data would be held, e.g approx 90 partitions. Is this sensible or is that too many partitions?

My question was whether I need to explictly create a partition for each day, since obvioulsy I do not want to do this upfront.

Is it the case that I will need to run alter table add partition every day to add the new partition.

John

-----Original Message-----
From: Ron Rogers [mailto:RROGERS_at_galottery.org] Sent: Wednesday, January 19, 2005 1:45 PM To: Oracle-L_at_freelists.org; jdunn_at_sefas.com Subject: Re: Question re range partioning tables

John,
 The use of a date field as the partitioning column is a good choice if the
amount of the data is about the same for each date range and the majority
of the queries are date related.
when you create a table you have to specify the date range for each partition and allow for
values greater than the last partitioned date range. I have many table that are partitioned by year and also a table_max_value partition for each table. The table_max_value allows an area for the data to be stired if the date is greater than the current year.
Each year end after checking that there is no data in the table_max_value partition I drop the table_max_value partition and create a new_next_year partitoin with the appropriate date value and then recreate the
table_max_value partition. The active yearly tables are approx 3 Gig and the max_value partition is 4 Meg.
All tables are LMT. It makes the table and data management easier with partitioned tables.
Ron

>>> "John Dunn" <jdunn_at_sefas.com> 01/19/2005 8:31:57 AM >>>
I have a table which has a date column I would like to use for range partitioning.
However, looking at the documentation examples it appears necessary to specify specific ranges when creating the table

e.g.

PARTITION part1 VALUES LESS THAN TO_DATE (01-APR-1994, DD-MON-YYYY), PARTITION part1 VALUES GREATER THAN TO_DATE (01-APR-1994, DD-MON-YYYY),
Is it always necessary to provide specific range values? What happens at
time moves on. Is it necessary to repartition the table to add new time
periods?
Or is it possible to specify ranges relative to SYSDATE e.g SYSDATE - 30,
SYSDATE - 60 etc
John

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 19 2005 - 09:29:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US