ALTER FRAGMENT Statement Interface

Home | Previous Page | Next Page Purpose-Function Reference > Purpose-Function Flow >

ALTER FRAGMENT Statement Interface

When the database server executes an ALTER FRAGMENT statement, the database server moves data between existing fragments and also creates a new fragment.

The statement in Figure 17 creates and fragments a jobsx index.

Figure 17. SQL to Create the Fragmented Jobsx Index

CREATE TABLEINDEX jobsx on jobs (sstatus file_ops)
   FRAGMENT BY EXPRESSION
      sstatus > 15 IN fragspace2,
      REMAINDER IN fragspace1
   USING file_am

The statement in Figure 18 changes the fragment expression for jobsx, which redistributes the index entries.

Figure 18. SQL to Alter the Jobsx Fragments

ALTER FRAGMENT ON TABLEINDEX jobsx
   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 new 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 Figure 19 through Figure 22 show the separate sequences of purpose functions that create the fragments and distribute the data for the SQL ALTER FRAGMENT statement in Figure 18. 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.

Figure 19 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 19. Getting All the RowsEntries in Fragment 1

Flowchart shows am_open fragspace1 pointing to am_beginscan, which points to am_getnext. If the am_getnext returns MI_ROWS, the scan executes am_getnext again. If am_getnext returns MI_NO_MORE_RESULTS, the scan continues to am_endscan, which points to am_close fragspace1.

In Figure 20, the database server returns the row identifiers that the access method should delete from fragspace1 and insert in fragspace2.

Figure 20. Moving RowsEntries Between Fragments

Flowchart shows "status > 5 and <= 10" pointing to am_open fragspace1, pointing to am_delete rowentry, pointing to am_close fragspace1, pointing to am_open fragspace2, pointing to am_insert rowentry, pointing to am_close fragspace2.

Figure 21 again shows the sequential scan in step 1. This scan returns all the rows from fragment2.

Figure 21. Getting All the RowsEntries in Fragment 2

Flowchart shows am_open fragspace2 pointing to am_beginscan, which points to am_getnext. If the am_getnext returns MI_ROWS, the scan executes am_getnext again. If am_getnext returns MI_NO_MORE_RESULTS, the scan continues to am_endscan, which points to am_close fragspace2.

Figure 22 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 22. Adding and Filling a Fragment

Flowchart shows "sstatus > 10" pointing to am_open fragspace2, pointing to am_delete rowentry, pointing to am_close fragspace2, pointing a box that says fragspace3 exist? If the answer is No, am_create fragspace3 is executed, and the scan proceeds to am_open fragspace3. If the answer is Yes, the scan continues directly to am_open fragspace3. am_open fragspace3 points to am_insert rowentry, which points to am_close fragspace3.

For more information about fragments that a VII-based access method manages, refer to Supporting Fragmentation.