Table Data Partitioning

Table Data Partitioning dinther Thu, 06/27/2019 - 15:03

Tables can be merged into one virtual merge table to provide Data Partitioning. Optionally the merge table can have a table partitioning scheme allowing data to be partitioned automatically by column or expression and on distinct values or value ranges upon insert. The partitioning schemes are stored in below system tables:

sys.table_partitions
name type references description
"id" INTEGER   The unique internal identifier for the table partition.
"table_id" INTEGER sys.tables.id The merge table id this partition belongs to.
"column_id" INTEGER sys.columns.id The merge table's column id by which the partitioning data is selected OR null when an expression is used.
"expression" VARCHAR   The merge table's expression by which the partitioning data is selected OR null when a column is used.
"type" INTEGER   The partitioning type: 5=By Column Range, 6=By Expression Range, 9=By Column Value, 10=By Expression Value.
sys.range_partitions
name type references description
"table_id" INTEGER sys.tables.id The partition table id this partition belongs to.
"partition_id" INTEGER sys.table_partitions.id The merge table's partition id this range partition belongs to.
"minimum" VARCHAR   The minimum value of the range. Can be null when WITH NULL VALUES is specified.
"maximum" VARCHAR   The maximum value of the range. Can be null when WITH NULL VALUES is specified.
"with_nulls" BOOLEAN   Whether this range also include the NULLs of the Column/Expression.
sys.value_partitions
name type references description
"table_id" INTEGER sys.tables.id The partition table id this partition belongs to.
"partition_id" INTEGER sys.table_partitions.id The merge table's partition id this value partition belongs to.
"value" VARCHAR   The partitioning value. Can be null when WITH NULL VALUES is specified.