Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cost of having MANY tables

Re: cost of having MANY tables

From: <jobrien99_at_my-deja.com>
Date: Thu, 02 Dec 1999 19:24:42 GMT
Message-ID: <826h14$nfe$1@nnrp1.deja.com>

What about batch jobs that fork and have multiple processes inserting millions of rows into a single table. You can increase freelist and initrans but sometimes vertical partitioning into different tables can be easier to tune if you know your i/o hardware well.

Regards,
John

In article <825gd4$9ck$1_at_ctb-nnrp1.saix.net>,   "Billy Verreynne" <vslabs_at_onwe.co.za> wrote:
> Steve Parker wrote in message <384458AE.75EAEC11_at_averstar.com>...
> >Can someone please give me a run down of the costs of having a HUGE
> >amount of tables (each being pretty small) as opposed to having fewer
> >tables that are bigger?
>
> This is not a simple comparison.
>
> You are in fact "breaking" the ERD and Codd's rule if you start to do
this
> type of thing - splitting a single physical table into multiple
smaller
> tables. Not that this is necessarily wrong, but something to keep in
mind as
> it makes the "translation" of your 3rd normal design (or star schema
or
> whatever design you're using) into the physical database structure
more
> complex.
>
> The application layer's complexity is increased as the developer no
longer
> needs to only deal with single table X, but with tables X1 and X2
thru to
> Xn.
>
> You can remove this from the application layer using views and stored
procs
> in the database. However, you're not removing the complexity - you're
simply
> shifting it to the database layer.
>
> There are various ways to partition data physically. From a
conceptual point
> of view you can either split data horizontally or vertically.
Horizontally
> means that instead of having all the columns in a single table, you
create
> multiple tables, all having the same key, and then split the columns
between
> these. Vertical partitioning is what you are referring to - breaking
rows
> from a single table up into multiple tables.
>
> From a technical and implementation point of view, you also have
various
> options. You can partition data using clusters, partition tables,
partition
> views and so on.
>
> So what is the Oracle costs between a couple of VLT's (very large
tables)
> and a lot of smaller tables. Negligible. The additional overheads in
Oracle
> are basically that the data dictionary requires more space to store
the
> database structure data. But the space requirements for the data
dictionary
> are really minuscule when compared to the overall space requirements
of the
> entire database.
>
> Joins and the like? That depends entirely on what you are trying to
do. The
> nature of the joins (hash, nested loops), the indexes, parallelising
of full
> table scans, and so on. You can run into performance problems by
joining 10
> small tables together. You can run into performance problem when
processing
> a single large table. You can not say because I have lots of small
tables I
> will not have performance problems. Neither can you say because I have
> everything in a few large tables, I will not have performance
problems.
> Processing data is far more complex than the simple issue of the
number of
> tables envolved. Or even the data volumes involved.
>
> For example, on a data warehouse I help to build, I could profile a
single
> family's medical history for the last 3 years in seconds. This
required
> processing a VLT of over 170 million rows, joining it with several
smaller
> tables and aggregating the resulting data. What played a critical
role here
> was not the number of tables involved, but how well the database was
> designed to cope with this type of requirement.
>
> So what to do? IMO you need to first identify WHAT needs to be done.
User
> requirements, specifications, logical designs and the like. Once you
have
> that, then you have enough info to decide HOW to do it. Doing the
technical
> stuff - translating the logical design and user requirements into a
physical
> design and processes.
>
> My crazed thoughts anyway... ;-)
>
> regards,
> Billy
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 13:24:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US