Is there a limit to number of tables in Oracle?

[bcheong]

Hi all,

We are currently designing a datamodel that potentially will require a very large number (thousands) of tables and potentially, each of these tables will have a large amount of rows.

We are wondering if there is a limit to the number of tables in Oracle. Also, are there any performance impacts to having a large number of tables?

Any help would be appreciated!

Many thanks in advance.

Cheers,
Ben

 

[sem]

Oracle can handle this amount, but the only case I know when this number may be encouraged is when Oracle is provided for hosting as a service.
1000 tables for single application is is a good candidate for redesign. BTW what's the reason for creating 1000+ tables?

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

BCheong,

I totally agree with Dima (Sem)...If a design has "thousands" of tables, I absolutely, positively guarantee (with my paycheck as backing) that the design is missing an opportunity for improvement (okay, I'll say it, "The design is bad"). It is far better to fix it now rather than try to do surgery on the design later. If you wish to fix it now, I'm certain that Dima and I (and others) can suggest design improvements that will be far superior to a "thousands-of-tables" design.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:02 (11Mar04) UTC (aka "GMT" and "Zulu"), 11:02 (11Mar04) Mountain Time)

 

[mrn]

We currently have over 45,000+ tables in our Oracle DB

--
| Mike Nixon
| Unix Admin
|
----------------------------

 

[carp]

On the database where we're running Oracle Apps 11.5.9, we have over 19,000 non-custom tables supplied by Oracle.

Elbert, CO
0837 MST

 

[SantaMufasa]

Mrn and Carp,

...and your points are....?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:30 (12Mar04) UTC (aka "GMT" and "Zulu"), 09:30 (12Mar04) Mountain Time)

 

[carp]

Dave -
Two points -
(1) a thousand tables is not necessarily excessive - although it might be.
(2) a response to your assertion: "If a design has "thousands" of tables, I absolutely, positively guarantee (with my paycheck as backing) that the design is missing an opportunity for improvement (okay, I'll say it, "The design is bad")." I am pointing out that 11.5.9 has "thousands" of tables. Therefore, according to your standards, it is a bad design. While 11i could certainly be improved, I'm not sure I would go so far as to say it is "bad design" (although, based on my experiences w/ 11i, I certainly would not try to lay claim to your paycheck on this!).

Elbert, CO
1748 MST

 

[SantaMufasa]

Carp,

First, those who know you and me know that we are "cut from the same cloth" (nearly to the point of sharing a brain). And in the final analysis, I cannot think of a time when our philosophies didn't match. And this is probably like all the other times. There may be an application out there whose "good" database design NEEDS thousands of tables...I just haven't seen it yet.

My personal experience is that I have NEVER seen an application with "thousands" of tables that would not be improved by the consolidation of tables where "artificial" boundaries exist between tables. (Remember the example we saw while we were at Oracle where a car manufacturer had separate tables for each vehicle model's Bill of Materials...consolidation of their BoMs into one table freed them up from maintaining well over 100 previously separate tables !)

My point is, if you have even hundreds of tables for an application, evaluate the tables for artificial or contrived boundaries...Are the tables separate because we HUMANS see the objects as separate, when, in fact, they have the same (or similar) attributes and the computer can differentiate them simply with codes.

And just because Oracle's database design for an application involes thousands of tables does not imply that the database design is the best that it can be.

Are we agreeing or disagreeing on this one?

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:17 (13Mar04) UTC (aka "GMT" and "Zulu"), 18:17 (12Mar04) Mountain Time)

 

[carp]

As usual, we are in agreement. At the risk of sounding Clintonian, "it depends on what the meaning of 'bad' is"!

While I would not call the 11i design "bad", I would certainly not call it "good". And apparently, neither would Oracle - hence their ongoing design morphing (which includes leaving unused tables in the database!).

But you also know I am chockablock full of kneejerk reactions, and I tend to break out into hives when I see magic numbers - such as assessing an design as "bad" because it has thousands of tables. You tripped my trigger, Dave! Now if only I could find a "good" design that had thousands of tables! But I never have and, like yourself, doubt that I ever will!

Kumbaya!

Elbert, CO
1858 MST