Stephane,
Just thinking of a few potential issues (I've worked in a warehouse set up
that way):
CONS
1) Duplicate objects (same name, different schema) - can be accidentally
created and cause a lot of confusion
2) Need to prefix objects with schema or create synonyms to get data from
one schema to the next
3) May hit a few errors regarding permissions across schemas - all can be
worked around though
PROS
1) Provides logical separation between objects
2) May make it easier to provide objects for adhoc reporting, etc (users
connected to dw cannot see stage tables)
3) In a complex environment it can help enforce good standards - like a dw
processing job should be able to read from stage but not update it (this
depends on the application design though)
I would recommend having a strong naming convention, even if using multiple
schema's, so that the purpose of each table is understood without knowing
which schema it is in - this helps fix con#1. I think the logical
separation and ability to restrict access to end users are good reasons for
choosing multiple schema's though.
I think schema's are a little like folders in a filesystem. Therefore your
question is similar to "should I put all of my documents in a single folder
or should I create folders for design, construction and testing. However I
do understand the issue is slightly more complex than that.
Regards,
Mark.
paquette stephane
<stephane_paquette@ To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
yahoo.com> cc:
Sent by: Subject: What do you think about
multiples schemas versus 1 schema
[EMAIL PROTECTED]
01/11/2002 07:09
Please respond to
ORACLE-L
Hi,
What do you think about multiples schemas versus 1
schema, pros and cons.
For example, having the schemas: stage, dw, metadata
or only dw.
I have my ideas but I want to check if do not missed
anything.
TIA
=====
St�phane Paquette
DBA Oracle et DB2, consultant entrep�t de donn�es
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]
__________________________________________________________
L�che-vitrine ou l�che-�cran ?
magasinage.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
