title: Snowflake won't use materialized view if I join tables
body: <p>I'm trying to join my fact table to my dim table. A Materialized view has been created on my fact table to help with performance when getting the sum of totals. However, I'm seeing that my MV isn't being used in example #1. The only time it works is if I created an aggregated sub-query based on examples #2</p>
  <p>The examples below use data from Snowflake's sample data.</p>
  <p>Do I always have to write my query like example #2 to make use of it?</p>
  <pre><code>--creating the MV
  create or replace materialized view my_db.public.inventory_mv as 
  (select inv_item_sk,sum(INV_QUANTITY_ON_HAND) as INV_QUANTITY_ON_HAND from &quot;SNOWFLAKE_SAMPLE_DATA&quot;.&quot;TPCDS_SF10TCL&quot;.&quot;INVENTORY&quot; group by 1)
  </code></pre>
  <pre><code>--Example #1 - My MV does not get used according to the query plan
  select 
      b.I_PRODUCT_NAME
      ,sum(a.INV_QUANTITY_ON_HAND) INV_QUANTITY_ON_HAND
  from &quot;SNOWFLAKE_SAMPLE_DATA&quot;.&quot;TPCDS_SF10TCL&quot;.&quot;INVENTORY&quot; a
  join &quot;SNOWFLAKE_SAMPLE_DATA&quot;.&quot;TPCDS_SF10TCL&quot;.&quot;ITEM&quot; b on a.inv_item_sk = b.i_item_sk 
  group by 1
  </code></pre>
  <pre><code>--Example #2 - The query planner indicates MV is used
  select 
      b.I_PRODUCT_NAME
      ,sum(a.INV_QUANTITY_ON_HAND) INV_QUANTITY_ON_HAND
  from (select inv_item_sk,sum(INV_QUANTITY_ON_HAND) as INV_QUANTITY_ON_HAND from &quot;SNOWFLAKE_SAMPLE_DATA&quot;.&quot;TPCDS_SF10TCL&quot;.&quot;INVENTORY&quot; group by 1) a
  join &quot;SNOWFLAKE_SAMPLE_DATA&quot;.&quot;TPCDS_SF10TCL&quot;.&quot;ITEM&quot; b on a.inv_item_sk = b.i_item_sk 
  group by 1
  </code></pre>