When the database server executes an ALTER FRAGMENT statement,
the database server moves data between existing fragments and also
creates a fragment.
The statement in the following figure creates and fragments
a jobs table.Figure 1: SQL to create the fragmented jobs table
CREATE TABLE jobs (sstatus file_ops)
FRAGMENT BY EXPRESSION
sstatus > 15 IN fragspace2,
REMAINDER IN fragspace1
USING file_am
The statement in the following figure changes the fragment
expression for jobs, which redistributes the table entries.Figure 2: SQL to
alter the jobs fragments
ALTER FRAGMENT ON TABLE jobs
MODIFY fragspace1 TO (sstatus <= 5) IN
fragspace1,
MODIFY fragspace2 TO
(sstatus > 5 AND sstatus <= 10) IN
fragspace2,
REMAINDER IN fragspace3
For each fragment that the ALTER FRAGMENT statement specifies,
the database server performs the following actions:
Executes an access-method scan
Evaluates the returned rows to determine
which ones must move to a different fragment
Executes the access method to create a
fragment for the target fragment that does not yet exist
Executes the access method to delete rows
from one fragment and insert them in another
Figures Getting all the rows
in fragment 1 through Adding and filling
a fragment show the separate sequences
of purpose functions that create the fragments and distribute the
data for the SQL ALTER FRAGMENT statement in SQL to
alter the jobs fragments. The database server
performs steps 1, 2, and 3 to move fragments from fragspace1 to fragspace2 and
then performs steps 1 through 3 to move fragments from fragspace2 to fragspace3.
The following figure shows the sequential scan in step 1, which returns all
rows from the fragment because the scan descriptor contains a NULL-valued
pointer instead of a pointer to a qualification descriptor.Figure 3: Getting all the rows
in fragment 1
In the following figure, the database server returns the
row identifiers that the access method should delete from fragspace1 and
insert in fragspace2. Figure 4: Moving rows between fragments
The following figure again shows the sequential scan in
step 1. This scan
returns all the rows from fragment2. Figure 5: Getting All the Rows in Fragment 2
The following figure shows steps 3 and 4. The database server
returns the row identifiers that the access method should delete from fragspace2 and
insert in fragspace3. The database server does not have fragspace3,
so it executes am_create to have the access method
create a fragment before it executes am_insert. Figure 6: Adding and filling
a fragment