Oracle query overwrites with virtual columns in source table

Question

I have a table, demo_fact in Oracle 11g and it has several virtual columns that are defined as such:

ALTER TABLE demo_fact ADD (demo_measure_from_virtual NUMBER GENERATED ALWAYS AS (CASE WHEN demo_category_column = 20 THEN demo_numericdata_column ELSE 0 END) VIRTUAL VISIBLE); 

Then I have a materialized view, defined as

 CREATE MATERIALIZED VIEW demo_agg_mv REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT demo_dim_one, demo_dim_two, SUM(demo_measure_from_virtual) demo_measure_from_virtual FROM demo_fact GROUP BY demo_dim_one, demo_dim_two 

Now I want Query Rewrite to execute the following query:

 SELECT demo_dim_one, SUM(demo_measure_from_virtual) FROM demo_fact GROUP BY demo_dim_one 

but this is not so. I ran EXPLAIN_REWRITE and here is the result:

 QSM-01150: query did not rewrite QSM-01102: materialized view, DEMO_AGG_MV, requires join back to table, DEMO_FACT, on column, DEMO_MEASURE_FROM_VIRTUAL QSM-01082: Joining materialized view, DEMO_AGG_MV, with table, DEMO_FACT, not possible QSM-01102: materialized view, DEMO_AGG_MV, requires join back to table, DEMO_FACT, on column, DEMO_NUMERICDATA_COLUMN 

Backstory: I do this with 70mm rows and 50 virtual columns (they all have the same structure, a simple example above, but with a different comparison column and a different result column)

This problem, apparently, appears only when the fact table has virtual columns, but changing them to non-virtual will consume too much disk space. Why doesn't Oracle rewrite the query? What can I do to fix this?

Answer 1

I don’t know how useful this is for you, but Oracle requires all columns grouped by material to be included in the statement that needs to be rewritten. (edit, at least in conjunction with virtual columns. Perhaps this is "off-design" ...)

If you try to explain_rewrite on

  select demo_dim_one, sum(s) from ( select demo_dim_one, sum(demo_measure_from_virtual) s from demo_fact group by demo_dim_one, demo_dim_two ) group by demo_dim_one 

he must tell you that he rewrote the request.

This can be demonstrated as follows:

The table in which the virtual column will be defined:

 create table tq84_virt_col ( a varchar2(2), b varchar2(2), c number, d number ); insert into tq84_virt_col values ('A', 'X', 1, 1); insert into tq84_virt_col values ('A', 'X', 2, 1); insert into tq84_virt_col values ('A', 'Y', 3, 0); insert into tq84_virt_col values ('A', 'Y', 4, 1); insert into tq84_virt_col values ('B', 'Y', 11, 1); insert into tq84_virt_col values ('B', 'X', 12, 0); insert into tq84_virt_col values ('B', 'X', 13, 1); 

Virtual Column Definition:

 alter table tq84_virt_col add ( virt_col number generated always as ( case when d = 1 then c else 0 end ) virtual visible ); 

Materialized view. Note: it groups columns a and b :

 create materialized view tq84_mat_view refresh force on demand enable query rewrite as select a, b, sum(virt_col) sum_virt_col from tq84_virt_col group by a,b 

The materialized view will not be used, as you noticed:

 begin dbms_mview.explain_rewrite( 'select a, sum(virt_col) from tq84_virt_col group by a' ); end; / select message from rewrite_table; QSM-01150: query did not rewrite QSM-01102: materialized view, TQ84_MAT_VIEW, requires join back to table, TQ84_VIRT_COL, on column, VIRT_COL QSM-01082: Joining materialized view, TQ84_MAT_VIEW, with table, TQ84_VIRT_COL, not possible QSM-01102: materialized view, TQ84_MAT_VIEW, requires join back to table, TQ84_VIRT_COL, on column, C 

Now both columns a and b are selected and grouped (with an external query to provide the same result set):

 truncate table rewrite_table; begin dbms_mview.explain_rewrite( 'select a, sum(s) from (select a, sum(virt_col) s from tq84_virt_col group by a, b) group by a' ); end; / select message from rewrite_table; QSM-01151: query was rewritten QSM-01209: query rewritten with materialized view, TQ84_MAT_VIEW, using text match algorithm QSM-01219: no suitable materialized view found to rewrite this query