Re: Question about database design and Max number of tables in Oracle

Hi,

7000 tables only?

I had to build a knowledge system that stores its facts as tables and rules as views. I've tested this before to prevent me from a dead-end solution. The testserver was a penguin, a 600 MHz Pentium with 512 Meg RAM, 160 UWide SCSI-disks, not tuned.
O.K., the 'You die too easily' method:
I've created 111000 tables (done with create table select), some with 3 columns and rows, some with 5, the biggest had 5000 rows. After that, I've created 300000 views (you're counting the zeros correctly: three-hundred-thousand). The first half of them joined some rows from two tables and select a few rows on it. The next view-layer joined two 1st-layer-views and so on. I've created six layers. After that I have shutdown the server and restart again. NO DELAY!
A table randomly selected appears immediately in sqlplus, the views on the sixth layer -O.K. needed 1-2 seconds. No performance holes occured. The data increases to 6 gig, the system-ts finishes by 1.5 gig.

In my opinion, perform your 7000-table-system, but tune your system-ts accordingly and consider the probably hughe administration task that will waiting for you.

And a tip: If you want to drop this user -build a new database and drop the old one. It's the FASTEST way, really!

oli.

On Don, 05 Okt 2000, Manish Asnani wrote:
> Hi Oracle gurus,
>
> We have a database design question which has me challenged.
>
> Here is the situation :
> The goal of the system is to be able to model any possible item. This is a
> consumer oriented site (kind of like Priceline's Perfect Yard Sale) where a
> user can enter what they have to be sold in a garage sale. They can also
> enter what they are looking for. An example is that a user might be
> interested in buying a 2 yr old Nissan Altima which is white or grey color
> and mileage is between 1000 and 23000. Another user might want a CD or a
> book or any consumer type item.
> So the main challenge is to create the data model which will accommodate
> this requirement. What makes this requirement more challenging is that we
> need to be able to search against this data in real time. A user might want
> to search the inventory, for example, to find all cars which have between
> 2000 and 5000 miles.
>
> One approach which helps solve the problem is to create a schema which
> stores the metadata for all possible items which can be accepted. So, this
> metadata would store definitions of all possible items such as Cars,
> Cameras, Music,........etc. The main problem with that is the efficiency is
> very bad. One brute force method is to create individual tables for all
> possible item types which we can define. So we will have tables for Cars,
> Cameras, Music etc. The main problem here is we would be dealing with a
> large number of tables. Our analysts project about 7000 tables. My question
> is - how does Oracle handle these many number of tables. What are the max
> numbers. If anyone has any experience in this kind of a situation, I would
> be glad to receive any tips.
>
> Thanks for your time.
>
> - Manish Asnani
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Manish Asnani
> INET: MAsnani_at_Bonanza.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
---

Oliver Artelt, System- und Datenbankadministration
---------------------------------------------------------------
  cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
   email: oli@cubeoffice.de # web: http://www.cubeoffice.de