REFRESH_COLUMNS

Refreshes table columns that are defined with the constraint SET USING or DEFAULT 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, parameter refresh‑mode must be set to REBUILD.

column‑list

A comma-delimited list of columns to refresh, specified as follows:

  • [[[database.]schema.]table.]column[,…]
  • [[database.]schema.]table.*

where asterisk (*) specifies to refresh all SET USING/DEFAULT USING columns in table. For example:

SELECT REFRESH_COLUMNS ('t1, t2', 't1.*, t2.b', 'REBUILD');

If column‑list is set to an empty string (''), REFRESH_COLUMNS refreshes all SET USING/DEFAULT USING columns in the specified tables.

The following requirements apply:

  • All specified columns must have a SET USING or DEFAULT USING constraint.
  • 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. For example:
    SELECT REFRESH_COLUMNS ('t1, t2', 't1.a, 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

  • Schemas of queried and flattened tables: USAGE
  • Queried table: SELECT
  • Flattened table: SELECT, UPDATE

UPDATE versus REBUILD Modes

In general, UPDATE mode is a better choice when changes to SET USING column data are confined to a relatively small number of rows. Use REBUILD mode 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 the column is specified in any of the following:

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 DEFAULT versus SET USING.