Re: [HACKERS] [GENERAL] Physical Database Configuration

AgentM Wed, 25 Jun 2003 11:48:12 -0700

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/nologging
        MOVE PARTITION
        ADD PARTITION part1 values less than (...)
        DROP PARTITION
        TRUNCATE PARTITION
        SPLIT 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
[EMAIL PROTECTED]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] [GENERAL] Physical Database Configuration

Reply via email to