avoid walking through all partitions

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Fri, 9 Jul 2010 20:03:13 +0700
Message-ID: <AANLkTinboCxA7yTkl3bgPobt_Y2tr3TOG_tHLGFgMjIv_at_mail.gmail.com>



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
--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 09 2010 - 08:03:13 CDT

Original text of this message