Oracle查询使用源表中的虚拟列重写
发布时间:2020-12-12 16:29:37 所属栏目:百科 来源:网络整理
导读:我在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); 然后
我在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 现在我想要Query Rewrite来启用以下查询: 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,with table,not possible QSM-01102: materialized view,DEMO_NUMERICDATA_COLUMN 背景故事:我正在使用70M行和50个虚拟列(所有这些都具有相同的结构,上面的简单case语句,但具有不同的比较列和不同的结果列) 这个问题似乎只在事实表具有虚拟列时才会显现,但将它们更改为非虚拟列会消耗太多磁盘空间.为什么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',2,'Y',3,0); insert into tq84_virt_col values ('A',4,1); insert into tq84_virt_col values ('B',11,1); insert into tq84_virt_col values ('B',12,0); insert into tq84_virt_col values ('B',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,TQ84_VIRT_COL,VIRT_COL QSM-01082: Joining materialized view,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,using text match algorithm QSM-01219: no suitable materialized view found to rewrite this query (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |