Here's my experience with partitioning along time period lines. First of
all, the partition key does not have to be directly related to or even a
part of the primary key. The way I have done it is to combine portions of a
date column, in this case from your parent table, to create a partitioning
key, like Year+Month (200303). The process of creating the partitions can be
automated so the DBA doesn't have to worry about, including naming the
partitions to include the partition key value (like tablename_P_yyyymm),
which makes automating other tasks more convenient.
The question of partitioning for performance vs. purgeability (did I just
coin a word?) is really a non-issue. When you have large amounts of data on
which you are running reports, being able to specify a year/month
combination as part of the selection criteria can tell the optimizer to
ignore partitions that should not be included in the query results. If you
have 5 years of data, and you are only interested in a couple of months, it
makes allot of sense to lop off the other 58 months in one fell swoop.
If anyone would like to discuss this issue more directly, please email me.
Andrew J. Hauger
Senior Technical Consultant
Diligent Consulting, Inc.
3737 Broadway, Suite 340
San Antonio, Texas 78209
andyh@xxxxxxxxxxxxxxx
-----Original Message-----
From: ERWin-DB-Modeling@xxxxxxxxxxxxxxx
[mailto:ERWin-DB-Modeling@xxxxxxxxxxxxxxx]
Sent: Thursday, March 20, 2003 4:26 AM
To: ERWin-DB-Modeling@xxxxxxxxxxxxxxx
Subject: [ERWin-DB-Modeling] Digest Number 51
To subscribe, send an email to:
ERWin-DB-Modeling-subscribe@xxxxxxxxxxxxxxx
To unsubscribe, send an email to:
ERWin-DB-Modeling-unsubscribe@xxxxxxxxxxxxxxx
Archives: http://groups.yahoo.com/group/ERWin-DB-Modeling/messages
------------------------------------------------------------------------
There are 3 messages in this issue.
Topics in this digest:
1. Re: designing partition keys
From: Warren Cotton <wrcotton@xxxxxxxxx>
2. RE: Digest Number 50
From: "Andrew Hauger" <andyh@xxxxxxxxxxxxxxx>
3. Re: designing partition keys
From: g.brennan@xxxxxxxxxxxxx
________________________________________________________________________
________________________________________________________________________
Message: 1
Date: Wed, 19 Mar 2003 06:21:24 -0800 (PST)
From: Warren Cotton <wrcotton@xxxxxxxxx>
Subject: Re: designing partition keys
I think you may be partitioning for the wrong reasons.
Partitioning is usually used to improve access
performance, not to facilitate purging. How often do
you purge, as opposed to accessing the data?
--- Mike Ryder <mike.ryder@xxxxxxxxxxx> wrote:
> Has anyone come across any ideas on strategies for
> incorporating
> physical partition key columns into a data model?
>
> The situation I refer to is thus: Due to large
> volumes of data there
> is a desire to physically partition tables based on
> some date, making
> it far more efficient to purge old data. The tricky
> part is that the
> purge date column is currently held in the parent
> table and not the
> child tables.
>
> Obviously we can cascade delete the parent - but
> that will be very
> inefficient at the cild level as these tables would
> not be partitioned
> based on that date.
>
> To make the design efficient wuld require the same
> date column used
> for purging to be added to each of the child tables.
>
> It offends me to store functonal data merely for
> physical performance
> reasons and even worse - to store it redundantly all
> over the place.
>
> Anyone have ideas on how this might be addressed
> without throwing good
> data modelling practises out the window?
>
> btw the DBMS is Oracle 8i, and the tables in
> question are up to 8GB
> (not sure of the row counts).
>
> Thanks
>
> Mike Ryder
>
>
__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
________________________________________________________________________
________________________________________________________________________
Message: 2
Date: Wed, 19 Mar 2003 08:40:52 -0600
From: "Andrew Hauger" <andyh@xxxxxxxxxxxxxxx>
Subject: RE: Digest Number 50
Mike,
Shall we assume the relationship is one-to-many from parent to child? What
kind of application is this; transactional or reporting?
Andy Hauger
-----Original Message-----
From: ERWin-DB-Modeling@xxxxxxxxxxxxxxx
[mailto:ERWin-DB-Modeling@xxxxxxxxxxxxxxx]
Sent: Wednesday, March 19, 2003 4:21 AM
To: ERWin-DB-Modeling@xxxxxxxxxxxxxxx
Subject: [ERWin-DB-Modeling] Digest Number 50
To subscribe, send an email to:
ERWin-DB-Modeling-subscribe@xxxxxxxxxxxxxxx
To unsubscribe, send an email to:
ERWin-DB-Modeling-unsubscribe@xxxxxxxxxxxxxxx
Archives: http://groups.yahoo.com/group/ERWin-DB-Modeling/messages
------------------------------------------------------------------------
There is 1 message in this issue.
Topics in this digest:
1. designing partition keys
From: "Mike Ryder" <mike.ryder@xxxxxxxxxxx>
________________________________________________________________________
________________________________________________________________________
Message: 1
Date: Tue, 18 Mar 2003 23:35:36 -0000
From: "Mike Ryder" <mike.ryder@xxxxxxxxxxx>
Subject: designing partition keys
Has anyone come across any ideas on strategies for incorporating
physical partition key columns into a data model?
The situation I refer to is thus: Due to large volumes of data there
is a desire to physically partition tables based on some date, making
it far more efficient to purge old data. The tricky part is that the
purge date column is currently held in the parent table and not the
child tables.
Obviously we can cascade delete the parent - but that will be very
inefficient at the cild level as these tables would not be partitioned
based on that date.
To make the design efficient wuld require the same date column used
for purging to be added to each of the child tables.
It offends me to store functonal data merely for physical performance
reasons and even worse - to store it redundantly all over the place.
Anyone have ideas on how this might be addressed without throwing good
data modelling practises out the window?
btw the DBMS is Oracle 8i, and the tables in question are up to 8GB
(not sure of the row counts).
Thanks
Mike Ryder
________________________________________________________________________
________________________________________________________________________
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.459 / Virus Database: 258 - Release Date: 2/25/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.459 / Virus Database: 258 - Release Date: 2/25/2003
________________________________________________________________________
________________________________________________________________________
Message: 3
Date: Wed, 19 Mar 2003 15:17:25 +0000
From: g.brennan@xxxxxxxxxxxxx
Subject: Re: designing partition keys
Mike,
Leaving aside the question of should you partition for purging?...
Quite simply if you want to partition by <key> you have to partition by
<key>.
>From what you describe the partitioning key is in the parent and the child
relationship is non-identifying so by definition you don't identify a child
row by it's
partition (parent) key and therefore can't partition the child table in a
meaningful
manner.
The minimum you would need is to have an identifying relationship and
migrate the
parent PK.
Since the issue seems to be volumes & visibility of data how about...
(depending on process/volumes/transactions )
An alternative would be to create discrete period tables and use a union
all view
to present a single view to the user app. This requires some processing
logic to
achieve and maintain it, basically you build/rebuild the view to include new
periods
and to exclude old periods (the data to purge), which is then purged at your
convenience.
An old technique is simple to flag rows as deleted, hiding them with a view,
which
are then purged at your convenience.
Regards
George
<pre>
<html><body>
<tt>
Has anyone come across any ideas on strategies for incorporating<BR>
physical partition key columns into a data model?<BR>
<BR>
The situation I refer to is thus: Due to large volumes of data
there<BR>
is a desire to physically partition tables based on some date, making<BR>
it far more efficient to purge old data. The tricky part is that
the<BR>
purge date column is currently held in the parent table and not the<BR>
child tables.<BR>
<BR>
Obviously we can cascade delete the parent - but that will be very<BR>
inefficient at the cild level as these tables would not be partitioned<BR>
based on that date.<BR>
<BR>
To make the design efficient wuld require the same date column used<BR>
for purging to be added to each of the child tables.<BR>
<BR>
It offends me to store functonal data merely for physical performance<BR>
reasons and even worse - to store it redundantly all over the place.<BR>
<BR>
Anyone have ideas on how this might be addressed without throwing good<BR>
data modelling practises out the window?<BR>
<BR>
btw the DBMS is Oracle 8i, and the tables in question are up to 8GB<BR>
(not sure of the row counts).<BR>
<BR>
Thanks<BR>
<BR>
Mike Ryder<BR>
<BR>
</tt>
<br>
<br>
<tt>
To subscribe, send an email to:<BR>
ERWin-DB-Modeling-subscribe@xxxxxxxxxxxxxxx<BR>
<BR>
To unsubscribe, send an email to:<BR>
ERWin-DB-Modeling-unsubscribe@xxxxxxxxxxxxxxx<BR>
<BR>
Archives: <a href="<a href="http://groups.yahoo.com/group/ERWin-DB-
Modeling/messages">http://groups.yahoo.com/group/ERWin-DB-
Modeling/messages</a></tt>">http://groups.yahoo.com/group/ERWin-DB-
Modeling/messages">http://groups.yahoo.com/group/ERWin-DB-
Modeling/messages</a></tt></a>
<br>
<br>
<tt>Your use of Yahoo! Groups is subject to the <a href="<a
href="http://docs.yahoo.com/info/terms/";>Yahoo!">http://docs.yahoo.com/info/
ter
ms/">Yahoo!</a>Terms of Service</a>.</tt>
</br>
</body></html>
</pre>
________________________________________________________________________
________________________________________________________________________
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.459 / Virus Database: 258 - Release Date: 2/25/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.459 / Virus Database: 258 - Release Date: 2/25/2003
------------------------ Yahoo! Groups Sponsor ---------------------~-->
Get 128 Bit SSL Encryption!
http://us.click.yahoo.com/xaxhjB/hdqFAA/xGHJAA/VzvwlB/TM
---------------------------------------------------------------------~->
To subscribe, send an email to:
ERWin-DB-Modeling-subscribe@xxxxxxxxxxxxxxx
To unsubscribe, send an email to:
ERWin-DB-Modeling-unsubscribe@xxxxxxxxxxxxxxx
Archives: http://groups.yahoo.com/group/ERWin-DB-Modeling/messages
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
|
