Tablespaces [CORE688]

[firebird-automations]

Submitted by: @pcisar

Votes: 9

SFID: 807945#⁠
Submitted By: pcisar

The ability to define/control the location of
data/user tables within a multi-file database.
----------------------
User: awharrison
Logged In: YES
user_id=66088

this is pretty much an "over my dead body" issue. Much
of the
design of InterBase focused on eliminating the need for
table
spaces and other placement. It works well and makes
managing
the database possible without an advanced degree in
Firebird
----------------------
User: nobody
Logged In: NO

I'm not sure but I think this would be usefull if you
could
define diferent files (tablespaces) for tables and indexes
(as it is a good pratice on Oracle).

ex:

1 file containing all objects and a 2nd file containing
all
indexes.

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 10712 ] => Firebird [ 15128 ]

[firebird-automations]

Commented by: Cosmin Apreutesei (cosmin_ap2)

I'd love to have the possibility to put some tables and indexes on a ramdrive device, others on a flash device and others on a hard disk, or I could use a compressed device for some big "archive" tables that I'd fill-up with a trigger.

[firebird-automations]

Commented by: JJ Mondoloni (jjm)

Hi,

I think that separate datas and index on different files and disks will be an best improvment.

By example, i have a big database (approx 18 go) with a big problem (page size 8192).
I can't store more than 55 millions of lines in Table A because my index have more than 3 level leaf

CREATE TABLE TB_URLS_HISTO (
URL_CRC INTEGER NOT NULL DEFAULT 0,
URC_CRC INTEGER NOT NULL DEFAULT 0,
ID_SOURCE INTEGER NOT NULL,
R159_RPLID INTEGER NOT NULL);

/* Primary keys definition */

ALTER TABLE TB_URLS_HISTO ADD CONSTRAINT PK_TB_URLS_HISTO PRIMARY KEY (ID_SOURCE, URL_CRC);

/* Indices definition */

CREATE UNIQUE INDEX IDX_TB_URLS_HISTO_URC_CRC ON TB_URLS_HISTO (URC_CRC, R159_RPLID);
CREATE UNIQUE INDEX IDX_TB_URLS_HISTO_URL_CRC ON TB_URLS_HISTO (URL_CRC, R159_RPLID);
CREATE UNIQUE INDEX R159_RPLID ON TB_URLS_HISTO (R159_RPLID);

SET TERM ^ ;

Where URC_CRC and Id_SOURCE can be repeated and URL_CRC unique at 98%

Solution is easy, increase my page size.. but i have many table with hundred of thousand blobs

without increase my page size, i had some table with blobs filled at 2% (more modifications Insert / update / delete..)

[firebird-automations]

Commented by: Ivan (patuljak)

http://www.postgresql.org/docs/8.3/interactive/manage-ag-tablespaces.html