Storing multiple named fragments in a single dbspace
For fragmented tables that use expression-based, interval, list, or round-robin distribution schemes, you can create named fragments that can be located within a single dbspace.
Storing multiple table or index fragments in a single dbspace improves query performance over storing each fragment in a different dbspace and simplifies management of dbspaces.
Suppose you are creating a fragmented table using an expression-based distribution scheme in which each expression specifies the data sets that are placed in particular fragments. You might decide to separate the data in the table with data from one month in one dbspace and data from the next 11 months in 11 other dbspaces. However, if you want to use only one dbspace for all the yearly data, you can create named fragments so the data for each month is stored in one dbspace.
If you create a fragmented table with named fragments, each row in the sysfragments system catalog table contains a fragment name in the partition column. If you create a fragmented table without named fragments, the name of the dbspace is in the partition column. The flags column in the sysfragments system catalog table tells you if the fragmentation scheme has named fragments.
You can create tables and indexes with named fragments, and you can create, drop, and alter named fragments using the PARTITION keyword and the fragment name.
CREATE TABLE tb1(a int)
FRAGMENT BY EXPRESSION
PARTITION part1 (a >=0 AND a < 5) IN dbspace1,
PARTITION part2 (a >=5 AND a < 10) IN dbspace1
...
;
ALTER FRAGMENT ON TABLE tb1 INIT FRAGMENT BY EXPRESSION
PARTITION part_1 (a >=0 AND a < 5) IN dbspace1,
PARTITION part_2 (a >=5 AND a < 10) IN dbspace1;
ALTER FRAGMENT ON INDEX ind1 INIT FRAGMENT BY EXPRESSION
PARTITION part_1 (a >=0 AND a < 5) IN dbspace1,
PARTITION part_2 (a >=5 AND a < 10) IN dbspace1;
PARTITION BY EXPRESSION
keywords in place
of the FRAGMENT BY EXPRESSION
keywords in the CREATE
TABLE, CREATE INDEX, and ALTER FRAGMENT ON INDEX statements, as shown
in this example:ALTER FRAGMENT ON INDEX idx1 INIT PARTITION BY EXPRESSION
PARTITION part1 (a <= 10) IN idxdbspc1,
PARTITION part2 (a <= 20) IN idxdbspc1,
PARTITION part3 (a <= 30) IN idxdbspc1;
CREATE TABLE t1 (c1 int) FRAGMENT BY EXPRESSION
(c1=10) IN dbs1,
(c1=20) IN dbs2;
ALTER FRAGMENT ON TABLE t1 MODIFY dbs2 TO PARTITION part_3 (c1=20)
IN dbs1
CREATE TABLE t1 (c1 int) FRAGMENT BY EXPRESSION
(c1=10) IN dbs1, (c1=20) IN dbs2, (c1=30) IN dbs3
CREATE INDEX ind1 ON t1 (c1) FRAGMENT BY EXPRESSION
(c1=10) IN dbs1, (c1=20) IN dbs2, (c1=30) IN dbs3
ALTER FRAGMENT ON INDEX ind1 INIT FRAGMENT BY EXPRESSION
PARTITION part_1 (c1=10) IN dbs1, PARTITION part_2 (c1=20) IN dbs1,
PARTITION part_3 (c1=30) IN dbs1,
See the IBM® Informix® Performance Guide for more information about fragmentation, including fragmentation guidelines, procedures for fragmenting indexes, procedures for creating attached and detached indexes with named fragments, and examples of SQL statements used to create attached and detached indexes containing named fragments.
See the IBM Informix Guide to SQL: Syntax for more syntax details, including information about named fragments in the GRANT FRAGMENT and REVOKE FRAGMENT statements, and details for using the DROP, DETACH, and MODIFY clauses of the ALTER FRAGMENT statement.