Re: data dictionary with a million tables

Tell them to come up with a more realistic design. If many of the tables were actually referenced by SQL statements, the database would have to cache SQL statements referring to each table and the definition of each table and index. This would be a huge overhead on parsing and would require a lot of memory for the sql area and dictionary cache.

Also, extent allocation is a large, single-threaded overhead in oracle. An ST enqueue is required for each extent (recursive SQL has to take a slice from the fet$ table for each extent). Try the following command sometime:

create table fred
storage (initial 10k next 10k pctincrease 0 minextents 1000) tablespace <whatever>
As select * from sys.dual;

Tell me how long it takes to create, then drop it.

A table with 100 rows would only require a few blocks and you may waste a lot of space. It would be an impossible task to detect any tables or indexes that were over/underallocated and reorganize them unless you had a tool to do this for you.

Regards,
Andrew.

Luis Londono wrote:
>
> I need help with a littlle problem that I have. I have a bunch of
> developers that want to create an application using Oracle that would
> end up having probably a million very small tables (100 - 1000 records
> each). It is easier for them to develop it this way, but I am trying to
> convince them that it is not a good idea. It seems to me that the
> performance of the entire database would suffer a lot since the data
> dictionary would be over worked. Unfortunately I do not have any solid
> evidence or an argument more convincing than a gut feeling. Does anyone
> have some information that would help me? Or if I am wrong, I would
> sure like to know. Thanks.
>
> -Luis
>
> --
> Luis Londono
> Senior Systems Engineer 370 E 1130 S
> Big Planet Inc. Provo, UT 84606
> mailto: luisl_at_bigplanet.net (801) 345-7222
Received on Wed May 05 1999 - 23:14:06 CDT