Vertica Analytics Platform Version 9.2.x Documentation

REFRESH_COLUMNS

Refreshes table columns that are defined with the constraint SET USING. All refresh operations associated with a call to REFRESH_COLUMNS belong to the same transaction. Thus, all tables and columns specified by REFRESH_COLUMNS must be refreshed; otherwise, the entire operation is rolled back.

Syntax

REFRESH_COLUMNS ( 'table‑list', '[column‑list]' 
   [, '[refresh‑mode ]' [, min‑partition‑key, max‑partition‑key] ]
)

Parameters

table‑list

A comma-delimited list of the tables to refresh:

[[database.]schema.]table[,…]

If you specify multiple tables, you must also set parameter refresh‑mode to REBUILD.

column‑list

A comma-delimited list of columns to refresh:

  • '' (empty list): Refresh all SET USING/DEFAULT USING columns in the specified tables.
  • [[[database.]schema.]table.]column[,…]
    Refresh the specified columns. The following requirements apply:

    • If REFRESH_COLUMNS specifies multiple tables, all column names must be qualified by their table names. If the target tables span multiple schemas, all column names must be fully qualified by their schema and table names.
    • All specified columns must have a SET USING or DEFAULT USING constraint.

    For example:

    SELECT REFRESH_COLUMNS ('t1, t2', 't1.a, t2.b', 'REBUILD');
  • [[database.]schema.]table.*[,…]
    Refresh all SET USING/DEFAULT USING columns in table. For example:
    SELECT REFRESH_COLUMNS ('t1, t2', 't1.*, t2.b', 'REBUILD');

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

refresh‑mode

Specifies how to refresh SET USING columns:

  • UPDATE: Marks original rows as deleted and replaces them with new rows. In order to save these updates, you must issue a COMMIT statement.
  • REBUILD: Replaces all data in the specified columns. The rebuild operation is auto-committed.

If set to an empty string or omitted, REFRESH_COLUMNS executes in UPDATE mode. If you specify multiple tables, you must explicitly specify REBUILD mode.

In both cases, REFRESH_COLUMNS returns an error if any SET_USING column is defined as a primary or unique key in a table that enforces those constraints.

See REBUILD Mode Restrictions for limitations on using the REBUILD option.

min‑partition‑key
max‑partition‑key

Qualifies REBUILD mode, limiting the rebuild operation to one or more partitions. To specify a range of partitions, max‑partition‑key must be greater than min‑partition‑key. To update one partition, the two arguments must be equal.

The following requirements apply:

  • The function can specify only one table to refresh.
  • The table must be partitioned on the specified keys.

You can use these arguments to refresh columns with recently loaded data—that is, data in the latest partitions. Using this option regularly can significantly minimize the overhead otherwise incurred by rebuilding entire columns in a large table.

See Partition-based REBUILD Operations below for details.

Privileges

  • MODIFY privilege on the target table, USAGE privilege on its schema
  • For each SET USING column to refresh that queries another table or view: SELECT privilege on the queried table/view, USAGE privilege on its schema

REBUILD versus REFRESH Modes

In general, UPDATE is a better choice when changes to SET USING column data are confined to a relatively small number of rows. Use REBUILD when a significant amount of SET USING column data is stale and must be updated. It is generally good practice to call REFRESH_COLUMNS with REBUILD on any new SET USING column—for example, to populate a SET USING column after adding it with ALTER TABLE…ADD COLUMN.

REBUILD Mode Restrictions

If you call REFRESH_COLUMNS on a SET USING column and specify the refresh mode as REBUILD, Vertica returns an error if any of the following conditions is true for that column:

Partition-based REBUILD Operations

If a flattened table is partitioned, you can reduce the overhead of calling REFRESH_COLUMNS in REBUILD mode, by specifying one or more partition keys. Doing so limits the rebuild operation to the specified partitions. For example, table public.orderFact is defined with SET USING column cust_name. This table is partitioned on column order_date, where the partition clause invokes Vertica function CALENDAR_HIERARCHY_DAY. Thus, you can call REFRESH_COLUMNS on specific time-delimited partitions of this table—in this case, on orders over the last two months:

=> SELECT REFRESH_COLUMNS ('public.orderFact', 
                        'cust_name',
                        'REBUILD', 
                        TO_CHAR(ADD_MONTHS(current_date, -2),'YYYY-MM')||'-01',
                        TO_CHAR(LAST_DAY(ADD_MONTHS(current_date, -1))));
      REFRESH_COLUMNS
---------------------------
 refresh_columns completed
(1 row)

Examples

See Flattened Table Example and SET USING versus DEFAULT in Analyzing Data.

See Also