Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
ALTER FRAGMENT statementUse the ALTER FRAGMENT statement to change the distribution strategy or the storage location of an existing table or index. This statement is an extension to the ANSI/ISO standard for the SQL language.
ADD ClauseUse the ADD clause to add another fragment to a list of fragments of an existing table or index.
Adding an expression-based fragment

Adding an expression-based fragment

Adding a fragment expression to the fragmentation list of an expression-based distribution scheme can relocate records from existing fragments into the new fragment. When you insert a new fragment into the fragmentation list, the database server reevaluates all the data in the existing fragments that follow the new fragment. (The evalpos column value for any fragment in the sysfragments system catalog table indicates the ordinal position of that fragment within the fragmentation list.)

The next statement fragment shows the original expression definition:

FRAGMENT BY EXPRESSION 
   c1 < 100 IN dbsp1, c1 >= 100 AND c1 < 200 IN dbsp2,
   REMAINDER IN dbsp3

To add another named fragment in dbspace dbsp2 to hold rows for column c1 values between 200 and 299, use the following ALTER FRAGMENT statement:

ALTER FRAGMENT ON TABLE news 
   ADD PARTITION century3 (c1 >= 200 AND c1 < 300) IN dbsp2;

Any rows that were formerly in the remainder fragment but that fit the criteria (c1 >= 200 AND c1 < 300) move to the new century3 fragment in dbspace dbsp2.

If the ALTER FRAGMENT ADD operation results in the redistribution of data rows while the automatic mode for updating distribution statistics is enabled, the database server drops the distribution statistics of the affected fragments, but the table statistics are not dropped. The next query on the table will cause the database server to recalculate the statistics for the same fragments.