Re: DBA performance and modeling question

In article <3A155D08.A59DB77E_at_ix.netcom.com>,   datasyst <datasyst_at_ix.netcom.com> wrote:
> I have a situation where we have exactly same table for different
 types
> of objects. The question is should we model it as multiple tables
 even
> though the columns are same or should we model it as one large table
 and
> have "type" as a column which distinguishes the records. The criteria
> we are evaluating is the table can become huge with e-commerce sites.
> We are assuming this table is going to be huge with 20-40million
> records. If we design it as multiple tables, there are 4 types, which
> could break down to 5 -10 million records per table if we keep them as
> separate tables.
>
> In terms of database design I like it as one table but not sure if
> having many small tables will be any performance benefit vs one big
> table. Most of the queries will be using index search.
>
> Thanks in advance.
> SRK
>
>

SRK, There is no all-purpose "right" answer. Here's some questions to ask though:

• Will you more often be operating on the rows within a given type of record, or do you want to be able to work with them universally (i.e. in SELECT statements across multiple types)?
• Can you foresee that there are more types coming? Will future occurrences of these "types" definately have the same structure, or will there be customizable differences?
• Is performance more important when you write out the data or when you read it?

Keep in mind, you can always partition your data by type so that 1 physical table does not have to be viewed by Oracle as one object. Likewise, views can be used to bring multiple tables together via UNIONs to make them queryable as if they were one table.

HTH. Matt.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 17 2000 - 11:37:10 CST