Re: Index vs. partitioning for performance

On Thu, 20 Apr 2006 14:23:46 -0700, aehven wrote:

> Hi,
>
> Suppose:
>
> - you have a table that has only a few colums and one of them, an
> integer column, is used as an index;

White kind of an index?

> - rows in this table are only ever selected using the index field;
> - no rows are ever deleted from the table; only inserts and updates
> are allowed;
> - this table can grow to one billion rows.

As long as it stays below nine trillions, it's OK. Billion is not a big deal.

>
> Can query (or insert) performance for this table be improved by
> partitioning the table and/or its index? If partitioning were done,
> the only sensible partitioning scheme for this data would be
> hash-partitioning on the indexed column.

What kind of querying/updating are you doing? OLTP, data mining, big reports? If OLTP, primary key must be a global index. If you're doing big reports, be a man, do it like real men do it and use full table scan. It will give you some time to think about your purpose in this universe while you are waiting for the report to finish.

>
> I'm guessing the answer is no. I'm also guessing that rebuilding the
> index is never an issue in this scenario. Am I right or am I right?

Can you test it? You don't have to have a billion rows, you can test the difference with a million or two. First, do one monolithic table, then partition it and run the same applications against the both versions.

-- 
http://www.mgogala.com