Fragmenting by HYBRID (XPS)

The HYBRID clause allows you to apply two distribution schemes to the same table. You can use a combination of hash- and expression-distribution schemes or a combination of range-distribution schemes on a table. This section discusses the hash and expression form of hybrid fragmentation. For details of range fragmentation, see RANGE Method Clause (XPS).

In hybrid fragmentation, the EXPRESSION clause determines the base fragmentation strategy of the table, associating an expression with a set of dbspaces (dbspace, dbslice, or dbspacelist format) for data storage. The hash columns determine the dbspace within the specified set of dbspaces.

When you specify a dbslice, the database server fragments the table across the dbspaces that make up the dbslice. Similarly, if you specify a dbspace list, the database server fragments the table across the dbspaces in that list. In the next example, my_hybrid distributes rows based on two columns of the table. The value of col1 determines in which dbslice the row belongs.

The hash value of col2 determines which dbspace (within the previously determined dbslice) to insert into.

RANGE Method Clause (XPS)

In Extended Parallel Server, you can use a range-fragmentation method as a convenient alternative to fragmenting by the EXPRESSION or HYBRID clauses. This provides a method to implicitly and uniformly distribute data whose fragmentation column values are dense or naturally uniform.

In a range-fragmented table, each dbspace stores a contiguous, completely bound and non-overlapping range of integer values over one or two columns. In other words, the database server implicitly clusters rows within the fragments, based on the range of the values in the fragmentation column.

RANGE Method Clause:
 
                                                           .-,-------.
                                            (1)            V         |
|--+-RANGE--(--column--| Range Definition |-------)--IN--+---dbspace-+-+--+-----------------------+-+--|
   |                                                     '-dbslice-----'  '-REMAINDER IN--dbspace-' |
   '-HYBRID--| First Range Specification |--| Second Range Specification |--------------------------'
 
First Range Specification:
 
                                               (1)
|--+-(--RANGE--(--column--| Range Definition |-------)--)-+-----|
   '-(--RANGE--(--column--)--)----------------------------'
 
Second Range Specification:
 
                                              (2)
|--+-RANGE--(--column--)--| Range IN Clause |---------------------------------+--|
   |                                        (1)                          (2)  |
   '-RANGE--(--column--| Range Definition |-------)--| Range IN Clause |------'

Notes:

See page Range Definition
See page Range IN Clause

Element	Description	Restrictions	Syntax
column	Column on which to apply the fragmentation strategy	Must be in the current table and must be of data type INT or SMALL INT	Identifier, p. Identifier
dbslice	Dbslice that contains the dbspaces where the table fragments reside	Must exist when you execute the statement	Identifier, p. Identifier
dbspace	Dbspace that contains the table fragment	Must exist when you execute the statement. The maximum number of dbspaces is 2048.	Identifier, p. Identifier

For hybrid strategies with two range definitions, the second column must have a different column name from the first. For hybrid strategies with exactly one range definition, both occurrences of column must specify the same column.

If you list more than one dbslice, including a remainder dbslice, each dbslice must contain the same number of dbspaces. Unless you are specifying the dbspace in the REMAINDER option, you must specify at least two dbspaces.