Hi Roman!
First of all, thanks for the development and offering it to Firebird.
Do you plan to partition the data depending on field value?
Oracle range partitioning example:
CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
);
Oracle list partitioning example:
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);
https://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm#i468016
ERP systems usually contain data from several years in one table (e.g. general
ledger), but after a year-end, the data from the previous years are rarely
queried / modified, so they do not necessarily have to be on a quick-access
disk.
András
-----Original Message-----
From: Roman Simakov [mailto:roman.sima...@gmail.com]
Sent: Wednesday, October 6, 2021 5:32 PM
To: For discussion among Firebird Developers
<firebird-devel@lists.sourceforge.net>
Subject: [Firebird-devel] Tablespaces proposal
Hello, team!
As you might know Red Soft has implemented Tablespace support for RedDatabase 4
which is based on Firebird 4 code base.
I hope we start working on a merge request for Firebird 5 or later but
previously I would like to get agreement about basic user visible things like
the syntax and ODS.
================PROPOSAL======================
GOALS
======
1) Extend the current limits on database size
2) Keep non active parts of a database on slow disks (having big volume)
3) Split indices from the database
etc
SYNTAX
=======
1. CREATE TABLESPACE <TS NAME> FILE '/path/to/file'
2. ALTER TABLESPACE <TS NAME> FILE '/path/to/file'
3. DROP TABLESPACE <TS NAME> [INCLUDING CONTENTS]
If the tablespace contains some database objects the behaviour depends on
INCLUDING CONTENTS clause. if it is specified all database objects in the
tablespace will be dropped as well. Otherwise there will be an error.
4. CREATE TABLE …
TABLESPACE <TS NAME>
5. ALTER TABLE <TABLE NAME> ALTER TABLESPACE <TS NAME>
Data of the table will be moved to the specified tablespace.
6. ALTER TABLE <TABLE NAME> DROP TABLESPACE
Data of the table will be moved to the main database.
7. CREATE INDEX … TABLESPACE {<TS NAME> | DEFAULT}
The index will be created:
- in the main database file if TABLESPACE is omitted.
- in the table tablespace for TABLESPACE DEFAULT.
- or in the specified tablespace.
8. ALTER INDEX <INDEX NAME> ALTER TABLESPACE <TS NAME>
Data of the index will be moved to the specified tablespace.
9. ALTER INDEX <INDEX NAME> DROP TABLESPACE
Data of the index will be moved to the main database.
ODS CHANGES
=============
A new table RDB$TABLESPACES:
RDB$TABLESPACE_ID - SMALLINT
RDB$TABLESPACE_NAME - CHAR (63)
RDB$SECURITY_CLASS - CHAR (63)
RDB$SYSTEM_FLAG - SMALLINT
RDB$DESCRIPTION - BLOB SUBTYPE TEXT SEGMENT SIZE 80
RDB$OWNER_NAME - CHAR (63)
RDB$FILE_NAME - VARCHAR (255)
RDB$OFFLINE - SMALLINT
RDB$READ_ONLY - SMALLINT
A new field in RDB$INDICES:
RDB$TABLESPACE_NAME - CHAR (63)
A new field in RDB$RELATION_FIELDS:
RDB$TABLESPACE_NAME - CHAR (63)
New fields in RDB$RELATIONS:
RDB$TABLESPACE_NAME - CHAR (63)
RDB$POINTER_PAGE - INTEGER
RDB$ROOT_PAGE - INTEGER
UTILITIES
========
Logical backup
--------------------
gbak -b works as usual for now. It gets data from a database transparently
working with tablespaces.
Logical restore
--------------------
gbak -c -TABLESPACE_MAP(PING_FILE) /path/to/file/map/tablespaces
The option TABLESPACE_MAP(PING_FILE) specifies a path to the file which maps
tablespace names on file names. For example,
TS1 /path/to/tablespace1.dat
TS2 /path/to/tablespace2.dat
It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every tablespace.
SOME DETAILS
=============
pag_header in every tablespace is reserved and may be replaced by a new page
type.
pag_scns and pag_pip are located in every tablespace pag_root is located in the
tablespace where a table is located
It's possible to create up to 253 tablespaces.
==================END=========================
First of all, please let me know whether you agree or not with SYNTAX and ODS
parts. Other opinions and suggestions are welcome as well.
--
Roman Simakov
https://reddatabase.ru
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
