Re: [GENERAL] Physical Database Configuration - Mailing list pgsql-hackers
From | AgentM |
---|---|
Subject | Re: [GENERAL] Physical Database Configuration |
Date | |
Msg-id | A8A8AF6A-A73D-11D7-818C-0030657192DA@cmu.edu Whole thread Raw |
In response to | Re: [GENERAL] Physical Database Configuration (johnnnnnn <john@phaedrusdeinus.org>) |
Responses |
Re: [GENERAL] Physical Database Configuration
|
List | pgsql-hackers |
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
pgsql-hackers by date: