Partition Question (8i)

We have a table that grows at a rate of 500,000 rows a month and will most likely increase in growth by a factor of 10. We are moving to a new harware environment (Solaris 8, nice disk array subsystem -- no details yet), and would like to be ready for increased volume as well as to improve the throughput as it stands today.

The transactions get into this large table are are updated several times (going through different statuses). The records in the table are updated either automatically by the system (if it receives some external feedback) or manually by users. The following are the key elements of the table:

• Geo. location
• group #
• trans. id (surrogate key)

Users/processes use one of these to locate a record or a group of records.
Now to partitioning: say, there were 4 geo. locations that most of the records fell into fairly uniformly, and there were 4 ranges of group #s that did the same, would it make to create 16 partitions:  loc 1, group range 1
 loc 1, group range 2
 ...
 loc 4, group range 4

If the search that users/automated processes do uses just the group #, would the optimizer be smart enough to chose the correct partitions, or would it always need the location information?

Are there other, alternative ways, of partitioning the data for performance?

Any help will be greatly appreciated.

Thank you!

--Alex Received on Tue Feb 26 2002 - 09:02:19 CST