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:
REFRESH [FULL| INCREMENTAL] [ALL] ANALYTICAL VIEWS ['pattern']
[FROM starting_sequence_column]
[TO ending_sequence_column];
*
(wildcards) and
|
(pipes) for alternation. The LIKE
keyword is
optional. The system stores object names in lowercase.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.
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.
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.
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.
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;
REFRESH INCREMENTAL ANALYTICAL VIEW sales_av;
REFRESH ANALYTICAL VIEW LIKE '*sales*';
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.