Re: One large table vs many small tables

If the value of column STATE is going to be the same in each record of each of the 50 new tables then you gain nothing by indexing it anymore. You're primary key of each of the new tables will be different to the primary key of the current large one. That's not really your question though is it?

Assuming you do split the table up though, how will you know, at any moment in time, which table you need to query. Previously it was just the search value of a column changing with each query. With the new design it's the table name itself that changes. How do you intend to manage this in your reports (and other code)?

I'm not sure if you will get the performance increase you might like just by the index but I can imagine twenty different people hitting twenty different tables concurrently must be better than twenty people all hitting the same table concurrently though.

--
Alan D. Mills

Dave Hardy wrote in message <3641CB7D.861F211_at_nortel.com>...
>Let say I have a 50Gb table with one of the columns being a US State
>name. This table is indexed so that the State is the first key. All
>queries to this table are for one state only, there are no joins between
>states.
>
>This table has become unmanagiable. If the index is lost, it would take
>over 2 days to recreate, so we would have to go to backup instead. My
>option is to devide this 50Gb table into 50 x 1 Gb tables (one for each
>state), each with its own index (and possibly grouping them into 10
>different tablespaces).
>
>Would there be any performance improvement between a query to the
>smaller New York table (1Gb), vs to the old 50Gb US table, where New
>York is one of the values in the State Column, and is the first in the
>index?
>
>Thanks,
>Dave
>
>
>
>
Received on Thu Nov 05 1998 - 10:51:31 CST