On Tue, Sep 28, 2010 at 6:31 AM, sandeep prakash dhumale
<sandy9940@rediffmail.com> wrote:
I have a table with 400M records with 5 int columns having index only on 1 column.
How is your data used? Is the update done by the primary key? Are the queries segmented in some way that may divide the data based on one of the other columns?
You should investigate using partitions to hold your data. I'd recommend at least 100 partitions. I've done this with great success by dividing some tables along one of the foreign keys. My table was just a pure relation relating the PKs of two other tables. After analyzing the queries that were most often run, we decided to split along the one which resulted in the fewest partitions being referenced per search.
By splitting, we reduced a 200M+ row table into 100 tables of ~2M rows, and the constraint exclusion (or altering the queries to directly access the proper partition) reduced our query times dramatically.