Re: Instance with thousands of schemas

Matthew Fuller <matthewlf_at_my-deja.com> wrote in <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/
>

Thanks for the input boys.
The fact is that the schemas is partly different meaning they have approx. 40 tables in common (not the data) and there can be from a couple to 50 - 60 "unique" tables.
The amount of data in these schemas is not overwhelming, I guess an average of about 10000 rows in the most populated tables. The thing is though, that these databases is to reside on SQL Server, Oracle and probably more in the future. And there is a goal to keep the design as "identical" as possible. For this reason, I guess your idea of redesigning the database "falls out" (???).

AJ Received on Tue Jan 23 2001 - 00:40:54 CST