Re: avoid walking through all partitions
- From: Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Mon, 12 Jul 2010 04:25:51 +0700
mark,
about partial data due to date partitioning isn't major to this apps.
this system is handling auditing about all transactions.
in summary table, trackingid is really unique
in trxdetail, there is combination trackingid and messid will make it
unique too. all other tables related to trackingid in summary and
trxdetail tables.
again, I really cannot test the transaction during housekeeping, due
to no such environment like production one.
On Sun, Jul 11, 2010 at 9:55 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> Which table is "the" table?
>
> Which of these tables do you plan to partition?
>
> It seems likely that trackingid is not unique in trxdetail and that
> trxdetail would have a new date for each entry. Is that right, and if so, do
> you need to keep the entire trackingid related set until the youngest
> trxdetail is at least six months old?
>
> If all that is true, then even if you partition by date, you will not be
> able to cleanly exchange/drop partitions by date on the six months boundary
> unless you make some provision for first copying out the pieces of the
> trackingid set that has some rows older than six months but is still active
> because there are also more recent rows.
>
> That possibly could work out, with about 30 weekly partitions to hold the
> rolling 6-plus months plus "old" active transactions.
>
> What you want to be alert to and avoid is creating a lost detail situation
> for a trackingid that is likely to be a sore spot: One that has multiple
> rows of detail spread over several months. I don't know what you are
> tracking, but I can think of several scenarios where this might create a
> customer service nightmare and customers hating you.
>
> Good luck,
>
> mwf
> -----Original Message-----
> From: Ujang Jaenudin [mailto:ujang.jaenudin@xxxxxxxxx]
> Sent: Sunday, July 11, 2010 12:16 AM
> To: Mark W. Farnham
> Cc: Oracle Discussion List
> Subject: Re: avoid walking through all partitions
>
> Hi mark,
>
> the tables which have trackingid is 6 tables.
> - summary
> - trxdetail
> - the rest trackingid with Clob data type
>
> the table will be filled 1 million rows per day. on peak hours can reach
> 1000tps
> so partition on weekly basis is moderate I think.
>
> this system around 2 years production, but got problem with housekeeping :D
> then no chance to modify inquiries/dml
>
>
> <snip>
>
>
>
--
thanks and regards
ujang jaenudin
jakarta - indonesia
--
//www.freelists.org/webpage/oracle-l
Other related posts:
