Re: Instance with thousands of schemas

From: Matthew Fuller <matthewlf_at_my-deja.com>
Date: Fri, 19 Jan 2001 20:41:35 GMT
Message-ID: <94a8pt$3bb$1@nnrp1.deja.com>

In article <949utl$pkv$1_at_nnrp1.deja.com>,   Mike Krolewski <mkrolewski_at_rii.com> wrote:
> In article <Xns902DA022CCF33audun.jyahoo.no_at_130.133.1.4>,
> audun_j_at_yahoo.no (Audun Jensen) wrote:
> > Hi,
> > I'm starting to migrate a MS SQL Server db to Oracle and have some
> > questions I would like to have comments on.
> > Today our SQL Server database consists of more than 3500 user
 databases and
> > the number is increasing. Each database corresponds to a
 user "project". As
> > some of you probably are aware of, SQL Server have a "database"-
 concept
> > that differs from Oracle's, in the way that one SQL Server instance
 can
> > consist of many databases, each database consisting of users,
 tables,
> > views, procs, triggers etc.. I believe the way to do this using
 Oracle will
> > be to create and implement a schema for each database, containing
 the
> > necessary objects and constraints.
> >
> > This leads to an instance containing thousands of schemas, each
 containing
> > 50-100 tables. In addition I will have to create users who can
 access
 one
> > or more of these "databases" depending on how many "projects" the
 customer
> > creates.
> >
> > Is there anybody who have any experience in this number of schemas?
 What
> > will I have to think of in order to have an instance that is
 operatable and
> > running smooth?
> >
> > TIA
> >
> > AJ
> >
>
> Here is a radical idea. Redesign the database. Thousands of schemes
> seems a bit silly. If each one is really a 'project' and each project
> is the same(?), why have separate schemas? If each project is
> different -- basically, a unique set of tables; the implication is
 that
> you have a thousand database designer/developers. Most likely, the
> projects are very similar or evolved from a parent project. Properly
> structured, one could have a single schema of 'project' with access
> privileges to each 'project'.
>
> Migration could be initially any 'project's that are currently
> underway. Others could be converted later. New projects would be added
> into the new schema.
>
> Working on a design to replicate a bad design, leads to another bad
> design. Garbage in, garbage out. One has too many constraints in doing
> good work to include a poorly concieved prior project.
>
> I have seen many projects fail due to poor design. A good rule of
 thumb
> in any design, if it sounds silly, it often is.
>
> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers
>
> Sent via Deja.com
> http://www.deja.com/
>

I think Michael has some valid points here. Here are some things to think about:

1 - How much data does the average schema have?
2 - Are all (or 99.999%) of your queries indexed?
3 - Does a user tend to only work with their plans and maybe a couple

of others?

If the schema's have a lot of data and are sometimes queried via full table scan, it can be quite logical to have several 100 schemas that look the same structurally. Really this is just another form of partitioning data. It helps if the user only works in their or a few schemas so that you don't have the overhead of a db connect everytime they want to work with a different project. The old project I cited with 1,600 schemas had large amounts of data (some tables had hundreds of thousands of records for just their schema) in each schema; some of the queries involved full table scans; and users tended to work in a given schema for hours at a time, or even all day.

Now, if your situation differs in the other direction (i.e. small data and/or all indexed reads and/or users bouncing all over) then Michael's solution may be more appropriate even if it does involve some migration effort.

HTH. Matt.

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 19 2001 - 14:41:35 CST