range-segmentation-clause

Range segmentation allows you to segment a projection based on a known range of values stored in a specific column chosen to provide even distribution of data across a set of nodes, resulting in optimal query execution.

Vertica Systems, Inc. recommends that you use Hash Segmentation instead of range segmentation.

Syntax

SEGMENTED BY expression

NODE node VALUES LESS THAN value

NODE node VALUES LESS THAN MAXVALUE

Semantics (Range Segmentation)

SEGMENTED BY expression

is a single column reference to a column in the SELECT list of the CREATE PROJECTION command. Choose a column that has:

INTEGER or FLOAT data type
a known range of data values
an even distribution of data values
a large number of unique data values
Avoid columns that:
are foreign keys
are used in query predicates
have a date/time data type
have correlations with other columns due to functional dependencies.
Segmenting on date/time data types is valid but guaranteed to produce temporal skew in the data distribution and is not recommended. If you choose this option, do not use TIME or TIMETZ because their range is only 24 hours.

NODE node

a symbolic name for a node. You can use a specific node only once in any projection. For a list of the nodes in a database, use the View Database command in the Administration Tools.

VALUES LESS THAN value

specifies that this segment can contain a range of data values less than the specified value, except that segments cannot overlap. In other words, the minimum value of the range is determined by the value of the previous segment (if any).

MAXVALUE

specifies a sub-range with no upper limit. In other words, it represents a value greater than the maximum value that can exist in the data. The maximum value depends on the data type of the segmentation column.

Notes

The SEGMENTED BY expression syntax allows a general SQL expression but there is no reason to use anything other than a single

column reference for range segmentation. If you wish to use a different expression, the following restrictions apply:

All leaf expressions must be either

constants or

column-references toa column in the SELECT list of the CREATE PROJECTION command

Aggregate functions are not allowed
The expression must return the same value over the life of the database.

During INSERT or COPY to a segmented projection, if expression produces a value outside the expected range (a negative value for example), no error occurs, and the row is added to a segment of the projection.
CREATE PROJECTION with range segmentation accepts the deprecated syntax SITE node for compatibility with previous releases.
CREATE PROJECTION with range segmentation allows the SEGMENTED BY expression to be a single column-reference to a column in the projection-column list for compatibility with previous releases. This syntax is considered to be a deprecated feature and causes a warning message. See DEPRECATED syntax in the Troubleshooting Guide.