RE: Question re range partioning tables

  • From: "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx>
  • To: <Oracle-L@xxxxxxxxxxxxx>
  • Date: Wed, 19 Jan 2005 09:42:33 -0500

John,

We have a table that has 500 million records, partitioned by month.
Each partition has around 40 million records.  The queries are
surprisingly quick -  a record for a specific day is returned in just a
few seconds. =20
In your case, you should be able to get adequate performance without
partitioning by day.  If you don't have any space issues, partition by
month and keep 4 partitions on-line (3 partitions would only leave you
with 2 months of data when you are doing your maintenance).  I've never
had users complain that I'm keeping records too long, so if you have the
space, only do the maintenance once a month.

Jay

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of John Dunn
Sent: Wednesday, January 19, 2005 9:28 AM
To: 'Ron Rogers'; Oracle-L@xxxxxxxxxxxxx
Subject: RE: Question re range partioning tables

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@xxxxxxxxxxxxx]
Sent: Wednesday, January 19, 2005 1:45 PM
To: Oracle-L@xxxxxxxxxxxxx; jdunn@xxxxxxxxx
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@xxxxxxxxx> 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











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

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





**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the =
use of the individual or entity to which they are addressed and may contain=
 information that is privileged, proprietary and confidential. If you are n=
ot the intended recipient, you may not use, copy or disclose to anyone the =
message or any information contained in the message. If you have received t=
his communication in error, please notify the sender and delete this e-mail=
 message. The contents do not represent the opinion of D&E except to the ex=
tent that it relates to their official business.
--
//www.freelists.org/webpage/oracle-l

Other related posts: