Re: One large table vs many small tables

Hi,
your problem is very similar to one that I have encountered at another telecom company.
The solution is divide and conquer is obviously the way to go. The advantage as I am sure you are aware is that ORACLE8 partitioning offers minimising your risks.
There is one unfortunate flaw with ORACLE partition tables you can not cluster your table on itself.
This for Call Data Records is the only way to go. I am talking about mass entries of data that can be clustered on a key therefore all data entries for a key example client within a state are contained within one or two blocks.
So a partition view is the solution.
Partition view USA of STATES keyed on the state and individual clustered tables based upon clients.
Access can then be managed by synonym/user/views or a combination of all three.
Cheers
The aged Aunt Augusta
www.oriolecorp.com - Free tools and solutions

Dave Hardy wrote:

> 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 Fri Nov 06 1998 - 09:23:00 CST