Purposely using a stale materialized view

When materialized view data becomes stale, or when the optimizer cannot determine the data freshness, which is the case if you use external tables, you might want to use the materialized view anyway:

You can use the materialized view by performing these tasks:

• Schedule the materialized view for rebuilding. For example, schedule a rebuild to occur every x minutes.
• Adjust the rewriting time window to use stale or possibly stale data for a period of time. For example, schedule the window within which to use stale data for x + y minutes.

1. Create a scheduled query to invoke the rebuild statement every 10 minutes.

   CREATE SCHEDULED QUERY scheduled_rebuild
   EVERY 10 MINUTES AS
   ALTER MATERIALIZED VIEW mv_recently_hired REBUILD;
                   

2. Define the window of time for using stale data, 13 minutes.

   SET hive.materializedview.rewriting.time.window=13min;