REFRESH ANALYTICAL VIEW; Sequence-Based

Refreshing a sequence-based analytical view populates it with data during the initial refresh, immediately after creating the analytical view. It also updates the analytical view as the base tables grow.

All analytical views can be refreshed fully, regardless of the partition key. Only certain analytical views can be refreshed incrementally. We describe the characteristics of incrementally refreshable analytical views in the Examples section.

This topic contains the following sections:

Syntax

REFRESH [FULL| INCREMENTAL] [ALL] ANALYTICAL VIEWS ['pattern']
  [FROM starting_sequence_column] 
  [TO ending_sequence_column];

Parameters

pattern
This is a quoted string literal, using * (wildcards) and | (pipes) for alternation. The LIKE keyword is optional. The system stores object names in lowercase.
starting_sequence_column
The base table row where to start the refresh.
ending_sequence_column
The base table row where to stop the refresh.

Notes

FULL | INCREMENTAL

By default, the refresh mechanism is in INCREMENTAL mode.

When you specify the FULL refresh mode, the system discards all the contents of the analytical view, and proceeds to rebuild it as if it is newly created and must have the initial refresh to build and make the analytical view usable. The clauses FROM and TO do not apply when the clause FULL is specified or for an initial refresh of an analytical view.

ALL

The keyword ALL affects all analytical views in the cluster.

We recommend that you avoid this operation unless completely necessary, as it may be very computationally intensive and time consuming.

FROM

The FROM starting_sequence_column optional clause specifies sequence column number where to start reading the base table data for refresh. The operation starts with reading the row indicated by the starting_sequence_column and continues forward.

When not using the FROM clause, the system reads all new rows added to the base table after the last analytical view refresh.

TO

The TO ending_sequence_column optional clause specifies the sequence column number where to end reading the base table for refresh. The operation completes after reading the row indicated by the ending_sequence_column.

When not using the TO clause, the system reads until the end of the table.

Examples

Full Refresh

This is the initial refresh that the system uses to build the analytical view after you create it. While most subsequent refreshes should be of the INCREMENTAL type, in special circumstances you may wish to rebuild with the FULL option.

REFRESH FULL ANALYTICAL VIEW sales_av;
Incremental Refresh
REFRESH INCREMENTAL ANALYTICAL VIEW sales_av;
Pattern
REFRESH ANALYTICAL VIEW LIKE '*sales*';
FROM and TO
REFRESH ANALYTICAL VIEW sales_av 
  FROM 1543712356000000 
  TO 1543798756000000;

Note that the FROM parameter, 1543712356000000, is the 'microseconds since Unix epoch' representation of the date December 2, 2018, 00:59:16 GMT. The TO parameter, 1543798756000000, is the 'microseconds since Unix epoch' representation of the date December 3, 2018, 00:59:16 GMT.