Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Tablespaces [CORE688] #1055

Open
firebird-automations opened this issue Sep 17, 2003 · 4 comments
Open

Tablespaces [CORE688] #1055

firebird-automations opened this issue Sep 17, 2003 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant