REFRESH

Performs a synchronous, optionally-targeted refresh of a specified table's projections.

Information about a refresh operation—whether successful or unsuccessful—is maintained in system table PROJECTION_REFRESHES until the function CLEAR_PROJECTION_REFRESHES executes or the storage quota for the table is exceeded. The table's IS_EXECUTING column returns a boolean value that indicates whether the refresh is running now (t) or occurred earlier (f).

Syntax

REFRESH ( '[schema.]table‑name[,...]' )

Parameters

schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject
table‑name

The anchor table of the projections to refresh. If you specify multiple tables, REFRESH attempts to refresh them in parallel. Such calls will be part of the Database Designer deployment (and deployment script).

Returns

Column Name Description
Projection Name

The projection targeted for refresh.

Anchor Table The projection's associated anchor table.
Status Status of the projection:
  • Queued: A projection is queued for refresh.
  • Refreshing: A projection refresh is in process.
  • Refreshed: A projection refresh successfully completed.
  • Failed: A projection refresh did not successfully complete.
Refresh Method Method used to refresh the projection:
  • Buddy: Uses the contents of a buddy to refresh the projection. This method maintains historical data. This enables the projection to be used for historical queries.
  • Scratch: Refreshes the projection without using a buddy. This method does not generate historical data. This means that the projection cannot participate in historical queries from any point before the projection was refreshed.
Error Count Number of times a refresh failed for the projection.
Duration (sec) Length of time that the projection refresh ran in seconds.

Privileges

Notes

Examples

The following example refreshes the projections in tables t1 and t2:

=> SELECT REFRESH('t1, t2');
                                             REFRESH
----------------------------------------------------------------------------------------
Refresh completed with the following outcomes:

Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------

"public"."t1_p": [t1] [refreshed] [scratch] [0] [0]"public"."t2_p": [t2] [refreshed] [scratch] [0] [0]

This next example shows that only the projection on table t was refreshed:

=> SELECT REFRESH('allow, public.deny, t');
                                               REFRESH
----------------------------------------------------------------------------------------

Refresh completed with the following outcomes:

Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"n/a"."n/a": [n/a] [failed: insufficient permissions on table "allow"] [] [1] [0]
"n/a"."n/a": [n/a] [failed: insufficient permissions on table "public.deny"] [] [1] [0]
"public"."t_p1": [t] [refreshed] [scratch] [0] [0]