SET_DEPOT_PIN_POLICY_PARTITION

Eon Mode only

Pins table partitions to a subcluster depot, or all database depots, to reduce its exposure to depot eviction. As a best practice, consider only pinning objects that are most active in DML operations and queries.

If too much depot space is claimed by pinned objects, the depot might be unable to handle load operations on unpinned objects. In this case, set configuration parameter UseDepotForWrites to 0, so load operations are routed directly to communal storage for processing. Otherwise, load operations are liable to return with an error.

Syntax

SET_DEPOT_PIN_POLICY_PARTITION ( '[[database.]schema.]table', '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.
table	Table to pin.
min‑range‑value max‑range‑value	Minimum and maximum value of partition keys in table 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 only on the depot in subcluster. If you omit this parameter, the policy is set on all depots in the database.

Privileges

Superuser

Requirements

The following requirements apply to partition pinning:

You can pin partitions of a table on a subcluster only if the table itself is not already pinned on the same subcluster.
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.

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.