One large table vs many small tables

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 - 09:59:58 CST