Re: cost of having MANY tables

Mark,

 I agree to your points. It all comes down to what the original poster  means by "MANY tables". I was thinking along the lines of multiple  thousands - IMO, maintenance and performance tuning are major issues  with this number of objects.

Karsten

In article <826ebs$le1$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> I believe Karsten has some valid points, but I would like to disagree
> with or modify some of her points.
>
> In article <825mg0$371$1_at_nnrp1.deja.com>,
> karsten_schmidt8891_at_my-deja.com wrote:
> > Hi,
> >
> > You will need more SGA memory to cache parsed statements (oviously,
> > there are going to be a lot of different statements).
> > You also need more memory to cache the data dictionary.
> >
> Just because you have more tables does not mean you will need more
> memory for the dictionary cache. How much memory you need to cache
the
> dictionary objects depends on how often and how many objects are
> accessed concurrently. You can expect to need more shared_pool to
hold
> the additional SQL statements.
> >
> > Maintainance is going to become difficult - you have to take care
of
> > lots of indexes, constraints etc. Performance Tuning is going to be
a
> > headache too.
> >
> I have a tablespace with 150 indexes in it. I can drop and recreate
> every index in the tablespace (with a tablespace coalesce between the
> drop and creates) in about 1 hour. I have another tablespace where
> several of the indexes take an hour each. The time and cpu expended
in
> maintenance has more to do with the total quanity of data than it does
> the number of objects. There will be more effort by the DBA to
> generate 150 index rebuilds as opposed to 15, but this should not pose
> a serious problem.
> >
> > Parsing will become more expensive, you need more CPU horsepower.
> >
> The cost of parsing is normally very small compared to the cost of the
> actual data retrieval. So the total amount of CPU is again more
> dependend on the quanity of data than the number of objects or sql
> statements.
> >
> > Joins, selects should not be that different (apart from the not
data-
> > dependend overhead described above.) - this tends to be bound on
disk
> > i/o.
> >
> > HTH Karsten
> >
> I question what Steve means by having a lhuge number of small objects
> verse a smaller number of large objects. His post implies that he has
> a choice in the matter. I would prefer to work on a system where the
> physical object structure was build based on the logical needs of the
> application and not on preference of the DBA based for object sizes or
> number of total objects. Partitioning tables to allow maintenance
> within expected available window duration, or based on hardware
> considerations I have no problem with. In the real world you are
> forced to live with liminations of your resources.
> >
> > In article <384458AE.75EAEC11_at_averstar.com>,
> > Steve Parker <sparker_at_averstar.com> wrote:
> > > 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?
> > >
> > > i.e., space, speed (individual accesses and joins)
> > >
> > > any help is much appreciated
> > > parker
> > >
> Another point. While joins against small tables can be expected on
> average to perform better than joins on large tables (a lot of
possible
> factors here) if you have to write a join on 9 small tables to get all
> the required data as oppossed to a 3 table join of large tables the
> extra join operations can consume and may exceed the cost of the join
> with large objects. It really all depends on what you are trying to
> accomplish. Anyway these are just my opinions. I do not think that
> anything Karsten said is wrong; it just all depends.
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 06 1999 - 03:35:34 CST