Re: tablespaces and schemas

In DB2, on the mainframe, tablespaces are created to "house" the tables.  Both tablespaces and indexspaces (created when you create an index on a table) are "physical" items in that they physically reside on disk and when created have size specified (primary quantity and secondary quantity) as well as other pieces of information.  When a table is created one part of the create statement specifies which tablespace the table belongs to and tablespaces are associated with databases (databases themselves are just logical objects and have no physical characteristics).  SQL has no concept of tablespaces or indexspaces.  SQL references tables and fields within tables.  The "black box" in the background knows where to go to find the tables or indexes physical location on disk.

Duane

-----Original Message-----
From: Andrew Rawnsley [mailto:ronz@ravensfield.com]
Sent: Wednesday, June 09, 2004 2:53 PM
To: Dennis Gearon
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] tablespaces and schemas

On Jun 9, 2004, at 5:15 PM, Dennis Gearon wrote:

> This post is as much about getting some questions answered as leaving
> the following definitions in the archives for the next person.
>
> After a quick perview of the web, I came up with the following:
>
> tablespaces are a hardware issue, and totally transparent to SQL
> execution. It is for optimization for IO, recovery, and separating
> user and application usage amongst disks even in the same databases.
>

A bit more like database configuration based upon your hardware/design
requirements and availability, but yes, its transparent to the guy
writing the SQL. In Oracle (Sorry to use the 'O' word on the list...),
you specify a tablespace when you create a table (or it uses a default
one), but after that it only matters to the DBA actually running the
installation.

> schemas are a logical issue, and NOT transparent to the SQL. If
> schemas are involved, the SQL needs to know which schema tables are in
> to access them.
>

Yep.

> My questions are:
>    1/ Am I right/

As much as makes no odds, yes.

>    2/ is the use of the '.' character standard across all databases as
> a schema delimiter, i.e. SELECT * FROM
> {schemaname.tablename.columnname;} ?

Yep.

>    3/ Once a user/dba gets down to the actual SQL, and past all the
> bl***ng Oracle Obfuscation(TM), does Oracle do the same thing with
> schemas that Postgres does, i.e. the aforementioned '.' separator?

Schemas are users in Oracle, but the net effect to the SQL author is
the same. 'SELECT * FROM SERVICES.USERS' is the same, just that
'SERVICES' is a user in oracle (although referred to as a schema, and
you have to do a 'CREATE SCHEMA AUTHORIZATION blablabla' to get
anything to work. See your Oracle Obfuscation(TM) documentation, which
of course will tell you nothing without the decoder ring that comes
with a $10,000 service contract), and a schema in Postgres.  Sybase and
DB2 IIRC float in the middle with the terminology, but again, same
effect to the author (the poor sod actually implementing the thing has
to pay attention to all the differences, of course).

>
> I am building an application that I want to work on Postgres, IBM DB2,
> Oracle, MSSQL, et.al.
>

If you keep your SQL generic, its not really that hard to do if you
have/write decent middleware. The temptation is always to cheat and
take advantage of native doodads to help things along.

> TIA, y'all.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match