Re: Schemas / instances - question

On Fri, 28 Sep 2001, KenParis_at_lvcm.com wrote:

> The application in question currently runs on IDMS and has
> identical "schemas" which are segregated into different
> physical databases under the same DBMS.

In this case, it looks like the Oracle term schema is analogous to the IDMS term database. The IDMS "identical schemas" seems to be saying "identical ddl", ie, in the IDMS DMBS, you created the same set of tables and indexes for two different databases. In Oracle, two different schemas would have identical table and index structures.

> There is an IDMS technique which allows the program to, in
> essence, connect to the specific database it wants to using an
> input parm.

Here, you would connect to oracle logging into a specific schema.

From a UNIX shell prompt it looks like

sqlplus schema/password_at_instance

Therefore, you would need to parameterize the schema and password to get this affect, but yes, no problem here.

> We're also concerned with how the access is optimized, since
> the statistics for the database may be different (e.g. in one
> physical database the ration of departments to employees might
> be 1 to 1000, while another might have a ratio of 1 to 4).

Oracle handles skewed data quite well with the CBO. Also, Oracle will not care that two tables have the same name in two different schemas (They are _completely_ different objects because they have different object ids.). It will create a plan for access to either and with the correct tuning different skewing will be taken into account for its plan.

-- 
Galen Boyer
I have nightmares about Tinky Winky beating me with his red bag.