SET_DEPOT_PIN_POLICY_PARTITION

Eon Mode only

Pins the specified partitions of a table or projection to a subcluster depot, or all database depots, to reduce exposure to depot eviction.

Partition groups can be pinned only if all partitions within the group are pinned individually. If you alter or remove table partitioning, Vertica drops all partition pin policies for that table. The table's pin policy, if any, is unaffected.

For details on pinning policies and usage guidelines, see Pinning Depot Objects.

Syntax

SET_DEPOT_PIN_POLICY_PARTITION ( '[[database.]schema.]object‑name', 'min‑range‑value', 'max‑range‑value' [, 'subcluster' ] )  )

Parameters

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
object-name

Table or projection to pin. If you specify a projection, it must store the partition keys.

After you pin a table or one of its projections to a subcluster, you cannot subsequently pin any of its partitions to that subcluster. Conversely, you can pin one or more table partitions to a subcluster, and then pin the table or one of its projections to that subcluster.
min‑range‑value
max‑range‑value

Minimum and maximum value of partition keys in object-name to pin, where min‑range‑value must be ≤ max‑range‑value. To specify a single partition, min‑range‑value and max‑range‑value must be equal.

If partition pinning policies on the same table specify overlapping key ranges, Vertica collates the partition ranges. For example, if you create two partition policies with key ranges of 1-3 and 2-4, Vertica creates a single pinning policy with a key range of 1-4.
subcluster Sets this pinning policy on the subcluster depot. If you omit this parameter, the policy is set on all database depots.

Privileges

Superuser

Precedence of Pin Policies

In general, partition management functions that involve two partitioned tables give precedence to the pin policy of the target table, as follows:

Function Application of pin policy
COPY_PARTITIONS_TO_TABLE Partition-level pinning is reliable if the source and target tables have pin policies on the same partition keys. If the two tables have different pin policies, then the partition pin policies of the target table apply.
MOVE_PARTITIONS_TO_TABLE Partition-level pin policies of the target table apply.
SWAP_PARTITIONS_BETWEEN_TABLES Partition-level pin policies of the target table apply.

For example, the following statement copies partitions from table foo to table bar:

=> SELECT COPY_PARTITIONS_TO_TABLE('foo', '1', '5', 'bar');

In this case, the following logic applies:

  • If the two tables have different partition pin policies, then the pin policy of target table bar for partition keys 1-5 applies.
  • If table bar does not exist, then Vertica creates it from table foo, and copies foo's policy on partition keys 1-5. Subsequently, if you clear the partition pin policy from either table, it is also cleared from the other.

See Also

CLEAR_DEPOT_PIN_POLICY_PARTITION