RE: avoid walking through all partitions

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_at_gmail.com] 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>

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