oracle如何查询虚拟列,Oracle查询使用源表中的虚拟列重写

我不知道这对您有多大帮助,但Oracle要求将包含的materialzied视图的所有列都包含在要重写的语句中. (至少与虚拟列一起编辑.这可能是“非设计”……)

如果您尝试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