DROP Clause

Use the DROP clause to remove an existing fragment from the fragmentation list of a table or index that is fragmented by round-robin. For a table or index that is fragmented by range interval, you can use this clause to drop one or more dbspaces from the list of dbspaces that store system-generated interval fragments.

Use the DETACH clause, rather than the DROP clause, to remove an existing fragment from a table that uses range-inerval fragmentation, such as a rolling window table.

(1)
DROP Clause

1  DROP
2.1 ? PARTITION fragment
2.1 1 INTERVAL? STORE IN ( + , dbspace )
Notes:
  • 1 Range interval fragmentation only
Element Description Restrictions Syntax
dbspace Dbspace that stores system generated fragments Must exist when you execute the statement. Identifier
fragment Name of the fragment Must exist when you execute the statement. For list or range interval fragments, the PARTITION keyword must precede this name. Identifier

If the table is fragmented by expression, you cannot drop a fragment containing data that cannot be moved to another fragment. If the distribution scheme has a REMAINDER option, or if the expressions overlap, you can drop a fragment that contains data. You cannot, however, drop a fragment if the table has only two fragments.

When you want to make a fragmented table nonfragmented, you can use either the INIT clause or the DETACH clause of the ALTER FRAGMENT statement, rather than the DROP clause.

If the fragment was not given a name by the user who created the table or index or added the fragment, then the name of the dbspace is also the name of the fragment. If the fragment is a system-generated range interval fragment of a table or of an index, its name is sys_pevalpos, where evalpos is the sysfragments.evalpos entry for the fragment in the system catalog. If a table and its index use the same range interval fragmentation strategy, each system-generated index fragment has the same identifier as a system-generated fragment of the table.

When you drop a fragment, the database server attempts to move all records in the dropped fragment to another fragment. In this case, the destination fragment might not have enough space for the additional records. If this happens, follow one of the procedures that ALTER FRAGMENT and Transaction Logging describes to increase your available space, and retry the ALTER FRAGMENT operation.

When the DROP clause specifies one or more dbspaces to remove from a range interval fragmentation strategy, those dbspaces are not affected, but the database server moves the data in any fragments of the table or index that are stored in those dbspaces to other available dbspaces. (The range interval fragmentation strategy is also affected, because it no longer includes the specified dbspaces among its storage locations for new system-generated fragments.)

You cannot use the DROP clause to drop a range interval fragment that contains data.

You can use this clause to drop a list fragment that contains data only if a remainder fragment exists to receive the data.

If the fragmented table has fragment level statistics, the ALTER FRAGMENT DROP operation also drops any fragment-level statistic distribution for the fragment been dropped. Table-level statistics, however, are not recalculated. The next explicit or automatic UPDATE STATISTICS operation on the table will rebuild stale fragment-level distributions and merge them to form table level distributions and store the results in the system catalog.

Examples of the DROP clause of the ALTER FRAGMENT statement

The following examples show how to drop a fragment from a round-robin fragmentation list. The first line shows how to drop an index fragment, and the second line shows how to drop a table fragment. 
ALTER FRAGMENT ON INDEX cust_indx DROP dbsp2;

ALTER FRAGMENT ON TABLE customer DROP dbsp1;
The following examples each drop a fragment that was defined by list fragmentation strategies. The first line shows how to drop a table fragment, and the second line shows how to drop an index fragment. 
ALTER FRAGMENT ON TABLE T2 DROP PARTITION part4;

ALTER FRAGMENT ON INDEX idx2 DROP PARTITION part4;
In both examples above, the PARTITION keyword is required, and the name of the dropped fragment is part4. If the index idx2 is defined on table T2 and thus the same storage distribution strategy as table T2, the second statement is not necessary, because the database server automatically modifies the fragmentation strategy of an attached index when the table fragmentation list is modified. If these fragments are not empty, the database server moves their data to the remainder fragments (or returns an error, if no remainder fragment exists).
The following examples each drop two dbspaces that store system-defined interval range fragments, as defined by range interval fragmentation strategies. The first statement drops dbspaces dbs7 and dbs8 from the storage spaces for table fragments, and the second statement drops the same storage spaces for index fragments: 
ALTER FRAGMENT ON TABLE T1 DROP INTERVAL STORE IN (dbs7, dbs8);

ALTER FRAGMENT ON INDEX idx1 DROP INTERVAL STORE IN (dbs7, dbs8);
The PARTITION keyword is again required, and if idx1 is an attached index on table T1, the second statement is not necessary: When the table fragmentation list is modified, the database server automatically modifies the fragmentation strategy of any attached index to match the modified strategy of its table. If these fragments are not empty, the database server moves any fragments from the specified dbs7 and dbs8 dbspaces to other available dbspaces.