| 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
steve parker wrote:
>
> 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.
> >
> > That requirement sounds more like a parent-child relationship and a
> > couple of surrogate keys to me (two two tables) or a nested table/varray
> > type arrangement (one table)...
> >
> > ... not thousands and thousands...
> >
> > Cheers
> > Connor
> > --
> > ===========================================
> > Connor McDonald
> > "These views mine, no-one elses etc etc"
> > connor_mcdonald_at_yahoo.com
> >
> > "Some days you're the pigeon, and some days you're the statue."
>
> 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
Steve, see Karsten's post for the foreign key solution...
Using Nested Tables you basically end with the child table 'inline' as a column of the parent.
Using VARRAY is a similar, but there is a fixed maximum of entries
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Wed Dec 08 1999 - 05:17:04 CST
 |
 |