oracle - 使用源表中的虚拟列重写Oracle查询

我在Oracle 11g中有一张表demo_fact，它具有几个定义为如下的虚拟列：

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);

然后我有一个物化视图定义为

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

现在，我希望查询重写能对以下查询起作用：

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

但事实并非如此。我在上面运行EXPLAIN_REWRITE，这是输出：

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

背景故事：我正在使用7000万行和50个虚拟列来执行此操作（它们都具有相同的结构，上面的简单case语句，但是具有不同的比较列和不同的结果列）

该问题似乎仅在事实表具有虚拟列时才显现出来，但是将其更改为非虚拟列会占用过多的磁盘空间。 Oracle为什么不重写查询？我该如何解决？

最佳答案

我不知道这对您有多大帮助，但是Oracle要求将实体视图分组的所有列都包含在要重写的语句中。 （至少与虚拟列一起编辑。这可能是“不是设计使然” ...）

如果您尝试在explain_rewrite上

 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

它应该告诉您它已经重写了查询。

可以这样证明：

将在其上定义虚拟列的表：

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);

虚拟列的定义：

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

物化视图。注意：它在列a和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

如您所见，将不使用实例化视图：

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

现在，选择了a和b列并将其分组（使用外部查询以确保相同的结果集）：

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