Oracle query rewrite with virtual columns in the source table

Question

I have a table, demo_fact in Oracle 11g and it has several virtual columns 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 kick in on the following query:

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

but it doesn't. I ran EXPLAIN_REWRITE on and here is the output:

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'm doing this with 70M rows and 50 virtual columns (all of them have the same structure, the simple case statement above, but with a different comparison column and a different result column)

This problem seems to only manifest when the fact table has virtual columns, but changing them to non-virtual would consume too much diskspace. Why isn't Oracle rewriting the query? What can I do to fix it?

Answer 1

I don't know how helpful this is for you but Oracle requires all columns that the materialzied view grouped on to be included in the statement to be rewritten. (edit at least in conjunction with virtual columns. This is probably "not by 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

it should tell you that it has rewritten the query.

This can be demonstrated like so:

A table to on 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);

The definition of the virtual column:

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

The materialized view. Note: it groups on 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 have observed:

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 on (with an outer query to ensure 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