Operating System - HP-UX
1748159 Members
3973 Online
108758 Solutions
New Discussion юеВ

How many tables in a manageable database

 
Graham Cameron_1
Honored Contributor

How many tables in a manageable database

My application has just exceeded the 1000 table mark, and that's just the main schema.
The development teams are still throwing tables at me (DBA) and growth of table numbers is around 40% pa.
If ever I question the need for a new table I get accused of hampering progress.
Any opinions on how many tables is manageable/supportable/sensible ??
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
4 REPLIES 4
Steve Steel
Honored Contributor

Re: How many tables in a manageable database

Hi

depends a bit on your machine but try to get through to your users

DBA Rules ok. Make them justify the tables.

http://www.orafaq.com/faq.htm is a very good data source and should help you make up your mind about limits.

Regards

Steve Steel

Quote of the moment
-------------------
"We are drowning in information but starved for knowledge."
-- John Naisbitt
If you want truly to understand something, try to change it. (Kurt Lewin)
Robert Thorneycroft
Valued Contributor

Re: How many tables in a manageable database

I run an SAP system sitting on top of an Oracle database, currently there are 19,234 tables on the system with 22,484 indexes to support them.

You may find it actually gets easier to run your system with more tables in some regards, ie. the more tables you have the more storage you require which gives you a better chance at being able to eliminate hot spots by migrating particularly heavily hit tables into low activity areas.

Reorganising the 1.5TB of data on the other hand takes a little while ;)

In answer to your question however you might find that at some point a few Oracle tools might help out, my personal choice are the Quest tools Spotlight SQLab and Space Manager, all of which are excellent for management/tuning and are relatively cheap as database tools go.

Regards,

Robert Thorneycroft
Dave Chamberlin
Trusted Contributor

Re: How many tables in a manageable database

Is this a "homegrown" app? In my opionion - the biggest mistakes are made at the level of data storage. The better the end product is understood and the better the programmer - the SIMPLER the solution. Otherwise this ap sounds very complicated and will be difficult to maintain - not from your (DBA) standpoint but from those supporting the app. We run many Oracle applications with many thousands of tables. It ain't pretty. I wouldn't call it difficult to maintain as much as difficult to fathom. If you wanted anything more complicated, you would have to use a government committee. I can't wait for 11i...

A. Clay Stephenson
Acclaimed Contributor

Re: How many tables in a manageable database

There is no magic answer to this question other than "as many as needed". The good news is that in any event it's only a small number of physical files to you (under most Database engines) so let them deal with the design issues.

As a more or less extreme example, our Baan ERP system utilizing an Oracle database currently has over 60K tables and over 250K indices. Moreover, the table and column names are almost gibberish so that unless you have the meta-data that describe these columns and tables, it's very difficult to run queries against the database itself.

I would say that a 40% annual growth rate indicates a flaw in the design process. I suppose that I suffer a bit from radical idea that you design first and then code. In the ideal development world, there would be no new tables needed. In the real development world, a moderate number of new tables would be expected but your growth rate indicates that not nearly enough effort went into the initial design phase.
If it ain't broke, I can fix that.