RE: Weekly aggregates

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Thu, 20 Mar 2003 16:41:10 -0500
Message-Id: <24726.322585@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C2EF29.6BF37490
Content-Type: text/plain;

charset="iso-8859-1"

What about materialized view on top of the partitioned table aggregated by week? I don't remember is there a restriction that the material. view must be partitioned same as table? If not it might be a bit of a perf. hit but only once. Also, can determine how to populate the mat. view and if you just get a week at a time can set it up so that just those changes are pushed out to the mater. view. - Just a thought.

Oracle OCP DBA

-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Thursday, March 20, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L Subject: Re: Weekly aggregates

I've used both methods at different sites for different reasons.

If you need the performance of the
partition-wise join, then you keep
the weekly aggregates in monthly
partitions, and work around the
problems of not being able to do
a single week

create as select / exchange partition

If the performance is adequate without
partitionwise joins, but you need to
be able to build the aggregates as
rapidly as possible, then use weekly
partitions. 3 years at weekly partitions is only 150 - 160 partitions - even with a handful of indexes, that shouldn't be
a problem at parse time.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 20 March 2003 19:18

> Hi all:
>
> An application I'm supporting needs weekly aggregates.
> Nothing wrong with that except I'm thinking of how to
> partition that aggregate table. The requirement is to
> keep 3 year history of data. I have been partitioning
> other aggregate tables (monthly etc) by month. This
> makes it easy to drop old partitions AND Oracle can
> use the partitions to reduce the size of the data for
> some queries. I'd like to keep the montly partitioning
> in for the uniformity reasons, but weeks do not lay
> over months, a week can span two months and therefore
> the usefulness of partitions for some of the reports
> will be reduced. I'm wondering how do others approach
> this. Do people partition weekly aggregates by week
> instead of months? ANy other thoughts?
>
> thanks
>
> Gene

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C2EF29.6BF37490
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2654.45">
<TITLE>RE: Weekly aggregates</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>What about materialized view on top of the =
partitioned table aggregated by week?&nbsp; I don't remember is there a =
restriction that the material. view must be partitioned same as =
table?&nbsp; If not it might be a bit of a perf. hit but only =
once.&nbsp; Also, can determine how to populate the mat. view and if =
you just get a week at a time can set it up so that just those changes =
are pushed out to the mater. view.&nbsp; - Just a thought.</FONT></P>

<P><FONT SIZE=3D2>Oracle OCP DBA</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Jonathan Lewis [<A =
HREF=3D"mailto:jonathan_at_jlcomp.demon.co.uk">mailto:jonathan_at_jlcomp.demon=
.co.uk</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, March 20, 2003 3:24 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: Weekly aggregates</FONT>
</P>
<BR>
<BR>

<P><FONT SIZE=3D2>I've used both methods at different sites for =
different reasons.</FONT>
</P>

<P><FONT SIZE=3D2>If you need the performance of the </FONT>
<BR><FONT SIZE=3D2>partition-wise join, then you keep</FONT>
<BR><FONT SIZE=3D2>the weekly aggregates in monthly</FONT>
<BR><FONT SIZE=3D2>partitions, and work around the </FONT>
<BR><FONT SIZE=3D2>problems of not being able to do </FONT>
<BR><FONT SIZE=3D2>a single week </FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; create as select / exchange =
partition</FONT>
</P>

<P><FONT SIZE=3D2>If the performance is adequate without</FONT>
<BR><FONT SIZE=3D2>partitionwise joins, but you need to </FONT>
<BR><FONT SIZE=3D2>be able to build the aggregates as</FONT>
<BR><FONT SIZE=3D2>rapidly as possible, then use weekly</FONT>
<BR><FONT SIZE=3D2>partitions.&nbsp; 3 years at weekly =
partitions</FONT>
<BR><FONT SIZE=3D2>is only 150 - 160 partitions - even with</FONT>
<BR><FONT SIZE=3D2>a handful of indexes, that shouldn't be</FONT>
<BR><FONT SIZE=3D2>a problem at parse time.</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Regards</FONT>
</P>

<P><FONT SIZE=3D2>Jonathan Lewis</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www.jlcomp.demon.co.uk" =
TARGET=3D"_blank">http://www.jlcomp.demon.co.uk</A></FONT>
</P>

<P><FONT SIZE=3D2>Now available One-day tutorials:</FONT>
<BR><FONT SIZE=3D2>&nbsp; Cost Based Optimisation</FONT>
<BR><FONT SIZE=3D2>&nbsp; Trouble-shooting and Tuning</FONT>
<BR><FONT SIZE=3D2>&nbsp; Indexing Strategies</FONT>
</P>

<P><FONT SIZE=3D2>(see <A =
HREF=3D"http://www.jlcomp.demon.co.uk/tutorial.html" =
TARGET=3D"_blank">http://www.jlcomp.demon.co.uk/tutorial.html</A> =
)</FONT>
</P>

<P><FONT SIZE=3D2>____UK_______April 8th</FONT>
<BR><FONT SIZE=3D2>____UK_______April 22nd</FONT>
</P>

<P><FONT SIZE=3D2>____Denmark May 21-23rd</FONT>
</P>

<P><FONT SIZE=3D2>____USA_(FL)_May 2nd</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Next dates for the 3-day seminar:</FONT>
<BR><FONT SIZE=3D2>(see <A =
HREF=3D"http://www.jlcomp.demon.co.uk/seminar.html" =
TARGET=3D"_blank">http://www.jlcomp.demon.co.uk/seminar.html</A> =
)</FONT>
</P>

<P><FONT SIZE=3D2>____UK_(Manchester)_May</FONT>
<BR><FONT SIZE=3D2>____USA_(CA, TX)_August</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>The Co-operative Oracle Users' FAQ</FONT>
<BR><FONT SIZE=3D2><A =
HREF=3D"http://www.jlcomp.demon.co.uk/faq/ind_faq.html" =
TARGET=3D"_blank">http://www.jlcomp.demon.co.uk/faq/ind_faq.html</A></FO=
NT>
</P>
<BR>

<P><FONT SIZE=3D2>----- Original Message ----- </FONT>
<BR><FONT SIZE=3D2>To: &quot;Multiple recipients of list ORACLE-L&quot; =
&lt;ORACLE-L_at_fatcity.com&gt;</FONT>
<BR><FONT SIZE=3D2>Sent: 20 March 2003 19:18</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>&gt; Hi all:</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; An application I'm supporting needs weekly =
aggregates.</FONT>
<BR><FONT SIZE=3D2>&gt; Nothing wrong with that except I'm thinking of =
how to</FONT>
<BR><FONT SIZE=3D2>&gt; partition that aggregate table. The requirement =
is to</FONT>
<BR><FONT SIZE=3D2>&gt; keep 3 year history of data. I have been =
partitioning</FONT>
<BR><FONT SIZE=3D2>&gt; other aggregate tables (monthly etc) by month. =
This</FONT>
<BR><FONT SIZE=3D2>&gt; makes it easy to drop old partitions AND Oracle =
can</FONT>
<BR><FONT SIZE=3D2>&gt; use the partitions to reduce the size of the =
data for</FONT>
<BR><FONT SIZE=3D2>&gt; some queries. I'd like to keep the montly =
partitioning</FONT>
<BR><FONT SIZE=3D2>&gt; in for the uniformity reasons, but weeks do not =
lay</FONT>
<BR><FONT SIZE=3D2>&gt; over months, a week can span two months and =
therefore</FONT>
<BR><FONT SIZE=3D2>&gt; the usefulness of partitions for some of the =
reports</FONT>
<BR><FONT SIZE=3D2>&gt; will be reduced. I'm wondering how do others =
approach</FONT>
<BR><FONT SIZE=3D2>&gt; this. Do people partition weekly aggregates by =
week</FONT>
<BR><FONT SIZE=3D2>&gt; instead of months? ANy other thoughts?</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; thanks</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Gene</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.net" =
TARGET=3D"_blank">http://www.orafaq.net</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Jonathan Lewis</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: jonathan_at_jlcomp.demon.co.uk</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
858-538-5051 <A HREF=3D"http://www.fatcity.com" =
TARGET=3D"_blank">http://www.fatcity.com</A></FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Mailing list =
and web hosting services</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
------</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =

Received on Thu Mar 20 2003 - 15:41:10 CST