sorry rada2 londo.... males translate :D 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
