Incremental Refresh of Analytical Views

The incremental refresh of analytical views follows these steps:

  1. Set metadata to specify last attempted refresh time.
  2. Insert data into partitions.
  3. Set metadata to track information regarding files in the base table, which is necessary to establish analytical view 'freshness'.
  4. Repeat the previous steps for each partition that is being updated.
  5. Run computational statistics on the analytical view.

The key to optimizing incremental refresh performance is in how to combine the various INSERT statements (Step 2) for multiple partitions. The query option MAX_PARTITIONS_PER_REFRESH_INSERT, which may be specified in connection settings (see Parameter Options), controls the number of partitions that refresh in a single INSERT statement; our default is 20 partitions.

Note that combining too many partitions in a single INSERT may make the operations so large that it runs out of memory. Combining several smaller INSERT operations typically improves overall performance and optimize access to the metastore. We suggest that you experiment with the value of the MAX_PARTITIONS_PER_REFRESH_INSERT option to find the best value for your configuration.