multiple - Reescribir consultas de Oracle con columnas virtuales en la tabla fuente

pivot oracle columns to rows

Question

Tengo una tabla, demo_fact en Oracle 11g y tiene varias columnas virtuales definidas como tales:

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

Entonces tengo una vista materializada definida como

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

Ahora quiero que Query Rewrite se active en la siguiente consulta:

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

pero no es así Ejecuté EXPLAIN_REWRITE y aquí está el resultado:

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

Trasfondo: estoy haciendo esto con 70M filas y 50 columnas virtuales (todas tienen la misma estructura, la declaración de caso simple anterior, pero con una columna de comparación diferente y una columna de resultados diferente)

Este problema parece manifestarse solo cuando la tabla de hechos tiene columnas virtuales, pero cambiarlas a no virtuales consumiría demasiado espacio de disco. ¿Por qué Oracle no está reescribiendo la consulta? ¿Que puedo hacer para arreglarlo?

Answer 1

No sé qué tan útil es para usted, pero Oracle requiere que todas las columnas que agrupan la vista materializada se incluyan en la declaración que se reescribirá. ( edítelo al menos junto con columnas virtuales. Probablemente esto "no sea por diseño" ...)

Si intentas explain_rewrite en

 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

debería decirle que ha reescrito la consulta.

Esto se puede demostrar así:

Una tabla en la que se definirá la columna virtual:

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

La definición de la columna virtual:

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

La vista materializada Nota: agrupa en las columnas a y 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 

La vista materializada no se usará, como lo ha observado:

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

Ahora, ambas columnas a y b se seleccionan y agrupan (con una consulta externa para garantizar el mismo conjunto de resultados):

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