dear lists,

I have a case that table has 2 keys:
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

Search Discussions

  • Mark W. Farnham at Jul 11, 2010 at 2:55 pm
    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,

    -----Original Message-----
    From: Ujang Jaenudin
    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

    the rest trackingid with Clob data type

    the table will be filled 1 million rows per day. on peak hours can reach
    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


Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
postedJul 9, '10 at 1:03p
activeJul 11, '10 at 2:55p

2 users in discussion

Mark W. Farnham: 1 post Ujang Jaenudin: 1 post



site design / logo © 2022 Grokbase