Managing query rewrites

After changes to base tables, the data in a materialized view is stale. You need to know how to prevent the SQL optimizer from rewriting queries in this situation. If you want a query executed without regard to a materialized view, for example to measure the execution time difference, you can disable rewriting and then enable it again.

As administrator, you can globally enable or disable all query rewrites based on materialized views. By default, the optimizer rewrites a query based on a materialized view.

DWAdmin

Disable rewriting of a query based on a materialized view named mv1 in the default database.
```
ALTER MATERIALIZED VIEW default.mv1 DISABLE REWRITE;
```
Enable rewriting of a query based on materialized view mv1.
```
ALTER MATERIALIZED VIEW default.mv1 ENABLE REWRITE;
```
Globally enable rewriting of queries based on materialized views by setting a global property.
```
SET hive.materializedview.rewriting=true;              
```

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 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.

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;

Define the window of time for using stale data.

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