The fragmentation feature gives you more control over where
the database stores data. You are not limited to specifying the locations
of individual tables and indexes. You can also specify the location
of table and index fragments, which are different parts
of a table or index that are on different storage spaces.
You can fragment a table in the following ways:
Fragment a table over more than one dbspace. However, you cannot
put fragments into dbspaces that have different page sizes. All fragments
must have the same page size.
Create multiple partitions of a fragmented table within a single
dbspace if the fragmented table uses an expression-based or round-robin
distribution scheme.
You can fragment the following storage spaces:
Dbspaces
Sbspaces
Usually you fragment a table when you initially create it. The
CREATE TABLE statement takes one of the following forms:
CREATE TABLE tablename ... FRAGMENT BY ROUND ROBIN IN dbspace1,
dbspace2, dbspace3;
CREATE TABLE tablename ...FRAGMENT BY EXPRESSION
<Expression 1> in dbspace1,
<Expression 2> in dbspace2,
<Expression 3> in dbspace3;
The FRAGMENT BY ROUND ROBIN and FRAGMENT BY EXPRESSION keywords
refer to two different distribution schemes. Both statements associate
fragments with dbspaces.
If you set
the AUTOLOCATE configuration parameter or session environment variable
to a positive integer, and you do not specify a location for the table,
new tables are fragmented in round-robin order in dbspaces that are
chosen by the database server.
When you fragment a table, you can also create multiple partitions
of the table within the same dbspace, as shown in this example:
CREATE TABLE tb1(a int)
FRAGMENT BY EXPRESSION
PARTITION part1 (a >=0 AND a < 5) in dbs1,
PARTITION part2 (a >=5 AND a < 10) in dbs1
...
;
The following figure illustrates the role of fragments in specifying
the location of data.Figure 1: Dbspaces
that link logical units (including table fragments) and physical units
of storage