Re: Databases and Instances

On Mon, 03 Apr 2000 00:29:38 GMT, Joe Maloney <mpir_at_compuserve.com> wrote:

>PMJI
You're welcome.

>
>I work with Oracle, Informix and MS-SS. From my perspective,
>essentially what is being addresssed is that Oracle has tablespaces
>while the others, as I understand it, have databases.

Not entirely correct. Tablespaces are purely for data management and have nothing to do with who owns what.

>
>What I hear you asking for is for owner smith to have two tables name
>wesson, one in a database named oils, the other in a database named
>guns. But both databases are in the same instance of, say, MS SQL-
>Server. You end up addressing the tables as smith.oils.wesson and
>smith.guns.wesson.
>

I suppose. I don't like the idea of the naming of the table reflecting where it is. That is not "location transparency" and "storage vs referencing isolation" at all.

>In Oracle, you can achieve the same split by have smith own a table
>named wesson, stored in partitions oil and guns based on a product key.
>Oil and Guns would be separate tablespaces. Orace would address the
>table as Smith.Wesson, differentiating by product key. You then don't
>have to worry about partition.
>

Not at all. In ORACLE, you have two users ("smith" and "oil") that own two tables with the same name "wesson". These owners are called "schemas".

Then ANY other user, let's assume JDoe, wants to access the "smith" version of "wesson. Easy: code the table name as "smith.wesson".

If JDoe then wants the oily one, he codes "oil.wesson". He can use them in the same SQL statement too. And that has NOTHING to do with where the DBA decided to store either of "smith.wesson" and "oil.wesson".

They can be on the same tablespace, different tablespaces, different instances even. The naming rule is still the same. And it doesn't change one bit if the DBA tomorrow decides (or needs) to move things around disks, tablespaces, instances.

Remember, location transparency. JDoe does not have to know what lives where in what disk or "database". All he has to know is the schema name and the table name. Everything else is up to the DBA.

Of course, some DBA's may have a less open policy. Entirely up to them again.

>I would argue that Oracle's approach gives you better enterprise data
>consistency and integrity (single database), but it is debatable.
>

Or multiple database. Logical names have nothing to do with physical location in ORACLE. Since about 1982. Flexible data location and total application transparency.

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Mon Apr 03 2000 - 00:00:00 CDT