| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: cost of having MANY tables
Connor McDonald wrote:
> Steve Parker wrote:
> >
> > Karsten,
> >
> > I was indeed referring to MANY, MANY tables. I'm talking ultimately
> > hundreds of thousands of small tables with between 10 - 1000 rows each.
> >
> > The need for these small tables arises from the need for certain fields in
> > some tables to be a "list" of values that has no imposed limit. e.g., in
> > table CUSTOMER, a field named CONTACTS should growable - thus the need for a
> > table called "<cusName>CONTACTS" for each row in table CUSTOMER.
> >
> > I do appreciate the help here.
> >
> > I'm acting as application developer, dbs, overall project lead, ect...
> >
> > so, my reasons for questioning are based on all angles
> >
> > -steve
> >
> > karsten_schmidt8891_at_my-deja.com wrote:
> >
> > > 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.
>
>
>
Can you elaborate?
would a varray allow for a list of no imposed limit?
i have a table of "items". Each "item" has a field that is a list with no imposed limit. How would you do this? I am not an extremely experienced DB designer...
thanks much,
steve
Received on Tue Dec 07 1999 - 11:09:57 CST
 |
 |