Re: avoid walking through all partitions
- From: Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Sun, 11 Jul 2010 11:16:29 +0700
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
On Sat, Jul 10, 2010 at 12:26 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> Well, first, what Ric Van Dyke wrote is probably enough. But a couple
> questions do come up. How many partitions are candidates? If you're getting
> one or more partitions per day to keep them a reasonable size, then six
> months worth is a lot to peek at. If you've got a rolling seven, then its
> probably okay and a more complex solution is probably counter productive.
>
> The other question is whether trackingids are associated with each other.
> Some tracking systems track an incident as multiple rows with a row for each
> action taken. Since your trackingid is unique that would imply a relation
> between trackingids that are part of a single incident. Often this is done
> as a non-unique trackingid with the sequence for row within trackingid.
> Either way that, are you allowed to get rid of older trackingids that are
> associated with trackingids too young to discard? Or does that case simply
> not fit your schema/model?
>
> Regardless, there is a solution space where it would be useful to have
> another table containing trackingid, date. (Or trackingid, seq, date for the
> many rows per incident model). Then, of course you look up the date for the
> trackingid you're inquiring about and add that to the lookup. You'll
> probably get that in a single index read (you might even use an IOT if they
> work well on your release). Then you add the date predicate to the where
> clause and you magically hit only the partition(s) you need to hit.
>
> If you've got lots of partitions, the overhead of the original insert into
> this new table, reading it everytime to inquire on a trackingid, and
> deletion from the old table later will be worthwhile. If you've got few, it
> won't. If it is a close call when you experiment and test, only do the extra
> engineering if that seems fun to you. How big is "lots" and how big is
> "few?" I don't know. Your mileage will vary.
>
> This can also be the backbone of dealing with associated trackingids, but
> this is too long already.
>
> Good luck,
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Ujang Jaenudin
> Sent: Friday, July 09, 2010 9:03 AM
> To: Oracle Discussion List; indo-oracle@xxxxxxxxxxxxxxx
> Subject: avoid walking through all partitions
>
> dear lists,
>
> I have a case that table has 2 keys:
> datetime
> trackingid -- unique (from sys_guid)
>
> I have plan to partition the table, but majority of query are by
> trackingid column in the where clause or joins.
>
> this table has retention policy 6 months.
> so, partition by datetime column is the best choice.
> I know that global partition will work well on the trackingid from the
> performance side,
> but during housekeeping, it should be "maintained" which actually DML
> that delete for the dropped its partition's rows.
> and index fragmentation will occur (index not balanced?).
>
> the next choice, I think of composite partition,
> range-hash (range by datetime, subpartition hash by trackingid),
> and index on trackingid will be LOCAL index. then no more "issue"
> during housekeeping.
>
> I know about walking through all partition during find something by
> trackingid alone...
> is hash algorithm will help us that it's not scan index on each
> partition on this strategy?
> is my assumption correct, when
> trackingid = blah
> oracle will compare the hashed(blah) with the subpartition hash key,
> so walking through the partitions will go faster?
>
> or do you have other strategy?
>
>
> don't ask me to modify query or blame application design... :D
>
> --
> thanks and regards
> ujang jaenudin
> jakarta - indonesia
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
--
thanks and regards
ujang jaenudin
jakarta - indonesia
--
//www.freelists.org/webpage/oracle-l
Other related posts:
