Home » SQL & PL/SQL » SQL & PL/SQL » Query rewrite failing to use an obvious MView candidate
Query rewrite failing to use an obvious MView candidate [message #221379] Mon, 26 February 2007 08:46 Go to next message
yanistheyak
Messages: 1
Registered: February 2007
Junior Member
Hi

I have a problem with a query rewrite appearing to not be correctly applied. First off let me show you an example of where the query rewrite does work.

The Materialised view is created as

MV #1
create materialized view MV_OLAP_FF_0023
refresh fast on demand enable query rewrite
as select
D_PERIOD.QUARTER as c0,
D_PERIOD.YEAR as c1,
D_NODE_MV.SYSTEM as c2,
D_MEASURE_TYPE.MEASURE_TYPE as c3,
D_EDR_TYPE_MV.EDR_TYPE as c4,
sum(F_FILE.EDR_COUNT) as m0,
sum(F_FILE.EDR_DURATION) as m1,
sum(F_FILE.EDR_VALUE) as m2,
sum(F_FILE.EDR_BYTES) as m3
from
D_PERIOD D_PERIOD,
F_FILE F_FILE,
D_NODE_MV D_NODE_MV,
D_MEASURE_TYPE D_MEASURE_TYPE,
D_EDR_TYPE_MV D_EDR_TYPE_MV
where F_FILE.D_PERIOD_ID = D_PERIOD.D_PERIOD_ID
and F_FILE.D_NODE_ID = D_NODE_MV.D_NODE_ID
and F_FILE.D_MEASURE_TYPE_ID = D_MEASURE_TYPE.D_MEASURE_TYPE_ID
and F_FILE.D_EDR_TYPE_ID = D_EDR_TYPE_MV.D_EDR_TYPE_ID
group by D_PERIOD.QUARTER, D_PERIOD.YEAR, D_NODE_MV.SYSTEM, D_MEASURE_TYPE.MEASURE_TYPE, D_EDR_TYPE_MV.EDR_TYPE;

Now if I run the following query the optimizer does what it should and uses the above MView.

QUERY #1
select "D_PERIOD"."QUARTER" as "c0",
"D_PERIOD"."YEAR" as "c1",
"D_NODE_MV"."SYSTEM" as "c2",
"D_MEASURE_TYPE"."MEASURE_TYPE" as "c3",
"D_EDR_TYPE_MV"."EDR_TYPE" as "c4",
sum("F_FILE"."EDR_BYTES") as "m0"
from "D_PERIOD" "D_PERIOD",
"F_FILE" "F_FILE",
"D_NODE_MV" "D_NODE_MV",
"D_MEASURE_TYPE" "D_MEASURE_TYPE",
"D_EDR_TYPE_MV" "D_EDR_TYPE_MV"
where "F_FILE"."D_PERIOD_ID" = "D_PERIOD"."D_PERIOD_ID"
and "F_FILE"."D_NODE_ID" = "D_NODE_MV"."D_NODE_ID"
and "F_FILE"."D_MEASURE_TYPE_ID" = "D_MEASURE_TYPE"."D_MEASURE_TYPE_ID"
and "F_FILE"."D_EDR_TYPE_ID" = "D_EDR_TYPE_MV"."D_EDR_TYPE_ID"
group by "D_PERIOD"."QUARTER", "D_PERIOD"."YEAR", D_NODE_MV"."SYSTEM", "D_MEASURE_TYPE"."MEASURE_TYPE", "D_EDR_TYPE_MV"."EDR_TYPE"


So far so good.

The problem starts when I remove the D_NODE_MV from my query giving me

QUERY #2
select "D_PERIOD"."QUARTER" as "c0",
"D_PERIOD"."YEAR" as "c1",
--"D_NODE_MV"."SYSTEM" as "c2",
"D_MEASURE_TYPE"."MEASURE_TYPE" as "c3",
"D_EDR_TYPE_MV"."EDR_TYPE" as "c4",
sum("F_FILE"."EDR_BYTES") as "m0"
from "D_PERIOD" "D_PERIOD",
"F_FILE" "F_FILE",
--"D_NODE_MV" "D_NODE_MV",
"D_MEASURE_TYPE" "D_MEASURE_TYPE",
"D_EDR_TYPE_MV" "D_EDR_TYPE_MV"
where "F_FILE"."D_PERIOD_ID" = "D_PERIOD"."D_PERIOD_ID"
--and "F_FILE"."D_NODE_ID" = "D_NODE_MV"."D_NODE_ID"
and "F_FILE"."D_MEASURE_TYPE_ID" = "D_MEASURE_TYPE"."D_MEASURE_TYPE_ID"
and "F_FILE"."D_EDR_TYPE_ID" = "D_EDR_TYPE_MV"."D_EDR_TYPE_ID"
group by "D_PERIOD"."QUARTER", "D_PERIOD"."YEAR", /* D_NODE_MV"."SYSTEM",*/ "D_MEASURE_TYPE"."MEASURE_TYPE", "D_EDR_TYPE_MV"."EDR_TYPE"


As far as I understand the optimizer should figure out that the MView MV_OLAP_FF_0023 should still be OK, the query is less granular than the MView - but it doesn't and I don't know why?

I have done further investigation and have distilled the problem down to the inclusion of the D_NODE_MV table in the MView. To illustrate, the following two definitions for my MView differ only by the inclusion of the D_NODE_MV table in the where clause (note no other reference to the table in the query). When running QUERY #2 with MV #2 the optimizer falls back to the raw tables, but when running QUERY #2 with the MV #3 the optimiser uses the MView. Why should the simple act of adding or removing the D_NODE_MV table make a difference to the optimizer?

MV #2
create materialized view MV_OLAP_FF_0023
refresh fast on demand enable query rewrite
as select
D_PERIOD.QUARTER as c0,
D_PERIOD.YEAR as c1,
D_MEASURE_TYPE.MEASURE_TYPE as c3,
D_EDR_TYPE_MV.EDR_TYPE as c4,
sum(F_FILE.EDR_COUNT) as m0,
sum(F_FILE.EDR_DURATION) as m1,
sum(F_FILE.EDR_VALUE) as m2,
sum(F_FILE.EDR_BYTES) as m3
from
D_PERIOD D_PERIOD,
F_FILE F_FILE,
D_NODE_MV D_NODE_MV,
D_MEASURE_TYPE D_MEASURE_TYPE,
D_EDR_TYPE_MV D_EDR_TYPE_MV
where F_FILE.D_PERIOD_ID = D_PERIOD.D_PERIOD_ID
and F_FILE.D_MEASURE_TYPE_ID = D_MEASURE_TYPE.D_MEASURE_TYPE_ID
and F_FILE.D_EDR_TYPE_ID = D_EDR_TYPE_MV.D_EDR_TYPE_ID
group by D_PERIOD.QUARTER, D_PERIOD.YEAR, D_MEASURE_TYPE.MEASURE_TYPE, D_EDR_TYPE_MV.EDR_TYPE;


MV #3
create materialized view MV_OLAP_FF_0023
refresh fast on demand enable query rewrite
as select
D_PERIOD.QUARTER as c0,
D_PERIOD.YEAR as c1,
D_MEASURE_TYPE.MEASURE_TYPE as c3,
D_EDR_TYPE_MV.EDR_TYPE as c4,
sum(F_FILE.EDR_COUNT) as m0,
sum(F_FILE.EDR_DURATION) as m1,
sum(F_FILE.EDR_VALUE) as m2,
sum(F_FILE.EDR_BYTES) as m3
from
D_PERIOD D_PERIOD,
F_FILE F_FILE,
--D_NODE_MV D_NODE_MV,
D_MEASURE_TYPE D_MEASURE_TYPE,
D_EDR_TYPE_MV D_EDR_TYPE_MV
where F_FILE.D_PERIOD_ID = D_PERIOD.D_PERIOD_ID
and F_FILE.D_MEASURE_TYPE_ID = D_MEASURE_TYPE.D_MEASURE_TYPE_ID
and F_FILE.D_EDR_TYPE_ID = D_EDR_TYPE_MV.D_EDR_TYPE_ID
group by D_PERIOD.QUARTER, D_PERIOD.YEAR, D_MEASURE_TYPE.MEASURE_TYPE, D_EDR_TYPE_MV.EDR_TYPE;


This almost looks like a bug to me and it is causing a great many problems as it would appear the we are going to have to create a MView for every combination of dimensions in our system, surely this can not be correct.

Thanks in advance for any help

Cheers

Ian

[Updated on: Mon, 26 February 2007 08:49]

Report message to a moderator

Re: Query rewrite failing to use an obvious MView candidate [message #221438 is a reply to message #221379] Mon, 26 February 2007 14:20 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Have you tried to run your MV2 with several rows in D_NODE_MV table? As it lacks join condition the query will produce cartesian join with possibly enormous number of rows and your SUMs will be absolutely wrong. So Oracle is doing fine this time Smile

Gints Plivna
http://www.gplivna.eu
Re: Query rewrite failing to use an obvious MView candidate [message #221451 is a reply to message #221438] Mon, 26 February 2007 20:51 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I have played around with MVs a fair bit, but have no experience with Query Rewite. I don't even know whether it is possible to do QR unless you include all of the base tables of the MV (and I'm too lazy to verify it in the doco).

What is obvious is that QR would be impossible unless you were joining to D_NODE_MV on a unique/primary key. Is d_node_id defined as a primry/unique key? Is the constraint enabled and validated? Do you need to create DIMENSION objects to make this work?

If that doesn't work, go back and re-read the doco carefully. Perhaps paste references here to the appropriate sections that demonstrate your correct understanding of QR to convince skeptics like me.

Good luck.

Ross Leishman
Previous Topic: Alter in PL/SQL
Next Topic: Something about UTL_FILE package !!
Goto Forum:
  


Current Time: Mon Dec 05 18:54:04 CST 2016

Total time taken to generate the page: 0.11447 seconds