Re: One large table vs many small tables

You did not say what version you are running. If 8.x, why not partition the table with local indexes on the subordinate keys. This gives you the flexibility of multiple tables(partitions) in terms of storage and rebuilds, while keeping the query and app code relativly simple.

In article <71sl0u$cus$1_at_schbbs.mot.com>,   "Alan D. Mills" <alanm_at_uk.europe.mcd.mot.com> wrote:
> 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
> >
> >
> >
> >
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Nov 05 1998 - 14:42:15 CST