Re: [GENERAL] Physical Database Configuration

On Wednesday, June 25, 2003, at 12:10 PM, johnnnnnn wrote:
> On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:
>> Has anyone looked at the syntaxes used by other databases to control
>> tablespaces (Oracle, DB2, etc)?  I have no strong desire to
>> slavishly follow Oracle, but it would be a shame to miss out on any
>> good ideas.
>
> DB2:
> CREATE TABLESPACE spacename ...
> ALTER TABLESPACE spacename ...
> RENAME TABLESPACE spacename TO newspacename
> CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN 
> spacename]
> "INDEX IN" and "LONG IN" refer to the tablespace used to store the
> indices and the LOB values for that table, respectively.
> The create syntax revolves around nodegroups and such which are DB2
> concepts i don't fully grok (i'm a programmer, not a DBA).
> But, yeah, those are really the only entrypoints. You can't create an
> index in a specific tablespace -- it will go wherever the table is set
> to put indices.
> I like the syntax ("IN spacename"), though. It's simple and
> straightforward.
Oracle 8 examples:

CREATE TABLE name(dud INTEGER) storage 8M next 4M pctincrease 0 
minextents 1 maxextents 200 tablespace TSNAME;

where storage, next, pctincrease, minextents, and maxentents are table 
space usage granularity requests.

CREATE TABLESPACE TSNAME datafile '/path/file.dbf' size 100M, 
'/another/file.dbf' size 50M default storage (initial 1M next 1M 
pctincrease 0 maxentents 249);

where each comma-delimited item is an "extent"- simply put, a block 
which Oracle is allowed to use for storage.

ALTER TABLESPACE TEMP ...;

allows for arbitrary placement of temporary table storage (higher-speed 
area?)

ALTER TABLESPACE TSNAME default storage (...);

changes settings for tablespace.

ALTER TABLESPACE TSNAME coalesce;

more extent "optimization" granularlity.

CREATE ROLLBACK SEGMENT R1 tablespace TSNAME2 storage (...);

which allocates space for a rollback area.

ALTER ROLLBACK SEGMENT R1 offline/online;

allows for cleanup of rollback segment's area.

CREATE TABLE name(dud INTEGER PRIMARY KEY USING INDEX ....);

allows for pointing an index to a tablespace.

CREATE INDEX ind ON table(col) global/local partition by range(col)
(partition PART1 values less than (11) tablespace TS1,
partition PART2 values less than (21) tablespace TS2,
....
partition PART3 values less than (MAXVALUE) tablespace TS3);

allows for a partioned index across tablespaces, but whose grammar 
setup could use some work.

ALTER TABLE table MODIFY PARTITION part1storage (...) logging/nologgingMOVE PARTITIONADD PARTITION part1 values less
than(...)DROP PARTITIONTRUNCATE PARTITIONSPLIT PARTITION ... INTO ...EXCHANGE PARTITION
 

a nasty alter table command related to partitions (a tablespace can 
have multiple partitions).

I post this just so there a flavor of how many "optimization" options 
are available in Oracle 8. Personally, I would prefer not to have so 
many options but this listing should help folks so they don't paint 
themselves into a corner while coding on the tablespaces.

All examples courtesy of "Oracle 8: Advanced Tuning and 
Administration", Aronoff, Eyal, et al.  ASIN: 0078822416 (c) 1998. 
(perhaps a little outdated)

><><><><><><><><><
AgentM
agentm@cmu.edu