Hey Dave,

The MV in my example can still be used even when joining the orders table to 1 or more tables as long as the query will have at least the same row and column filtering on orders table as defined in the Materialized View.

I've done several tests in MV to really see how usable it can be and the result is quite impressive. It was used even on cases where I thought it wouldn't be. Some example where I've proven it can help are below:

1. To improve calculations on a single table that's joined to another table/s.

2. To improve subqueries.

3. CREATE_TABLE_AS_SELECT type of queries.

4. INSERT SELECT.

5. UPDATE.

6. Data unload (COPY INTO).

7. Clustering.

8. Nested views in multiple levels - TABLE (with MV) -> VIEW1 -> VIEW2 -> VIEW3 -> VIEW4 -> VIEW5. All views have different calculations on them. Querying VIEW5 still utilized the MV.

But then again, these may not always be true. There are many factors that plays around when an MV will be used or not. As a general rule, always use EXPLAIN to see if the created MV will be used by the intended query, drop it if it's not used.