Re: [GENERAL] Physical Database Configuration - Mailing list pgsql-hackers

DB2 looks good. I have horrid, horrid memories of wrestling with the Oracle
"extent" madness.

andrew

AgentM wrote:
> 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
> agentm@cmu.edu
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html